Query Help 2

[FledglingAnalyst]

I'm trying to create a query to display the number of records received, completed, and on hand for a specified range of dates (via a form). Counting the received and completed records is no problem, its the counting whats incomplete at the end of a given day that's giving me trouble.

Example if I received 5 records on 01/01/2012 and closed 3 records, End of Day Inventory would be 2 records for 01/01/2012. If I received 1 record 01/02/2012 and closed 0, end of day inventory for 01/02/2012 would be 3.

I have fields table.record_id, table.receipt_dt, table.complete_dt.

The output I'm looking for would be Date, Received Count, Completed Count, End of Day Inventory.

Is this possible via a query, or do i need to create a function to make a table that stores this data, then report off the new table?

Using Access 2003.

 

[MazeWorX]

you called your table 'table'??? wow change that quickly to some else like tblTransactions


and yes a query(s) is the way to go.

Query1 Names have been changed

SELECT tblTransactions.receipt_dt, Count(tblTransactions.receipt_dt) AS CountOfreceipt_dt, Count(tblTransactions.complete_dt) AS CountOfcomplete_dt
FROM tblTransactions
GROUP BY tblTransactions.receipt_dt
WITH OWNERACCESS OPTION;

query2

SELECT Query1.receipt_dt, Query1.CountOfreceipt_dt, Query1.CountOfcomplete_dt, [CountOfreceipt_dt]-[CountOfcomplete_dt] AS Summary
FROM Query1
WITH OWNERACCESS OPTION;

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[FledglingAnalyst]

Thanks for the response MazeWorX. I changed the table and field names to make it easier to explain.

[CountOfreceipt_dt]-[CountOfcomplete_dt] will not give me the open inventory for a given day, as it does not take into account anything left over from the previous date.

"Example if I received 5 records on 01/01/2012 and closed 3 records, End of Day Inventory would be 2 records for 01/01/2012. If I received 1 record 01/02/2012 and closed 0, end of day inventory for 01/02/2012 would be 3."

Using the SQL you provided would only give me the incomplete records for a given day, wouldn't tell me what's left over from a range of dates, which is what I need.

 

[MazeWorX]

ok so you want a running total or a summary total?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

summary total:
note: apply your date range to query1

SELECT Sum(Query1.CountOfreceipt_dt) AS SumOfCountOfreceipt_dt, Sum(Query1.CountOfcomplete_dt) AS SumOfCountOfcomplete_dt, Sum([CountOfreceipt_dt]-[CountOfcomplete_dt]) AS Summary
FROM Query1
WITH OWNERACCESS OPTION;

a running sum can be achieved in a reports properties



HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

I would use a table of all dates so you could more easily query this.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

So if he wants a running sum he creates a report based on query1 and query2 and on the Summary field he sets the Running Sum property to: Over All or if he wants a total outstanding for the date range he uses query1 and the newer version of 2. Whats easier than that?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

Create a table of all dates with values for every possible date. Use Excel or whatever.
[tt][blue]
tblDates
================
[TheDate] Date
[/blue][/tt]
Then create a query with the SQL of:

SELECT tblDates.TheDate, Count(tblFledgling.Record_ID) AS CountOfRecord_ID
FROM tblDates, tblFledgling
WHERE (((tblDates.TheDate) Between [Receipt_dt] And Nz([Complete_Dt],#12/31/2022#)))
GROUP BY tblDates.TheDate;

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

The table that would make sense to me would be:
[tt]
MyTable
record_ID Received (Date) Complete (Date)
1 01/01/2012 01/01/2012
2 01/01/2012 01/01/2012
3 01/01/2012 01/01/2012
4 01/01/2012 <NULL>
5 01/01/2012 <NULL>
6 01/02/2012 <NULL>
[/tt]
You can do whatever you want with this table.

Have fun.

---- Andy

 

[dhookom]

Andy,
Where/how would you use your "MyTable"?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I show only a part of a table, for every record there would be more fields, but there are 2 fields with dates for when the order was Received and when Completed. Counting the (not completed) records is a simple task.

FledglingAnalyst mentioned in one of the posts: "I changed the table and field names to make it easier to explain." but I stil can not see a sample of how the table looks like, so MyTable would be my version of it.

Have fun.

---- Andy

 

[MazeWorX]

Actually Andrzejek could use my 2 queries on MyTable

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

Duane
My post above was in haste.

“So if he wants a running sum he creates a report based on query1 and query2 and on the Summary field he sets the Running Sum property to: Over All or if he wants a total outstanding for the date range he uses query1 and the newer version of 2. Whats easier than that?”

I should know there is always more than one way to resolve an issue within Access and forcing my solution as I did was inappropriate. My apologies to you for my hasty response

Regards


MaZeWorX


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

MaZeWorX,
No problem. You have posted tons of quality answers in the past and I expect to keep reading them in the future.

Duane
Hook'D on Access
MS Access MVP

 

[FledglingAnalyst]

The missing piece to much of the trial and error queries I had already written was the variable date. Creating a table with a list of dates solved that problem for me. Thanks dhookom.

Thanks for all the suggestions.

 

[FledglingAnalyst]

Here is what I ended up using. Total of 3 queries. Third query just joins the first two and displays the data. I use IIf statements and sum to count multiple fields in a single query. I'm sure there is a better way to do that.

Qry1

SELECT s_dates.Date, Sum(IIf([dt_closed]=[Date],1,0)) AS Closed, Sum(IIf([dt_loc_receipt]=[Date],1,0)) AS Received
FROM f_case, s_dates
GROUP BY s_dates.Date
HAVING (((s_dates.Date)<=Date()));

Qry2

SELECT s_dates.Date, Count(f_case.sys_case_id) AS CountOfsys_case_id
FROM s_dates, f_case
WHERE (((s_dates.Date)>=[dt_loc_receipt] And (s_dates.Date)<=[dt_closed]) AND ((f_case.dt_closed)=#12/31/1864#))
GROUP BY s_dates.Date
HAVING (((s_dates.Date)<=Date()));

 

[dhookom]

It think you could get the numbers with:

SELECT s_Dates.TheDate,
 Count(f_Case.Record_ID) AS OnHand,
 Sum(Abs(TheDate = Receipt_Dt)) as Received,
 Sum(Abs(TheDate = Complete_Dt)) as Completed
FROM s_Dates, f_Case
WHERE s_Dates.TheDate Between [Receipt_dt] And Nz([Complete_Dt],#12/31/2022#)))
GROUP BY s_Dates.TheDate;

You really shouldn't name a field the same as a function name.

Duane
Hook'D on Access
MS Access MVP

 

[FledglingAnalyst]

dhookom,

Thanks for the awesome suggestion to use Sum(Abs. I didn't even know that function worked for date fields. I've used so many Sum(IIf functions. This will really help me.