Count Query

[ccordner]

Not sure if I should be posting this in reports, but...

I have three tables.

One (tblPeriods) shows the start and end date of each period - 12-01, 12-02, 12-03 etc.



The second(tblRestDaysWorked) shows each instance of somebody working their day off, with a reason field which is the primary key of the third table...

...(tblJustificationCategories) which lists the possible reasons why (Cover for Leave, Sickness, etc.).

What I need is a query that shows, for each period, how many days have been worked for each reason (leave, sickness etc.).

Thought I'd cracked it, but all it's doing is showing the total number of instances for each category for all the periods combined.

Chris

[rpeare]

This is what I used:

tblJustificationCategories (Just_ID autonumber primary key)
Just_ID  JustDesc
1        Just - 1
2        Just - 2
3        Just - 3
4        Just - 4

tblRestDaysWorked (RDW_ID autonumber primary key, Rest_Day date)
RDW_ID  Rest_Day  Just_ID
1       1/1/2012  1
2       1/2/2012  2
3       1/3/2012  2
4       2/1/2012  1 
5       2/2/2012  3 
etc.

This is the query I used:

SELECT Right(DatePart("yyyy",[rest_day]),2) & "-" & DatePart("m",[rest_day]) AS Period_Text_Conv, tblJustificationCategories.JustDesc, Count(tblJustificationCategories.JustDesc) AS CountOfJustDesc
FROM tblRestDaysWorked LEFT JOIN tblJustificationCategories ON tblRestDaysWorked.Just_ID = tblJustificationCategories.Just_ID
GROUP BY Right(DatePart("yyyy",[rest_day]),2) & "-" & DatePart("m",[rest_day]), tblJustificationCategories.JustDesc;

[ccordner]

I've pasted that as SQL and was about to edit it, but looking at it, I don't think it'll work because of the way our periods are (four-weekly and beginning in Apri)

SO period 13-01, for example, might start on the 1/4/12, for four weeks. What I'd need to test is if the [tblRestDaysWorked]![Day] <= [tblPeriods]![EndDate] and [tblRestDaysWorked]![Day] >= [tblPeriods]![StartDate], if that makes sense?

Chris

[rpeare]

So do you have a start and end date in your tblPeriods to go along with the period description? You didn't mention that in your original post and I assumed it was year/month based on your test data.

[ccordner]

Yes, that was my fault. Each Period has an ID (Autonumber) a Caption (What we call it), a Start Date and an End Date (Because they're not all exactly four weeks!)

[rpeare]

I just added StartDay and EndDay as fields in the tblPeriods table and ran this:

SELECT tblRestDaysWorked.RDW_ID, tblRestDaysWorked.Rest_Day, tblRestDaysWorked.Just_ID, DLookUp("[PeriodID]","tblPeriods","[StartDay] <= #" & [Rest_Day] & "# AND [EndDay] >= #" & [Rest_Day] & "#") AS PeriodID_Calc
FROM tblRestDaysWorked;

Then ran my aggregate query based on this 'pre' query and got the result you're looking for.

[ccordner]

Good Afternoon

That is working great. It matches up the days with the periods, but whenever I try to do a query based on it, Access crashes. I also tried, just to experiment, making one of those fields a count field with the same result. Access freezes.

Any ideas why?

Thanks
Chris

[rpeare]

Does any of the data in your table containing the [rest_day] have a null value? If so, that's why. Limit your query only to those records that have a value in the [rest_day] field.

[ccordner]

Doesn't seem to be that. I looked and couldn't find any null values, so I tried putting "Is Not Null" on each one just in case.

What's actually causing it to freeze is when I click the "Sigma" icon to give me the option to add counts etc. Even if I leave them all as "group by" it freezes...

Bizarre!

[rpeare]

Can you make a sample copy of your database and put in some fake data and upload it to this site, I'd rather try to rebuild the wheel if I don't have to.

[ccordner]

Thanks

Sorry for the delay. It's actually a rather big database - or two linked databases. I have uploaded the "Back end" and I will give you a link to download the "Front end", which should work for five days (https://www.transferbigfiles.com/9a2...l2sVN9eai56cg2)

The query "qryAllRestDays" is the one you gave me, which links the rest day with the appropriate Period.

The query "qryRDWTypes" is the one I want to group them by category, from "tbljustificationcategories".

In order to send it, I've had to trim out quite a lot of the "fat", so it is a very slimline version of the database. Unfortunately it doesn't seem to be crashing the same way, so I'm not sure why, but perhaps you'll be able to give me some guidance on getting the result I'm after anyway.

Thanks for your help.

Just so it's completely clear, what I'm trying to achieve is a report that's something like this:

2013 Totals:

Leave: 20 Sickness: 10 etc.

Period 02/01

Leave 2, Sickness 3, Training 1.

Period 02/02

Leave 4 etc.

Chris.

[rpeare]

Your file is 89 megs zipped. Have you compacted and repaired this database lately? I'm in the process of downloading it but you may be experiencing some issues with bloat/small corruptions and a compact/repair may clear up some of your issues.

[ccordner]

Yes, I did try that (on both parts). The file sizes do seem to be astronomical, yet bizarrely it's only the reports and queries etc. With all the reports and queries, it's nearer 500MB even after a compact and repair!

[rpeare]

I can't open your front end database, it gives me an error message that it's been partially converted (usually this happens when you start a conversion and it fails partway through). I see you're using 2007 so try a compact and repair then zip the file up and upload it to this site. The tables that you have linked into your database, just make local tables for those with some bogus data in there to give a flavor of what your data actually looks like.