What is the best way to combine, daily, weekly and monthly data into one query

Question

I have 3 types data that I need to combine into a single query and would like to know the best way to do this. Type one is data collected on a daily basis.  Type two is data collected on a weekly basis (not necessarily a calendar week) and type three is data collected on a monthly basis. What I need is to be able to query by suppling a start and end date and get a single line with all 3 data types rolled up for that time period ( which could be a month, 2 months, a quarter or even a year).

So to illustrate

table 1 daily data
MeasmnDate	Field1	Field2

table2 weekly data
MeasmnDate	Field3	Field4	Field5



Table3 Monthly data
MeasmnDate	Field6	Field7

Required Output:

Result Query/Table (rolled up for user supplied start and end date that may span a month or longer
Field1	Field2	Field3	Field4	Field5	Field6	Field7

I hope this is clear enough and thanks in advance

Answer 1

I have 3 types data that I need to combine into a single query and would like to know the best way to do this.

Hi KWKing,

Probably you need the query to populate a Form or Report.

Have you thought of a Form/Report with Subforms/Subreports with each their own dedicated query?

Much simpler in my opinion.

Imb.

Answer 2

@lmb

Yes the user will enter a start date and end date on a form and will need to see a single line containing all the fields. 

My one thought was to create a table which will contain the result with all the fields and then  run queries that rollup first the daily data (append query) then the weekly data (update query) and finally the monthly data (update query)

Answer 3

You only need one table with the following fields:

• RecordID as a Primary Key (Data Type AutoNumber)
• Field1 Name......Field7 Name
• BeginDate (Data Type Date/Time)
• EndDate (Data Type Date/Time)
• Frequency (Data Type Number)

• Construct a form with a Record Source based on a query that includes all the fields