- Joined
- Aug 9, 2009
- Messages
- 11
- Reaction score
- 0
I am building an app that at one point compares two versions of the same records. That is, the data in question is input at two different times in two different databases. I import the version on a mainframe into an Access application in which the other input has been performed. (Sadly, the two versions are often quite different, with no real options to correct that problem). I want the user to select the month for which to compare the data.
My plan was to run a query on each table (the 2 tables that contain the two versions of the records). There are often multiple entries for each customer in any given month. For each table I planned to use a query to first select only the records for the month in question (which of course changes regularly) and performs a function of totaling all items per customer with a sum and group by operation in the query.
The next step would be to have another query where, for every customer, subtract sum1 (from data table/query 1) from sum 2 (from data table/query 2) and show only those customers in the result whose sum1 varies from sum2 by an amount to be determined by the user.
My plan, essentially, was to have 2 "trees" of data the come together in a final query that checks the difference to see if it surpasses the set threshold for the difference to be considered significant.
Maybe I can explain that better. I planned to have parameter query1 look at invoice records for the given month's records in one version of data and sum those records by customer. With the user inputting the month in question.
Then parameter query 2 would do the same for the the table containing the second version of the data.
Finally, query 3 would bring use those 2 queries as the data source to compare the two sums amounts and determine, per customer, if they greater than the threshold of variance.
The problem with this is that is requires the user to input the month for the desired data twice, once each for query 1 and query 2. Which is both annoying and leaves room for typos that would produce bizarre results.
Trying to combine queries into 1 large query would require an outer join because I need all records from both tables for the month in question.
Even if I can combine query 1 and query 2 into a single query that looks at both sets of data, I would still have two fields that require input as to the month we are looking at and thus two parameters to which the user would need to respond.
Is there a way to have the input from the user apply to both fields asking for the month, allowing only one response from the user? I am trying very hard to keep this as simple as possible and not drop into doing this all in code.
The only answer I can think of is to use code in a first step that asks for the month and drops the response into a Constants table that the various queries use as their source of the month to consider. I would like to think that there are people out there than know a better, easier way to utilize a parameter query so I can avoid VBA altogether.
My plan was to run a query on each table (the 2 tables that contain the two versions of the records). There are often multiple entries for each customer in any given month. For each table I planned to use a query to first select only the records for the month in question (which of course changes regularly) and performs a function of totaling all items per customer with a sum and group by operation in the query.
The next step would be to have another query where, for every customer, subtract sum1 (from data table/query 1) from sum 2 (from data table/query 2) and show only those customers in the result whose sum1 varies from sum2 by an amount to be determined by the user.
My plan, essentially, was to have 2 "trees" of data the come together in a final query that checks the difference to see if it surpasses the set threshold for the difference to be considered significant.
Maybe I can explain that better. I planned to have parameter query1 look at invoice records for the given month's records in one version of data and sum those records by customer. With the user inputting the month in question.
Then parameter query 2 would do the same for the the table containing the second version of the data.
Finally, query 3 would bring use those 2 queries as the data source to compare the two sums amounts and determine, per customer, if they greater than the threshold of variance.
The problem with this is that is requires the user to input the month for the desired data twice, once each for query 1 and query 2. Which is both annoying and leaves room for typos that would produce bizarre results.
Trying to combine queries into 1 large query would require an outer join because I need all records from both tables for the month in question.
Even if I can combine query 1 and query 2 into a single query that looks at both sets of data, I would still have two fields that require input as to the month we are looking at and thus two parameters to which the user would need to respond.
Is there a way to have the input from the user apply to both fields asking for the month, allowing only one response from the user? I am trying very hard to keep this as simple as possible and not drop into doing this all in code.
The only answer I can think of is to use code in a first step that asks for the month and drops the response into a Constants table that the various queries use as their source of the month to consider. I would like to think that there are people out there than know a better, easier way to utilize a parameter query so I can avoid VBA altogether.