Solved

MS Access Query to get Current and Past Data

Posted on 2016-08-08
10
48 Views
Last Modified: 2016-08-11
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.
0
Comment
Question by:bassman592
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 5

Expert Comment

by:Manuel Flores
ID: 41747404
select (whatever) from (table) where InvoicePeriod > datediff(now, -180) order by SuplierCode....  would be an aproximation?

This way will be showed in rows...  to show it in columns must provide several selects as columns.

It is a fun select!
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41747423
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
0
 

Author Comment

by:bassman592
ID: 41747478
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?
0
 
LVL 5

Expert Comment

by:Manuel Flores
ID: 41747490
OK, however you could change the NOW()  for a given date and it will work the same.
0
 
LVL 11

Accepted Solution

by:
CraigYellick earned 500 total points
ID: 41747594
So you were doing seven left outer joins, one for each pair of date/qty columns? I guess that would work but it's pretty complicated and difficult to extend/enhance/maintain.

SQL Server can do correlated sub-queries and the syntax is identical, so the technique is generally applicable.

Let's start with the two dates. To model the queries we're building, I am using the Northwind sample database for the Orders and OrderDetails tables. I renamed the OrderDate field to InvoiceDate and renamed RequiredDate to ReSubmissionDate.  Since the ReSubmissionDate has a value for every record, I randomly nulled-out most of the dates.

The following column expression creates a new column named ItemDate that is the Resub date if present, otherwise the InvoiceDate. We can then ignore the date issue and just reference ItemDate in the WHERE condition.

     nz(ReSubmissionDate,InvoiceDate) as ItemDate

Open in new window


The WHERE condition for 1..6 months from a specified CurrentDate is:

     DateDiff('m',nz(ReSubmissionDate,InvoiceDate),[CurrentDate]) between 1 and 6

Open in new window


The results show orders for the same customer (aka supplier in your case) where there is at least one ItemDate in the past six months but never more than six.

Let me know if you can map this much of the solution to your database schema. It's tough to design complex queries against a database schema that I don't know.

Note: the way Access implements BETWEEN you'll be prompted twice for the CurrentDate. In practice I assume you'll be provided the date as a parameter and it won't be prompting the user. There is a more complex syntax to avoid using BETWEEN if it is required to prompt the user. I really don't like Access parameter prompts and avoid them whenever possible.

-- Craig
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Expert Comment

by:CraigYellick
ID: 41747698
>> 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
0
 

Author Comment

by:bassman592
ID: 41747730
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41747897
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.
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 If

Open in new window


In the query, change the prompt to:

Where IIf(IsDate([ReSubmissionDate], [ReSubmissionDate], [InvoicePeriod]) Between Forms!yourform!txtFromDate AND Forms!yourForm!txtThruDate;

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.
0
 

Author Closing Comment

by:bassman592
ID: 41753016
Thank you!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41753084
So you chose to continue doing 7 joins and prompting rather than using a range and a form control?  OK.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now