Putting Date Parameters into a Query/Table in Access 2010/13

[warren66]

In order to select a dataset, I have to run five queries, three of them requiring date parameters, and two which use a date to calculate a week number.

I accept that what I have created is a bit clumsy, and someone with better skills than I possess could probably write a single query to do what I have had to in five, but by creating a macro to run them all, it is effective and it produces the goods, so making it more elegant is not what I am seeking help for right now.

Two of three dates are: EndDate and StartDate (always 181 days prior to the EndDate) and they appear in three of the queries and CalcDate (always 7 days after the EndDate) which appears in the fourth and fifth.

At the moment, I can either hard code them into the statement (this means I have to edit all of the queries each week) or use user parameters prompting the user to provide all the dates (a bit of a fag). I have a solution in mind, but I don't know how to implement it. I would create a table, and create a single field and just one record, namely the EndDate. A query could calculate the other two dates, and I would only have one record to edit; I could either edit the record manually each week, or use a parameter to ask the user to enter the EndDate. However, how would I get the other queries to use these values?

Thanks in advance.

You can stop reading here, unless you want to know what I am trying to achieve...

I want to create a weekly analysis of the value of invoices a list of customers have had and the how much they have paid off their account over the last six months.

Using an ODBC link to the accounts data I get the invoice data in one query, but the receipts data is held in two similar tables, a current and an archive, so I have a query to select the data from one and put it into a table, and another one to append the results from the other into that table.

The last queries use a calculation to summarise the total value of both types of transaction, and allocates a week number, from 26 to the oldest and 1 to the newest.

So now I have two tables of data, I export these into Excel and VBA does an excellent job of lining them up, inserting zero records where there maybe is an invoice value but no payment (or vice versa), calculates a moving balance and creates charts plotting all this data for each of the selected customers.

 

[Nails1]

For starters, don't put in anything that's redundant. Google "database table normalization" for simple steps to make your tables much more elegant. Trust me, you want to put some effort into this.

So don't put in a start date if you already know it's 181 before the end data. Instead, just calculate it on the fly. I haven't tested this, but I think something like it would work in you query field:

StartDate: tblName.EndDate - 181​

Also, don't duplicate your data in separate tables. If the tables are linked, just use that link.

> I have a solution in mind

Yes, you do. Implement it.

> At the moment, I can either hard code them into the statement

Not sure what statement, but just "hardcode" them into a query as above. If necessary, you can make forms calculate these values, too. But use those redundant values only for the stupid humans -- those redundant values don't belong in any table.

> how would I get the other queries to use these values

Put the answer table from one query in the query def of the next one. That is, add the query as you would a table.

There are very few circumstances when I've had to do string queries together, though. And they always involve needing to recurse through an intermediate answer table in a procedure. (I'm guessing that this only seems necessary because your tables are not normalized.)

> I want to create a weekly analysis of the value of invoices a list of customers have had and the how much they have paid off their account over the last six months.

This seems to beg for an aggregate query. Search "Sum".

> Using an ODBC link to the accounts data I get the invoice data in one query, but the receipts data is held in two similar tables, a current and an archive, so I have a query to select the data from one and put it into a table, and another one to append the results from the other into that table.

Sure sounds like poor normalization to me. If not a pile of hacks that link legacy (desktop) systems, instead of building a proper database to do it all. (IMHO, if a new database system doesn't lead to changing how you work, then what's the point of doing anything?)

> calculates a moving balance

Exactly why you want to do this in a query instead of a table: if the results are ephemeral, they clearly don't need to be in any table. Put them in RAM instead.

 

[dhookom]

Great comments from Nails1. I would add faq701-6763.

Duane
Hook'D on Access
MS Access MVP

 

[warren66]

Thanks guys - I've taken note of your comments and I am going to redesign the entire structure of the database on the lines of your suggestions.

I just hope I can get the queries to pick up the dates from the form I'm going to create!