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

  • 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


    • Edited by KWKing.zw Monday, May 20, 2019 6:32 AM
    Friday, May 17, 2019 10:45 AM

Answers

  • To return all data over the date range as a single row in a result table you'd firstly have to concatenate the values from each of the three tables over each column in three separate queries.  This can be done with a VBA concatenation function.  You'll find an example in Concat.zip in my public databases folder at:



    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    In this little demo a GetList function uses the highly efficient GetString method of the ADO recordset object to return the concatenated values over the set as a single string expression.  To restrict the return value by the date range you'd pass the date parameters into the function as a BETWEEN…..AND expression as the function's strFilter argument.

    Once you have the result tables of the three queries the Cartesian product of the three can then easily be returned in a final query in which the three queries are not specifically joined.  As the result table of each query is a single row, the Cartesian product of the three will itself be a single row.

    Ken Sheridan, Stafford, England

    Thanks Ken

    While I did not use the example you provided, you confirmed my thought process . I actually had more than 3 tables and more fields than in the example, they were just representative of the types of data I am dealing with. My final query is a cartesian product of a number of other queries each with a single row as required.

    Cheers.

    • Marked as answer by KWKing.zw Tuesday, May 21, 2019 4:28 PM
    Tuesday, May 21, 2019 4:27 PM

All replies

  • 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.

    Friday, May 17, 2019 11:00 AM
  • @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)


    • Edited by KWKing.zw Friday, May 17, 2019 12:35 PM
    Friday, May 17, 2019 12:33 PM
  • 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
     
  • Insert an Option Group control on the form and bind it to the Frequency field. The Option Group wizard will allow you to create the Daily Frequency as a number 1, Weekly Frequency as a number 2 and Monthly Frequency as number 3.

    That way you keep all your data in one table and simply indentify each record entry as a Daily, Weekly or Monthly record. Then the user fills out the coorresponding fields. You can even enable or disable entry fields depending on whether the record is a Daily, Weekly or Monthly record.

    You can also easily filter records on your form and create reports based upon the Frequency field and corresponding entry fields.

Friday, May 17, 2019 2:30 PM
  • To return all data over the date range as a single row in a result table you'd firstly have to concatenate the values from each of the three tables over each column in three separate queries.  This can be done with a VBA concatenation function.  You'll find an example in Concat.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    In this little demo a GetList function uses the highly efficient GetString method of the ADO recordset object to return the concatenated values over the set as a single string expression.  To restrict the return value by the date range you'd pass the date parameters into the function as a BETWEEN…..AND expression as the function's strFilter argument.

    Once you have the result tables of the three queries the Cartesian product of the three can then easily be returned in a final query in which the three queries are not specifically joined.  As the result table of each query is a single row, the Cartesian product of the three will itself be a single row.

    Ken Sheridan, Stafford, England

    Friday, May 17, 2019 5:58 PM
  • To return all data over the date range as a single row in a result table you'd firstly have to concatenate the values from each of the three tables over each column in three separate queries.  This can be done with a VBA concatenation function.  You'll find an example in Concat.zip in my public databases folder at:



    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    In this little demo a GetList function uses the highly efficient GetString method of the ADO recordset object to return the concatenated values over the set as a single string expression.  To restrict the return value by the date range you'd pass the date parameters into the function as a BETWEEN…..AND expression as the function's strFilter argument.

    Once you have the result tables of the three queries the Cartesian product of the three can then easily be returned in a final query in which the three queries are not specifically joined.  As the result table of each query is a single row, the Cartesian product of the three will itself be a single row.

    Ken Sheridan, Stafford, England

    Thanks Ken

    While I did not use the example you provided, you confirmed my thought process . I actually had more than 3 tables and more fields than in the example, they were just representative of the types of data I am dealing with. My final query is a cartesian product of a number of other queries each with a single row as required.

    Cheers.

    • Marked as answer by KWKing.zw Tuesday, May 21, 2019 4:28 PM
    Tuesday, May 21, 2019 4:27 PM