Any MS Access wiz kids out there!!!

Found 23rd Dec 2008
Hi all,

I am hoping someone can help me here. I am stuck with a query in access and have tried everything i know to get the results i need but having no luck. Below is what i need!

I know people will say try the distinct function. And from my understanding is that it doesnt work with a cross tab query. You see all the data is one table. and the fields are as follows:

Year
Centre No
Period (which is the month)
Subject

Now a centre will hold a thousands of courses a year. Some will be englsih some will be maths etc. Now i need to count how many centres were used for a course each month for the year. Now this is the part where it gets tricky. If a centre has been counted for in a previous month then i dont want it counted again for that same course in that year.

Sorry i hope that makes more sense!

11 Comments

the way i understand, its like:

year CENTER Period Subject2008 1 Jan Math2008 1 Jan English2008 1 Feb … year CENTER Period Subject2008 1 Jan Math2008 1 Jan English2008 1 Feb Math2008 1 Feb English


you want the result to be:

Course Month NumberMath Jan 1Math Feb 0English Jan 1English Feb 0


?

I dont think you can do this using a query
you would be more lucky to create a macro and setup the result in a table

try this:
create a table with 3 columns:
Course
Month
Count

Then the macro will basically just count from jan of a year and go on to feb for the subjects that have not been found in jan and so on so forth

cheers

Original Poster

hashman;3844720

the way i understand, its like:you want the result to be:?I dont think … the way i understand, its like:you want the result to be:?I dont think you can do this using a queryyou would be more lucky to create a macro and setup the result in a tabletry this:create a table with 3 columns:CourseMonthCountThen the macro will basically just count from jan of a year and go on to feb for the subjects that have not been found in jan and so on so forthcheers



Ok i get what your saying. But then how would i know how many centres were used during the year.

For example if Centre A was used 4 times in (May, June and August)2008 for maths and 6 times in 2 (Feb, March)2008 for english i would like the results to look something like this:
Centre B was used 5 times in (Feb)2008 for maths

Feb 2008 Mar 2008 May 2008 June 2008 Aug 08
English 1
Maths 2

So basically its more like a unique count of centres by subject and Month/Year!

Does that make better sense?

U need to use the "group by" function when in design view which you do by pressing the greek letter etha or whatever its called, then choose count in the field you want to count and it should show the data you want it to..

Original Poster

scott1295;3844990

U need to use the "group by" function when in design view which you do by … U need to use the "group by" function when in design view which you do by pressing the greek letter etha or whatever its called, then choose count in the field you want to count and it should show the data you want it to..



yeah i have done and that does work to an extent. It will count by month. Now the problem i am having is If a centre has been counted for once already for a specific subject then i dont want it counted again for that year. But if its the same centre for different subject then i want that counted for only once!

then just select count on the centre and that should be it

Original Poster

scott1295;3845289

then just select count on the centre and that should be it



Nah mate. I wish it was that easy. That will count the total number of times a centre is used. I only was a count on how many centres were used by subject and month/year!

For example if Centre A was used 2 times for English 3 times for Maths and 7 times for science. So the total count should only be 3!

Abz;3845343

Nah mate. I wish it was that easy. That will count the total number of … Nah mate. I wish it was that easy. That will count the total number of times a centre is used. I only was a count on how many centres were used by subject and month/year!For example if Centre A was used 2 times for English 3 times for Maths and 7 times for science. So the total count should only be 3!



Aww right i see; try using countif function, its hard to try think of how to do it when i cant sit and plitter with it; most of the time its just got to be trial and error

Original Poster

scott1295;3845574

Aww right i see; try using countif function, its hard to try think of how … Aww right i see; try using countif function, its hard to try think of how to do it when i cant sit and plitter with it; most of the time its just got to be trial and error



yeah i know what your saying. The problem is I've been using the Trial and Error method for about a week now!!!! Need to find a new method. I'm not very good with CountIf function. But will try do some research!

Banned

Passenger 57 starring Wesley Snipes

Abz;3844817

Ok i get what your saying. But then how would i know how many centres … Ok i get what your saying. But then how would i know how many centres were used during the year.For example if Centre A was used 4 times in (May, June and August)2008 for maths and 6 times in 2 (Feb, March)2008 for english i would like the results to look something like this:Centre B was used 5 times in (Feb)2008 for maths Feb 2008 Mar 2008 May 2008 June 2008 Aug 08English 1Maths 2So basically its more like a unique count of centres by subject and Month/Year!Does that make better sense?



I've had a word with my OH (who spends all day on programming and help desk for everyone's problems) and is confident he can explain and/or give a solution but first of all he needs you to clarify the above example again as it doesn't make sense to him as it stands. His main bugbear is always lack of specification and doesn't want to go off on a wrong tangent lol

Initial feedback from my OH based on his understanding of your needs so far:
>>>>>
I think what you want requires a two-step query:

Firstly to ensure that each centre is counted only once do a DISTINCT query

SELECT DISTINCT tblCentreUsage.year, tblCentreUsage.period, tblCentreUsage.subject, tblCentreUsage.centre
FROM tblCentreUsage;

where "tblCentreUsage" is my equivalent of your base table.

(If you create a new query in design view, close the table selection dialogue box and use the view drop-down to select SQL, you can paste the query into that view and change the table name to the correct one.)

Secondly, do an aggregation query based on the query above:

SELECT qryDistinct.year, qryDistinct.period, qryDistinct.subject, Count(qryDistinct.centre) AS [centre count]
FROM qryDistinct
GROUP BY qryDistinct.year, qryDistinct.period, qryDistinct.subject;

Where “qryDistinct” is the name I saved my original query under.

This will produce a list of the years, months, subjects and the number of centres used for each period/subject.
>>>
Happy Christmas by the way - don't work too hard!:gift::santa:
Post a comment
Avatar
@
    Text
    Top Discussions
    1. First batch of original Xbox backwards compatible games56
    2. Anger over Donald Trump's UK crime tweet3369
    3. 5 Letter Scrabble Game52522882
    4. *** Weekly game codes giveaway 192 ***822

    See more discussions