I am working on a business history report. My main data sources are a table connected to the database through ODBC (open database connectivity), a Snapshot table in Access with a copy of the external database’s information from a month ago, and some smaller tables within Access.
I started by creating two main queries, one for the current data and one for the Snapshot data. There is also a query (with its own two sub-queries) to extract data from the same set of tables, with the condition that data from the most recent 6 months is excluded.
I got all this working, but the man I am working with has asked for an additional piece to be added. He asked me to import some more recent data into a separate Access table to compare with the data in the external table and the Snapshot table. If there is a KEYFIELD match the sales numbers and the order numbers are to be substituted for the ones in the two big tables; if no match is found, the data in the big tables is used. I have reworked my queries and set up some sub-queries, but some of the numbers are coming out in really strange sequences.
For instance, two columns will each show a series of numbers, such as:
1 5
1 6
1 7
1 8
2 5
2 6
2 7
2 8
3 5
3 6
3 7
3 8
This sort of thing is showing up in both the final query and the report. It involves the sales totals from last month and the number of orders from last month with respect to the other fields.
I broke things down so that the data with the 6 month filter is processed by one set of queries, whereas the data without the filter is processed by another set of queries.
The next level down there is a main query, a couple of smaller table, and a query that consolidates data from another three smaller tables.
The third level down is a query that actually checks for the KEYFIELD match and performs data substitution as needed. The fourth level down are two smaller queries that extract data from the external table and the recent table, and the Snapshot table and the recent data. (The table with recent data is lacking some fields, so I get some numbers, such as how many mailings were sent out, from one of the big tables.)
I have gone over these queries, but I am not yet seeing where the problem is.
I wrote this out in order to clarify my own thinking, but any suggestions on what to look for will be welcome. Thank you in advance.
I started by creating two main queries, one for the current data and one for the Snapshot data. There is also a query (with its own two sub-queries) to extract data from the same set of tables, with the condition that data from the most recent 6 months is excluded.
I got all this working, but the man I am working with has asked for an additional piece to be added. He asked me to import some more recent data into a separate Access table to compare with the data in the external table and the Snapshot table. If there is a KEYFIELD match the sales numbers and the order numbers are to be substituted for the ones in the two big tables; if no match is found, the data in the big tables is used. I have reworked my queries and set up some sub-queries, but some of the numbers are coming out in really strange sequences.
For instance, two columns will each show a series of numbers, such as:
1 5
1 6
1 7
1 8
2 5
2 6
2 7
2 8
3 5
3 6
3 7
3 8
This sort of thing is showing up in both the final query and the report. It involves the sales totals from last month and the number of orders from last month with respect to the other fields.
I broke things down so that the data with the 6 month filter is processed by one set of queries, whereas the data without the filter is processed by another set of queries.
The next level down there is a main query, a couple of smaller table, and a query that consolidates data from another three smaller tables.
The third level down is a query that actually checks for the KEYFIELD match and performs data substitution as needed. The fourth level down are two smaller queries that extract data from the external table and the recent table, and the Snapshot table and the recent data. (The table with recent data is lacking some fields, so I get some numbers, such as how many mailings were sent out, from one of the big tables.)
I have gone over these queries, but I am not yet seeing where the problem is.
I wrote this out in order to clarify my own thinking, but any suggestions on what to look for will be welcome. Thank you in advance.