Help with query or filter option

[ljksmith]

I have a database where I import a new table every other month. It contains virtually the same data with updated usage in it. What I need to do is somehow find a way to compare the meter field in the previous table with the meter field in the new table to see if they are still the same. Basically its a water usage table with addresses and meters and I need to see if the meter number has changed so I can update it. I've tried everything I can think of but I'm a self taught novice with Access. Any input would be helpful.

[aytee111]

You don't need to see if it has changed, you can update it regardless.

[June7]

You are creating a new table each month for that month's usage? Should import into existing table.

If you replace the meter number, how will that impact historical records and calculation of usage?

If you need to calculate usage, perhaps subquery technique will serve. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[ljksmith]

I have a database where I import a new table every other month. It contains virtually the same data with updated usage in it. What I need to do is somehow find a way to compare the meter field in the previous table with the meter field in the new table to see if they are still the same. Basically its a water usage table with addresses and meters and I need to see if the meter number has changed so I can update it. I've tried everything I can think of but I'm a self taught novice with Access. Any input would be helpful.

It is an excel spreadsheet from an outside source. I use it to prepare an import file into my billing software. I usually use excel to check for updated meter numbers but I was hoping to use Access to do it faster and easier but I'm guessing it's not possible.

[aytee111]

One macro will do it:
- import or link to Excel spreadsheet
- run an update query
- export using a query back to Excel or into the desired format for your billing software

[June7]

Okay, a little confused about data structure. You have a master table in Access associating addresses and meters? Then you get a spreadsheet that has addresses and meters with usage data? You want to update the meters in the master table? Do you want to retain usage data in Access?

[ljksmith]

Every other month I get an excel spreadsheet full of new usage data. It's got the address, meter number and usage data in it. I import the spreadsheet into Access because it's easier to make the import files that way than try to just do it in Excel. I've also been using it to create two queries, one from the previous data and one from the new data then I export it back to Excel and run a formula against the data once I've merged it. What I was hoping that is since I already have the data in Access, run a query that tells me that the meter number for address 123 Main St is not the same in both tables. I'm just not sure how to accomplish this in Access. I use an if formula to do it in Excel. My main goal is to know which meters need to be added in my billing system.

[June7]

Try a query that links the two tables on the address fields. Then create a field with expression: IIf(Table1!meter <> Table2!meter, "Change", "NoChange"). Apply filter criteria under that constructed field: ="Change" to view only records where meter is different.

Now if you want to change the meter in Table1 to the new meter from Table2, run an UPDATE action.

This does depend on the addresses being consistent.

[ljksmith]

Thank you! It works great. And I learned something as well.