MS Access Query to get Current and Past Data
I have a linked (to SQL Server) table in Access 2007 that contains supplier data. The table name is tbSupplierSummary, and the relevant columns are: InvoicePeriod (a date type, the first of a month, like 1/1/2016), SupplierCode (a string, like "ABCD" , ShipQuantity (an integer), and ReSubmissionDate (a date value just like InvoicePeriod). There are other data columns, but just using ShipQuantity should be sufficient. This table tracks each supplier's information over time. So if I want to see what each supplier did in May 2016, the query would be:
SELECT SupplierCode, Invoice_Period , ShipQuantity FROM tbSupplierSummary where InvoicePeriod = #5/1/2016#;
resulting in:
SupplierCode InvoicePeriod ShipQuantity
ABCD 5/1/2016 2000
If I want to create a query that asks for the date desired, it would be:
SELECT SupplierCode, Invoice_Period , ShipQuantity FROM tbSupplierSummary where InvoicePeriod = [CurrentDate];
which would give the same result, except that it would prompt for the "CurrentDate".
What I actually need, however, is the information for the CurrentDate PLUS the information for each supplier for the previous 6 dates, so the result would be something like this:
SupplierCode InvoicePeriod Qty InvoicePeriod-1 Qty-1 InvoicePeriod-2 Qty-2 ........ InvoicePeriod-6 Qty-6
ABCD 5/1/2016 2000 4/1/2016 2100 3/1/2016 2200 11/1/2015 2600
where "Qty" is the CurrentMonth ShipQuantity, and Qty-n is the ShipQuantity for prior months 1 to 6. Initially, I though of simply running a set of 7 queries, one for the CurrentMonth and one for each of prior months 1 to 6, and then using a LEFT JOIN to join all of the results with the CurrentMonth query, since the final result will only have as many records as would be in the CurrentMonth query.
However, there is a kicker, which I didn't realize at first - what I have been calling the "CurrentMonth" can be variable. In other words, supplier data may be originally submitted in say, April 2016, and the "CurrentMonth" for that data would be 4/1/2016, but it would contain a mistake, and be Re-submitted in May 2016. So for records which contain a ReSubmission date (most don't), that date would be used as the CurrentDate. So an example result which includes a "normal" record and a re-submitted record would be:
SupplierCode InvoicePeriod Qty InvoicePeriod-1 Qty-1 InvoicePeriod-2 Qty-2 ........ InvoicePeriod-6 Qty-6
ABCD 5/1/2016 2000 4/1/2016 2100 3/1/2016 2200 11/1/2015 2600
EFGH 4/1/2016 3000 3/1/2016 3100 2/1/2016 3200 10/1/2015 3600
Now, since the CurrentDate can be different, and therefore the time period can be different, I'm not sure how to proceed. I don't know how to create a query that will account for that. I'm thinking I may have to create an Access function that will return a recordset that contains the 7 values given a CurrentDate and SupplierCode, then run that function on every record (in essence, create a cursor). There's not that much data (maybe 100 to 150 records per month), so performance shouldn't be an issue.
Thank you for any help you can provide.
SELECT SupplierCode, Invoice_Period , ShipQuantity FROM tbSupplierSummary where InvoicePeriod = #5/1/2016#;
resulting in:
SupplierCode InvoicePeriod ShipQuantity
ABCD 5/1/2016 2000
If I want to create a query that asks for the date desired, it would be:
SELECT SupplierCode, Invoice_Period , ShipQuantity FROM tbSupplierSummary where InvoicePeriod = [CurrentDate];
which would give the same result, except that it would prompt for the "CurrentDate".
What I actually need, however, is the information for the CurrentDate PLUS the information for each supplier for the previous 6 dates, so the result would be something like this:
SupplierCode InvoicePeriod Qty InvoicePeriod-1 Qty-1 InvoicePeriod-2 Qty-2 ........ InvoicePeriod-6 Qty-6
ABCD 5/1/2016 2000 4/1/2016 2100 3/1/2016 2200 11/1/2015 2600
where "Qty" is the CurrentMonth ShipQuantity, and Qty-n is the ShipQuantity for prior months 1 to 6. Initially, I though of simply running a set of 7 queries, one for the CurrentMonth and one for each of prior months 1 to 6, and then using a LEFT JOIN to join all of the results with the CurrentMonth query, since the final result will only have as many records as would be in the CurrentMonth query.
However, there is a kicker, which I didn't realize at first - what I have been calling the "CurrentMonth" can be variable. In other words, supplier data may be originally submitted in say, April 2016, and the "CurrentMonth" for that data would be 4/1/2016, but it would contain a mistake, and be Re-submitted in May 2016. So for records which contain a ReSubmission date (most don't), that date would be used as the CurrentDate. So an example result which includes a "normal" record and a re-submitted record would be:
SupplierCode InvoicePeriod Qty InvoicePeriod-1 Qty-1 InvoicePeriod-2 Qty-2 ........ InvoicePeriod-6 Qty-6
ABCD 5/1/2016 2000 4/1/2016 2100 3/1/2016 2200 11/1/2015 2600
EFGH 4/1/2016 3000 3/1/2016 3100 2/1/2016 3200 10/1/2015 3600
Now, since the CurrentDate can be different, and therefore the time period can be different, I'm not sure how to proceed. I don't know how to create a query that will account for that. I'm thinking I may have to create an Access function that will return a recordset that contains the 7 values given a CurrentDate and SupplierCode, then run that function on every record (in essence, create a cursor). There's not that much data (maybe 100 to 150 records per month), so performance shouldn't be an issue.
Thank you for any help you can provide.
When resubmitted does the InvoicePeriod get over-written? If so, then you can ignore it since there's no way to tell that the date changed. In your example, how do you know that 4/1/2016 was a resubmission and not the original date?
Also, do you need the results pivoted that way, with numbered column names like Qty-1, Qty-2 etc? That's a very challenging result format because not all suppliers will have six months of prior history for every period requested. It's not impossible, but just difficult.
I'd start with getting the results in a simple tabular format and hold off on pivoting until you're getting the correct results. What you need is called a "correlated sub-query". The Access query designer cannot construct such queries. Technically it can display them but the designer is of almost no use to help create them.
A correlated sub-query uses values from each row in an outer query as parameters to a second query. I can help write the sub-query but first want to know more about the InvoicePeriod issue.
-- Craig
Also, do you need the results pivoted that way, with numbered column names like Qty-1, Qty-2 etc? That's a very challenging result format because not all suppliers will have six months of prior history for every period requested. It's not impossible, but just difficult.
I'd start with getting the results in a simple tabular format and hold off on pivoting until you're getting the correct results. What you need is called a "correlated sub-query". The Access query designer cannot construct such queries. Technically it can display them but the designer is of almost no use to help create them.
A correlated sub-query uses values from each row in an outer query as parameters to a second query. I can help write the sub-query but first want to know more about the InvoicePeriod issue.
-- Craig
ASKER
For the comment from Manuel - I'm not sure that "now" would be a good starting point, since the query may be run in any month, for any month. In other words, I may run the query in September of 2016, but want results for May 2016.
For the comment from Craig - good point, I hadn't thought in terms of a subquery (I'm not that familiar with Access, I do most of my work in SQL Server), but that sounds like the correct approach. The results in my examples are pivoted only because I used the left join to limit the results to whatever records were associated with the original value of CurrentMonth (May 2016 in the examples). The results don't actually have to be pivoted in the initial query - I can do that later, or pull into Excel and do it there. Nothing gets over-written. In my second example, the InvoicePeriod is 4/1/2016, but EFGH contains a ReSubmissionDate of 5/1/2016. ABCD contains NULL in ReSubmissionDate. So when the value of [CurrentDate] is determined (let's say it is 5/1/2016), then I grab two sets of records - those which have an InvoicePeriod of 5/1/2016 (which would have a date range of 5/1/2016 back to 11/1/2015, and those which have a ReSubmissionDate of 5/1/2016, which would have a date range depending on the InvoicePeriod. For example, supplier IJKL may have an InvoicePeriod of 3/1/2016, but a ReSubmissionDate of 5/1/2016, in which case I would have to include it in the results, and get data for it from 3/1/2016 back to 9/1/2015. However, if supplier MNOP contains an Invoice period of 3/1/2016, and a ReSubmissionDate of 4/1/2016, that record would NOT be included in the results. One of those dates has to be the value of [CurrentMonth]. Does that make sense?
For the comment from Craig - good point, I hadn't thought in terms of a subquery (I'm not that familiar with Access, I do most of my work in SQL Server), but that sounds like the correct approach. The results in my examples are pivoted only because I used the left join to limit the results to whatever records were associated with the original value of CurrentMonth (May 2016 in the examples). The results don't actually have to be pivoted in the initial query - I can do that later, or pull into Excel and do it there. Nothing gets over-written. In my second example, the InvoicePeriod is 4/1/2016, but EFGH contains a ReSubmissionDate of 5/1/2016. ABCD contains NULL in ReSubmissionDate. So when the value of [CurrentDate] is determined (let's say it is 5/1/2016), then I grab two sets of records - those which have an InvoicePeriod of 5/1/2016 (which would have a date range of 5/1/2016 back to 11/1/2015, and those which have a ReSubmissionDate of 5/1/2016, which would have a date range depending on the InvoicePeriod. For example, supplier IJKL may have an InvoicePeriod of 3/1/2016, but a ReSubmissionDate of 5/1/2016, in which case I would have to include it in the results, and get data for it from 3/1/2016 back to 9/1/2015. However, if supplier MNOP contains an Invoice period of 3/1/2016, and a ReSubmissionDate of 4/1/2016, that record would NOT be included in the results. One of those dates has to be the value of [CurrentMonth]. Does that make sense?
OK, however you could change the NOW() for a given date and it will work the same.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> where there is at least one ItemDate in the past six months but never more than six
This assumes you only invoice or resubmit once per month. If you invoice more frequently we'll need a more complex query to limit to six dates.
-- Craig
This assumes you only invoice or resubmit once per month. If you invoice more frequently we'll need a more complex query to limit to six dates.
-- Craig
ASKER
I believe it's only once per month, but I'll double check. I'm working out the query now, but so far this approach looks good.
There is no reason to prompt for 6 dates and no reason to run subqueries. You only need one date which is either the start date or end date of a 6 month range OR if you want a variable range then you would need to enter both start and end dates. The best way to enter variables in a query is to use a form to capture them. Then you can run the report/form/query and have the query reference the form fields. So, since I like flexibility, I would create a form with two date controls. In the AfterUpdate event of the From Date, I would use Me.txtThruDate = DateAdd("m", 6", Me.txtFromDate)
to populate the thru date. The user could override if he wanted a range other than 6 months.
Set the format of these two unbound controls to ShortDate. This will tell Access that only valid dates are allowed and also Access will be smart enough to pop up a calendar for the user to pick a date from.
Then use a button to run the form/report/query. But, validate the date range first.
In the query, change the prompt to:
Where IIf(IsDate([ReSubmissionDa
Now, if you want the result set pivoted, use a crosstab. Use the wizard to create it. A warning about crosstabs - parameters MUST be defined. So even though the crosstab is referencing a query that contains the parameter, the parameter must still be defined in the crosstab -- don't ask!
Voila! no multiple prompts, no sub queries, and a variable date range.
to populate the thru date. The user could override if he wanted a range other than 6 months.
Set the format of these two unbound controls to ShortDate. This will tell Access that only valid dates are allowed and also Access will be smart enough to pop up a calendar for the user to pick a date from.
Then use a button to run the form/report/query. But, validate the date range first.
If IsDate(Me.txtThruDate) then
If IsDate(Me.txtFromDate) then
If Me.txtFromDate <= Me.txtThruDate Then
''' run report/form/query
Else
MsgBox "From Date must be <= Thru Date.",vbokonly
Me.txtFromDate.SetFocus
Exit Sub
End If
Else
Msgbox "From Date is required.",vbokOnly
Me.txtFromDate.SetFocus
Exit Sub
End If
Else
Msgbox "Thru Date is required.",vbokonly
Me.txtThruDate.SetFocus
Exit Sub
End IfIn the query, change the prompt to:
Where IIf(IsDate([ReSubmissionDa
Now, if you want the result set pivoted, use a crosstab. Use the wizard to create it. A warning about crosstabs - parameters MUST be defined. So even though the crosstab is referencing a query that contains the parameter, the parameter must still be defined in the crosstab -- don't ask!
Voila! no multiple prompts, no sub queries, and a variable date range.
ASKER
Thank you!
So you chose to continue doing 7 joins and prompting rather than using a range and a form control? OK.
This way will be showed in rows... to show it in columns must provide several selects as columns.
It is a fun select!