Trying not to run multiple queries

[Tamara]

Hello,
I have a query where I select the items in our property room on a specific
day by using the criteria [Received Date]<=Date AND [Disposed Date]>=Date or
Is Null. I then have another query that is based of the first query and
merely counts the number of records from the first query. Also FYI, my Date
fields are actually number fields set up like YYYYMMDD (ex. 20090101).

I need to get historical data for each day back a couple of years and I do
not want to have to run the query hundreds of times to get a count for each
day. Is there any way I can set a macro or program to run the query multiple
times with a different parameter each time? I do not know VB, so I would
like to do this on the front end if possible.

Please let me know if you have any ideas as I have posted to this community
in the past and have never received any responses.

Thank you,
TAMARA

 

[Jeff Boyce]

Not sure from your description, but it sounds like a Totals query, using
Group By for the date field and Count for something like the ID field, would
give you daily totals.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

Hello,
I have a query where I select the items in our property room on a specific
day by using the criteria [Received Date]<=Date AND [Disposed Date]>=Date or
Is Null. I then have another query that is based of the first query and
merely counts the number of records from the first query. Also FYI, my Date
fields are actually number fields set up like YYYYMMDD (ex. 20090101).

I need to get historical data for each day back a couple of years and I do
not want to have to run the query hundreds of times to get a count for each
day. Is there any way I can set a macro or program to run the query multiple
times with a different parameter each time? I do not know VB, so I would
like to do this on the front end if possible.

Please let me know if you have any ideas as I have posted to this community
in the past and have never received any responses.

Thank you,
TAMARA

This could be done with the help of a little auxiliary table. You can build it
quickly in Excel - create a new sheet, put the earliest date you'll ever need
in A1, select a few thousand rows of column A, and use Insert... Fill Series
to fill with sequential days. Set the format of the column to yyyymmdd, and
copy and paste into a new table, Alldates, with one number field TheDate
(don't use Date as a fieldname, it's a reserved word).

Then create a Query joining AllDates to your table:

SELECT AllDates.TheDate, Count(*)
FROM AllDates INNER JOIN yourtable
ON AllDates.TheDate <= yourtable.[Received Date]
WHERE NZ(yourtable.[Disposed Date], AllDates.[TheDate]) >= AllDates.TheDate;

 

[anlu]

Hi Tamara,

If I have understood your problem correctly, one way could be like this:

1) Create a table containing all the dates you want to count - if these are
successive in some interval, you can easily generate the dates in Excel and
import them to a table. I'll assume that the table is called "tblDate" and
the column is called "DateNo".

2) If I assume that your table of items is called "tblItem" and has a column
called "ItemNo" identifying the item, create a query with this SQL:

SELECT tblDate.DateNo, tblItems.ItemNo
FROM tblDate, tblItems
WHERE [Received Date]<=DateNo AND ([Disposed Date]>=DateNo OR [Disposed
Date] is null);

3) Create a query based on the query from step 2 with this SQL (assuming the
first query is called "Query1")

SELECT Query1.DateNo, Count(*)
FROM Query1
GROUP BY Query1.DateNo;

Then this last query will give you the number of items for each date. Dates
with no items will be left out, but I could imaginge that this is not an
issue. If it is, you can fix it with an outer join on the tblDate table.

Hope this can help you.

Regards,
anlu

 

[Tamara]

That worked perfectly. Thank you so much!

Hi Tamara,

If I have understood your problem correctly, one way could be like this:

1) Create a table containing all the dates you want to count - if these are
successive in some interval, you can easily generate the dates in Excel and
import them to a table. I'll assume that the table is called "tblDate" and
the column is called "DateNo".

2) If I assume that your table of items is called "tblItem" and has a column
called "ItemNo" identifying the item, create a query with this SQL:

SELECT tblDate.DateNo, tblItems.ItemNo
FROM tblDate, tblItems
WHERE [Received Date]<=DateNo AND ([Disposed Date]>=DateNo OR [Disposed
Date] is null);

3) Create a query based on the query from step 2 with this SQL (assuming the
first query is called "Query1")

SELECT Query1.DateNo, Count(*)
FROM Query1
GROUP BY Query1.DateNo;

Then this last query will give you the number of items for each date. Dates
with no items will be left out, but I could imaginge that this is not an
issue. If it is, you can fix it with an outer join on the tblDate table.

Hope this can help you.

Regards,
anlu

Hello,
I have a query where I select the items in our property room on a specific
day by using the criteria [Received Date]<=Date AND [Disposed Date]>=Date or
Is Null. I then have another query that is based of the first query and
merely counts the number of records from the first query. Also FYI, my Date
fields are actually number fields set up like YYYYMMDD (ex. 20090101).

I need to get historical data for each day back a couple of years and I do
not want to have to run the query hundreds of times to get a count for each
day. Is there any way I can set a macro or program to run the query multiple
times with a different parameter each time? I do not know VB, so I would
like to do this on the front end if possible.

Please let me know if you have any ideas as I have posted to this community
in the past and have never received any responses.

Thank you,
TAMARA