Comparing Data From Two Tables

[dcoley]

Hey, guys. I have a quick opinion question.....



I have an access query that I run every Friday. That query is exported as an Excel file and emailed to certain people. Now, they would like for me to bump last week's data to this week's data and highlight the ones from this week that also appears on last week's spreadsheet. I'm thinking there is more than one way to accomplish this. I would prefer to do it with as little VBA as possible. My initial thought was to create a history table each week and then have a Access link to that table and use that table in a query to compare the two spreadsheets. The problem I see with that is that when I run the macro and it creates the history table, it will be fresh information and not actually the data from last week's report.

Any thoughts on how to accomplish this in the most straightfoward way possible? Please do include the conditional formatting part of it also if the two sets of data matches.

Thanks so much!

[rpeare]

You're trying to highlight individual rows on a variable length file with variable data from week to week. I don't think I'd want to try this without using VBA.

Do you have a date field that tracks when the record originally appeared on an excel file? Or are you basically just exporting everything and trying to identify cross over items after the fact?

[dcoley]

The reports work with loan numbers. So, basically, I'll be comparing the loan numbers from last week's report to this week's report to see if they are still there. It's used for checks and balances basically. My bosses need to view the loans from last week that were supposed to be corrected that still show up on this week's report because they weren't corrected as they should have been. I hope that makes sense.

[JeroenMioch]

Then you make two tables, Current and a history one.
The current one has an extra field with a Yes/No value on it.

Every Friday you check if the loan numbers fromn the Current table come up in the history table. If so you check the Yes/No field with a 'yes' programaticly.
Once that is done you transfer the data from the current table to the history table.

When you export your data from the current table to an excel field you can highlight records using conditional formatting based on the 'yes' value.

Now to program all of this is a whole new story, but this would be my plan..

[rpeare]

That doesn't really answer my question. How do you know something was corrected? what is your trigger for an item to appear in your report? If you can identify when something is placed on and when something is taken off your report by looking at the data you might be able to do something with exporting to a formatted excel file.

Let's say this week items 1 and 2 appear on the report.
Next week item 2 and 3 appear on the report.

I have got to assume there is some piece of data that is identifiable to tell you whether a correction has been made or it'd be impossible to get different results from week to week, so is the field that identifies the 'uncorrected' items a date field? is it just a yes/no? what's the mechanism?

Similarly how do you know something should appear on this report? For instance, let's say you have a correction due on an item, part of your data entry is to record the date the correction is noticed, then when the item is actually corrected you record the date it was completed. If you're doing that, it should be possible to identify via formula on an excel spreadsheet which rows need to be highlighted without comparing to last week's report.

[dcoley]

Thank you for that information, Jeroen. That was helpful to me. What do you think about the possibility of bumping current table with history table....perhaps using an if then statement with some formatting for the duplicates (i.e., if [Table]![Field] = [HTable]![Field], then bold)? I know that what I just wrote in parens isn't the code but just the thought process. After that runs, then run an update query to update the history query with the current query's information.

I'm relatively new to doing these sorts of processes so please bear with me. I'm just kind of thinking through it out loud.

Thanks so much.

[dcoley]

My apologies, rpeare.....If the necessary corrections are made, the loan will no longer come up on the report. We have certain tasks and codes that our queries search for. If, in fact, the correction has been made, it would not be on the new report. So, by the mere fact that it's on the new report still tells us that it wasn't corrected. Does that help any?

That doesn't really answer my question. How do you know something was corrected? what is your trigger for an item to appear in your report? If you can identify when something is placed on and when something is taken off your report by looking at the data you might be able to do something with exporting to a formatted excel file.

Let's say this week items 1 and 2 appear on the report.
Next week item 2 and 3 appear on the report.

I have got to assume there is some piece of data that is identifiable to tell you whether a correction has been made or it'd be impossible to get different results from week to week, so is the field that identifies the 'uncorrected' items a date field? is it just a yes/no? what's the mechanism?

Similarly how do you know something should appear on this report? For instance, let's say you have a correction due on an item, part of your data entry is to record the date the correction is noticed, then when the item is actually corrected you record the date it was completed. If you're doing that, it should be possible to identify via formula on an excel spreadsheet which rows need to be highlighted without comparing to last week's report.

[John_G]

Is there a date field in the data that indicates when the record first appeared on the report? If there is, then Excel could just highlight any record where that date is 7 or more days ago. Once the loan is fixed, that record won't appear anyway.

I should be easy - or am I missing something?

John

[dcoley]

Yes, we do have an aging field setup. I tried to explain to the powers that be that that would be the easiest way to accomplish this, but they want to make it harder than it needs to be. Apparently my brain doesn't work quite hard enough as it is

I think what I may try is to simply duplicate the spreadsheet and then age from 7 days ago (which will mean it was on the last report from a common sense standpoint).

Is there a date field in the data that indicates when the record first appeared on the report? If there is, then Excel could just highlight any record where that date is 7 or more days ago. Once the loan is fixed, that record won't appear anyway.

I should be easy - or am I missing something?

John

[JeroenMioch]

Yes thats about it.
I have to work now so i cant provide you with an example at this moment. But if someone hasnt provided you with
an answer ill try to make an example tomorrow.

Im thinking of a double value query, but ill have to try it out first.

[dcoley]

Thanks a ton, guys! I really appreciate the feedback.

[JeroenMioch]

Your welcome mate.
Ive build a query to compare values in the loan number that appear in both tables.
Im in the process now to mark the matching number in the yes / no field.
When im done, ill show you the codes and tell you how to implement them

[JeroenMioch]

Youll have to bare with me im struggeling to get the value of the Yes / No field to Yes.
Im trying this at the table level, where i normally work with forms..