Find data determined by date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to populate a report based on volume data for each day of
the year. For example: If today is March 1st, and historically March 1st is
a high volume date, the volume # is 5. If March 1st is a low volume date,
the volume # is 3. I have a table that lists all the high volume dates and
low volume dates, and I have a table that gives me the number for high
volumes and low volumes. How do I build a query that based on the date will
give me my volume #. I started to think this would be an if/then statement,
but access only seems to want that to be true or false so that doesn't work.
Any suggestions (easy steps please I'm not a power user). thx

Example: I run the query, it asks me what date it is, then it gives me what
my volume # for today is - based on it looking at the two tables, date &
volume.
 
You could set up a query like:

SELECT [VolumeTable].[VolumeNumber]
FROM [VolumeTable] INNER JOIN [DateTable]
ON [DateTable].[Type] = [VolumeTable].[Type]
WHERE ([DateTable].[DateValue] = [Enter Date];

where DateTable and VolumeTable are the names of your two tables, Type is
the name of the field indicating whether a date is High or Low volume (this
field must exist in both tables), Type is the name of the field containing
the volume # (ex. 3 or 5) in the table VolumeTable, and [DateValue] is the
name of the field containing the date in the table DateTable. [Enter Date]
in this case will result in a prompt to the user to enter the date of
interest; you may have a different method of inputting the target date.

If you wanted a report containing all dates for the year, you would leave
off the WHERE clause in the above query and add an ORDER BY clause:

SELECT [DateTable].[DateValue], [VolumeTable].[VolumeNumber]
FROM [VolumeTable] INNER JOIN [DateTable]
ON [DateTable].[Type] = [VolumeTable].[Type]
ORDER BY [DateTable].[DateValue]

Carl Rapson
 
Back
Top