Daily import from excel - current only

[TomH]

Hi all.

I'm working with an A/P department that is using "legacy technology" and has no capability for maintaining notes on invoices due, etc. I'd like to give them a database that would provide them with the features they need, but I'm running into a conceptual snag.

I am able to extract from their database system all open invoices each day. For each invoice, the A/P person will make notes from day to day on approvals to pay, any problems with the invoice or related inventory or service, discussions with the vendor, etc. So, let's say on Monday they make notes on a particular open invoice. On Tuesday, they'll pull all open invoices from their system and import that set of invoices into the Access database. Some will be new, but many will already be in the table. Using the Vendor Number and Invoice Number together as the table key, I can avoid duplication into the table. But, some already in the database will not appear in the new extract because they've been paid.

So, my question is... how do I get to a point where they can do this import each day and see only the invoices that are currently open AND maintain their previously-entered notes? I thought first that there would be a query to run each day, but I don't know how to keep consistency regarding table names in the query, etc, so the query doesn't have to be edited for each new import. Would this be an append to get the new ones? If so, how do I drop the ones that are not in the newest extracted data?

A complication, of course, is that I want them to be autonomous and able to do this without my input each day.

Thanks for any help!!

Tom

 

[MarkK]

how do I get to a point where they can do this import each day and see only the invoices that are currently open AND maintain their previously-entered notes?

Well, you read the new data, compare it to the existing data, and any changes you want to keep track of, you, well, keep track of.

but I don't know how to keep consistency regarding table names in the query, etc, so the query doesn't have to be edited for each new import

All the same type of data should go in the same table. Don't make multiple tables of the same structure with different names. If rows in that table need to be distinguished from each other, don't add a new table, add a field and set the value of that field such that you can distinguish rows by the value of that field.

For example, if you have tables named like Data140611, Data140612, Data140613, then you are confusing your data and your structure. Dates are not table names, dates are data. Dates should not be in the name, dates should be data in the record, in the table.

Hope this helps,

 

[TomH]

Well, you read the new data, compare it to the existing data, and any changes you want to keep track of, you, well, keep track of.

... Yes, I understand that. However, my real question is how to get the database to do it. So if I have a table with records

1,2,3,4,5,6,7,8,9,10

and tomorrow my extract has

4,5,6,7,8,9,10,11,12,13

what I want to have happen are the following:

Drop 1,2,3 from the table (or the query that drives the form based on it)
Maintain 4,5,6,7,8,9,10 in the table (and all their relationships)
Add 11,12,13 to the table

and I need to design it in such a way that any Access user can manage it. I'm hoping to not have to import the data into a temporary table requiring further manual intervention each day. Otherwise, I'll be getting called all the time... and we all know how that is.

 

[MarkK]

However, my real question is

Let's say you want directions to Pheonix. It makes a difference if you are currently in LA or Miami. It also makes a difference if you need to walk, drive or fly.

The scope of the problem you raise is broad, and the specificity of the solution will be very very narrow, so you'll need to provide lots more detail, and ask much more specfic questions.

Have you ever opened a recordset before? Do you know how to write SQL? What is your skill level? What is the actual structure of your table? What is the actual format of the incoming data?

 

[TomH]

Let's say you want directions to Pheonix. It makes a difference if you are currently in LA or Miami. It also makes a difference if you need to walk, drive or fly.

Thank you. Though I didn't get an answer, I did learn that I'm too f-ing stupid to communicate clearly enough for a "super moderator."

 

[namliam]

Tom,

Its simple enough, however do you realy want to drop invoices 1,2,3 once payed? Here is what I would do:
Add one column to your table "DatePaidInFull", i.e. will be the date it disappears from your import, this column you fill when it is .... paid in full (or no longer in your import) with the date it is found to be paid in full. I.e. today June 13th.
Now this invoice is by default no longer visible by a standard where clause "DatePaidInFull is null" however is still searchable and still can find associated comments and stuff. You can "clean up" your database by adding a delete query for any invoices paidinfull for longer than a year or 6 months or whatever period.

What you do to only add your new invoices, is simply make an UNMATCHED query from your import table to your perminant table
A quick link from MS themselves
http://office.microsoft.com/en-us/a...find-records-without-matches-HA010205132.aspx

You can do the reverse to fill the paidinfull column with an update query

 

[MarkK]

TomH, I'm sorry if I hurt your feelings. There was no ill will in what I wrote, and I regret if that 's what you heard. Best of luck with your project.