Query with counts of multiple date ranges (1 Viewer)

Derevon · Jan 24, 2014

Hello,

I have a table for invoices (one record per invoice). Each record contains a location ID and a received date.

Let's call the table "tbA", the location ID "loc" and the date "rdate". Now I would like to make a query that display one line for each location and in each column shows the count of the number of invoices which are from within a certain date range (age). I'm going to need maybe 5-6 different date ranges, but to simplify things here, let's say two different date ranges in relation to today's date (0-5 days old and 6-10 days old let's say). Could anyone please advise me on how to create such a query? Or perhaps I need many queries? I really can't think of a solution for this problem so any help is appreciated.

Thanks

plog · Jan 24, 2014

This is possible, but requires a few medium/advanced skill level queries (cross-tab, possibly a LEFT JOINED cartesian product sub-query, a custom stratification function). I need more information about your database before I can fully help you:

1. I'd rather not deal with your obvious sample field names, so can you provide the actual names of tables and fields? And if the data is different, give me some example starting data and what should ultimately result from that sample data.

2. Do you have a table that lists all the unique locations?

3. Do you have a table that lists all the date ranges you want to report on?

4. Should your query produce results for which there isn't data? For example, suppose there are no 0-5 day old invoices for Cleveland: should the resulting data show Cleveland having 0 for 0-5 day old invoices, or can it just not appear in the results?

Derevon · Jan 24, 2014

Thanks for your answer. I didn't realise the degree of difficulty this involved, so if this would be too complicated, perhaps I could use some kind of Pivot table instead as what I really want is just a view displaying each location and the number of open invoices in each date range. Anyway, to answer your questions:

1. At first I have a table that is imported from a report generated in the .csv-format. This table is called "d3". It contains a lot of useless fields (I decided it's safer to import all fields to avoid trouble as the reports sometimes have a different number of fields depending on how you make them), so I have a query called "Query_d3_Open" that just shows the fields that I'm interested in, and only invoices which have "Invoice Status"="Open". The fields I display in the query are "Scan date" (date) and "Location_ID" (text). No other fields are interesting here. Basically I just need a line count matching the date ranges in question, i.e. the number of records with a "Scan date" entry (all invoices have this date) for the date range in question. An example output for 3 locations could be:

Location ID 0-5 Days 6-10 Days 11-20 Days 21-30 Days Over 30 Days
--------------------------------------------------------------------------
00035 27 15 3 0
00384 55 23 0 2
00586 66 44 11 7

2. Yes I have a location table, it's called "Locations" and contains besides "Location_ID", "Location" (name of location), and "Country". All fields are "Text". Although, it should suffice to only display the location ID for this query, so no need to involve this table.

3. No, I don't have a table with date ranges, but I'd like to list 0-5 days, 6-10 days, 11-20 days, 21-30 days, and over 30 days. The date ranges can be fixed as I don't need the possibility to view custom ranges.

4. I was thinking there should be a zero if there are no invoices for the specific period, yes, although if it's simply "null" that's ok as well. If there are no invoices in any date range for a specific location, it could be omitted I suppose, although displaying zeros/null instead would be preferable.

jdraw · Jan 24, 2014

You might want to review the info at this link regarding the Partition function.

Good luck

plog · Jan 25, 2014

You could do this with the Partition function, but I don't think that allows you to control the specific text of your ranges, so I made my own function for this:

Code:

Function get_ScanAgeRange(in_ScanDate As Date) As String

Dim ret As String
ret = "Invalid"
    ' return value, by default is because age is negative number
    
Dim int_ScanAge As Integer
int_ScanAge = DateDiff("d", in_ScanDate, Date)
    ' holds age of scan date from today

If int_ScanAge >= 0 And int_ScanAge <= 5 Then ret = "0-5 Days"
If int_ScanAge >= 6 And int_ScanAge <= 10 Then ret = "6-10 Days"
If int_ScanAge >= 11 And int_ScanAge <= 20 Then ret = "11-20 Days"
If int_ScanAge >= 21 And int_ScanAge <= 30 Then ret = "21-30 Days"
If int_ScanAge > 30 Then ret = "Over 30 Days"
' puts age into appropriate range

get_ScanAgeRange = ret


End Function

Paste the above into a module and save it. Then paste this SQL into a new query.

Code:

SELECT Query_d3_Open.Location_ID, get_ScanAgeRange([Scan date]) AS ScanAgeRange, Count(Query_d3_Open.[Scan date]) AS Scans
FROM Query_d3_Open
GROUP BY Query_d3_Open.Location_ID, get_ScanAgeRange([Scan date])
ORDER BY Query_d3_Open.Location_ID, get_ScanAgeRange([Scan date]);

Run that and look at the results. It gives you the data you want, but not in the format you specified. To get it exactly as you want (with the date ranges as columns) you should save that query and then build another query using it. That query would be a cross-tab query which will transform the values in a column into columns themselves. You could also throw the data in the query I made into Excel and pivot it.

Derevon · Jan 25, 2014

Thanks a lot plog!

I tried what you said and made a cross-tab query, and it shows me the data pretty much as I want it, just that the columns are in the wrong order due to alphabetical sorting of numbers, but I can live with this. Again, big thanks!

Derevon · Jan 27, 2014

I have now made a form with a subform displaying the cross tab query, and it's all fine, except one problem: If there are no open invoices in a date range, this date range will not exist as a source control and thus #Name? will be displayed instead in this column which doesn't look very nice. Is there any easy way to make it display nothing instead?

Thanks

Derevon · Jan 27, 2014

Now I have also run into another problem. I'm trying to adapt the above function and query to make it possible to show the results with reference to a selectable date also rather than today's date. I changed the function as follows (adding a second parameter to the function):

Code:

Function get_KPIScanAgeRange(in_ScanDate As Date, KPIDate As Date) As String
Dim ret As String
ret = "Invalid"
    ' return value, by default is because age is negative number
    
Dim int_ScanAge As Integer
int_ScanAge = DateDiff("d", in_ScanDate, KPIDate)
If int_ScanAge >= 0 And int_ScanAge <= 15 Then ret = "0-15 Days"
If int_ScanAge >= 16 And int_ScanAge <= 30 Then ret = "16-30 Days"
If int_ScanAge >= 31 And int_ScanAge <= 45 Then ret = "31-45 Days"
If int_ScanAge >= 46 And int_ScanAge <= 60 Then ret = "46-60 Days"
If int_ScanAge > 60 Then ret = "Over 60 Days"
' puts age into appropriate range
get_KPIScanAgeRange = ret
End Function

Then I tried to edit the query like this to make it retrieve the date based on a text box in a form:

Code:

SELECT Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]) AS KPIScanAgeRange, Count(Query_d3_Open.[Scan date]) AS Scans
FROM Query_d3_Open
GROUP BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate])
ORDER BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]);

