Hy,

I have a table in Ms Access with only 1 field, “CODE” which I store the currency code of different country. I have a second table Rates with 3 fields, “CODE”, “DATE” and “EXCHANGE RATE”. “CODE” and “DATE” are my primary key in the 2nd table. Everyday I insert new records in the Rates table. That is for the month of January I should have 31 records for each Currency Code. For example I should have 31 records for USD. How could I check that for the currency USD, I have 31 records in my table Rates. How can I do it in Ms Access. Using macros or any other means. And how can I make an average for each currency code of the 31 records. And how could I make the check for the month of February (28/29 days)

Please help me

Thanks in advance

Don't post your message in more than one section. It gets confusing. Just be patient and someone should answer you.

Your scenario is easily resolved using a totals query.

To handle the desired date range, express it as a string with m/yyyy, for example:

The date field is eDate (never a good idea to use just 'Date', since that has other implications in Access)

Calculated field Widget =
widget: Month([eDate]) & "/" & Year([eDate])

If your other fields are Code and eRate, and you wanted an average of each currency code for Feb 2000, the complete query would look like:


SELECT Month([eDate]) & "/" & Year([edate]) AS widget, tblEDates.Code, Avg(tblEDates.eRate) AS AvgOfeRate
FROM tblEDates
GROUP BY Month([eDate]) & "/" & Year([edate]), tblEDates.Code
HAVING (((Month([eDate]) & "/" & Year([edate]))="2/2000"));


Try copying / pasting to a new query in your application, substituting your table and field names as necessary.