Any MS Access wiz kids out there!!! - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Any MS Access wiz kids out there!!!

£0.00 @
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 peop… Read More
Abz Avatar
8y, 6m agoPosted 8 years, 6 months ago
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!
Abz Avatar
8y, 6m agoPosted 8 years, 6 months ago
Options

All Comments

(11) Jump to unreadPost a comment
Comments/page:
#1
the way i understand, its like:

year CENTER Period Subject
2008 1 Jan Math
2008 1 Jan English
2008 1 Feb Math
2008 1 Feb English
you want the result to be:

Course Month Number
Math Jan 1
Math Feb 0
English Jan 1
English 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
#2
hashman
the way i understand, its like:

you want the result to be:

?

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


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?
#3
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..
#4
scott1295
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!
#5
then just select count on the centre and that should be it
#6
scott1295
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!
1 Like #7
Abz
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
#8
scott1295
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#9
Passenger 57 starring Wesley Snipes
#10
Abz
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?


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
#11
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

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!