But for some reason Access has no idea what [Forms]![d3FormAging]![KPIDate] means when trying to pass it as a parameter to the function giving the error "The Microsoft Access database engine does not recognize <name> as a valid field name or expression. (Error 3070)", however, when the above is used as a criterium it works just fine. What am I missing here? I'm sure I didn't misspell anything, and the form is open, so why can't Access reference the value of this tex box?

Any help is much appreciated!

plog · Jan 27, 2014

If there are no open invoices in a date range, this date range will not exist as a source control and thus #Name? will be displayed instead in this column which doesn't look very nice. Is there any easy way to make it display nothing instead?

That was why I asked #4 in my first post. A query can't create data that isn't in its underlying data sources. So, you need to make an underlying datasource which contains all the data you want in your final query. This means you need to create a table with all the ranges you want.

After creating that table you make a new query on it and the query you currently have. You would LEFT JOIN the existing query to that new table via the date range field and choose to show all from the date range table.

For your form issue, I'd post that in the form section--those aren't my strong suit and I can't see what issue might be--looks like you referenced it correctly, so I don't know.

Derevon · Jan 28, 2014

Thanks. With your instructions I was able to get the crosstab query to display all date ranges, the only problem now being that there is an empty line with a "null" location number and no entries. But I guess I will have to live with that.

Query with counts of multiple date ranges (1 Viewer)

Derevon

Registered User.

plog

Banishment Pending

Derevon

Registered User.

jdraw

Super Moderator

plog

Banishment Pending

Derevon

Registered User.

Derevon

Registered User.

Derevon

Registered User.

plog

Banishment Pending

Derevon

Registered User.

Similar threads

Users who are viewing this thread