Hi, I am hoping someone will be able to put me on the right track.

I am very much a beginner in Access SQL and Query design and I have tried and failed to get the correct answer myself.

Please note for the purpose of clarity, I I have used [] to highlight the fields in the table in this question and () to highlight the Table Name

I need to find the total number of Subjects [Subject_Code] that have started between two [Start_Date] dates, so I can record the date range entered as well as acting as the criteria for Ad Hoc Queries.

Then I need the total number of Subjects broken down into two [Split_Code] values, Voc and NonVoc

I then want to know, for each code type, the number for each code as a % of the overall total of Subject Starts

E.G.

Total Starts = 100
Voc = 80
NonVoc = 20

Voc = 80%
NonVoc = 20%

I have two tables.

One stores a learners education record (Course_Information) - [Learner_ID],[Start_Date], [Subject_Code] and another that stores the courses (Subject_Area) - [Subject_Code], [Split_Code]

[Subject Code] in (Subject_Area) table is linked to [Subject_Code] in (Course_Information) allowing for [Split_Code] value to be retrieved against each entry.

When allocating a learner to a course, the following is stored in the (Course_Information) table for each Learner - [Learner_ID],[Subject_Code] and [Start_Date]

The query should ask me to enter a date range from the (Course_Information) table - Start:[Start_Date] and End:[Start_Date]

I have tried to create a query within a query anded up with four queries and tried to put the all together in Access SQL and all failed.

Please can any one help?

Thanks

Mark

see below. is this a school assignment?


hth
tina

Hi tina t,

Thanks for the reply, I am not sure what you mean, please can you explain a little more?

Thanks

Mark

Hi

It would be easier if you could upload a zipped copy of the Db
so we can see the structure

Hi Mike,

Thanks for the request.

Unfortunatley I am unable to send the DB due to factors out of my control.

I know I am making things really awkward, and I appreciate that it is easier to see for yourself a working version.

However I will answer any questions and where I can I will give samples.

Sorry, Really hope that this does not put you off helping?

Thanks

Mark

Mark, is this a school assignment?

tina

Hi Tina,

I wish it was, but it is a live database that I have created for the education department where I work.

I am developing it as a Full Management Information Tool.

We have to provide data and Statistics on an Ad Hoc and Quarterly Basis.

One of the reports we have to produce is the 80/20 Split of Vocational and Non Vocational Subjects delivered.

As you can see this is the area I am having problems with.

I have already tried the following:

To get the total of Voc Subjects:

SELECT Count([Subject Area].Split_Code) AS Voc, [Subject Area].Split_Code, Course_Information.Start_Date, [Start] AS StartDate, [End] AS EndDate, [Quarter] AS Period
FROM [Subject Area] INNER JOIN Course_Information ON [Subject Area].Subject_Area_Code = Course_Information.Subject_Area_Code
GROUP BY [Subject Area].Split_Code, Course_Information.Start_Date, [Start], [End], [Quarter]
HAVING ((([Subject Area].Split_Code)<>"None" And ([Subject Area].Split_Code)="Voc") AND ((Course_Information.Start_Date) Between [Start] And [End]))
ORDER BY [Subject Area].Split_Code DESC;


and NonVoc:

SELECT Count([Subject Area].Split_Code) AS NonVoc, [Subject Area].Split_Code, Course_Information.Start_Date, [Start] AS StartDate, [End] AS EndDate, [Quarter] AS Period
FROM [Subject Area] INNER JOIN Course_Information ON [Subject Area].Subject_Area_Code = Course_Information.Subject_Area_Code
GROUP BY [Subject Area].Split_Code, Course_Information.Start_Date, [Start], [End], [Quarter]
HAVING ((([Subject Area].Split_Code)<>"None" And ([Subject Area].Split_Code)="NonVoc") AND ((Course_Information.Start_Date) Between [Start] And [End]))
ORDER BY [Subject Area].Split_Code DESC;

Totals:

SELECT DISTINCT Course_Information.Subject_Area_Code, Count(Course_Information.Subject_Area_Code) AS CountOfSubject_Area_Code, Course_Information.Start_Date
FROM Course_Information
GROUP BY Course_Information.Subject_Area_Code, Course_Information.Start_Date
HAVING (((Course_Information.Start_Date) Between [Start] And [End]))
ORDER BY Course_Information.Subject_Area_Code;

I am not convinced that they work properly.

I need a query to give me the overal total of all Subjects started.

And then the total subjects as a percentage of the total broken down by Split_Code Voc and NonVoc.

Hope this helps?

Thanks

Mark

Hi Mark

If you cant upload a zipped copy of the Db with no Confidential data in then
maybe you can upload a screenshot of the Relationship window.

I am not really following the relationships you have described

You have not made any mention of Primary Keys and how the PK's are linked to related tables?

Hi Mike,

I am working on clearing out a sample Database for use in this problem, not quite there yet.

Please find a screen shot of the Relationships I have put in place.

Hope this helps, please see my reply post in response to Tina for some sample code I have tried.

Thanks

Mark

okay, just checking. my answers to students are less direct and detailed; i don't want to deprive them of the learning experience their instructors intend to provide through homework assignments and tests.

i've read your posts to myself and to Mike and looked at the screenshot of the Relationships window. you don't need two identical Totals queries to get "Voc" and "NonVoc" totals. just take the first Totals query you wrote, and remove the criteria for "Voc", leaving the rest of the query as is. you should get all the records that the first query returned before you changed it, plus all the records that the second query returns - together in one dataset.

actually, i'm wondering if a crosstab query will return all the data you need. i'm not a query guru, by a long shot, so i'd have to play around with your tables and the query wizard (no freehand SQL writing for me!). you can do the same. you might start by writing a straight SELECT query to pull all the records you want to count. taking the "revised" query that i suggested in the previous paragraph, and turning it back into a SELECT query by unclicking the Totals button in query Design view, would probably do it. then try running the query wizard - choose Crosstab in the dialog, and choose the SELECT query as the base for the crosstab, then work through the wizard. once you have a crosstab, if it doesn't give you exactly what you need, look at it in query Design view and tinker with it.

best i can do, hon, without an actual db to play with. your setup is way too complex for me to attempt to make a dummy copy of my own to work with - at least in the limited amount of time i have, sorry. perhaps Mike will be able to give you more skilled SQL help; many of the VIPs are much better at SQL than i am.

hth
tina

Hi Tina,

Thanks very much for your help with this, I will have a go at the cross tab query idea. I for whatever reason have not had much luck with these queries, but I am desperate to get this to work.

I am still working on a dummy version of my database to upload, I know Mike has asked for that and I know it will help, but I have almost finished it.

I will upload it as soon as I can.

Again thanks

Mark