Seeking advice

[Needhlp]

I read about your different topics/discussions but still have issues.
The group I work for needs to track Customs entries, compile them for payment and make reports. They used to track the entries in Excel but they wish to do this in a Form = INVOICE TRACKING, be able to query and make reports. My Access knowledge is limited but I have created two tables and the above mentioned Form

The main table= Invoice Tracking (as per the group request) as 37 fields. Starting at the 8th field, data is updated by the users on a daily basis.
The source table = XLS 1127 as 7 fields and is refreshed and updated 2-3 times weekly by a live report on the Internet.
Up to now, when I need to update the main table, I run an APPEND QUERY. Once this is done, I run a FIND DUPLICATE QUERY and I delete the duplicates manually because some of the first 7 fields will get updated this time or some other time and therefore it will create duplicates Entry Nbr in the Main table. Meanwhile the other 30 fields are populated by users with the information supplied by the invoices.
Is there a macro or an expression that could be created to avoid the Query from selecting the duplicates randomly? I have a very basic knowledge of macros and VB but would it be possible to write something that would be like In record #1, IF the field 2 or 3 is empty than look at field 9 and others and if Not Null then get the data in the duplicate record #2, merge the
information from the two records??
When the Main table will grow bigger, it will be too much time consuming to delete XXX duplicates and can cause errors.

Thanks

 

[qdogfball]

Must you have duplicate entries? Is there a way to eliminate them?

ie never allow them to be entered in the first place? Your users should not be making duplicate entries into any type of system!
If there are records that they need, they can look them up in Access BEFORE adding them to the system. (or any system)

Is there a way to go directly into Access instead of Excel to Access? That way you can setup your table to have a no duplicate key with an auto number for your transactions.

Why do you import 2-3 times a week?
Couldn't you do this once per day, and then you do not allow any duplicates to be imported?

One thing to point out, is have you REALLY analyzed what you are doing?

ie, Why bother using Access?

What end results are you going for?

What do you mean compile them for payment? Wouldn't that be an accounting system? To track orders and payments for those orders?

 

[rsmonkey]

From the sounds of it you are trying to create a Sales System Tracking db i.e. Have a root around in the sample db's section you'l find some very insightful stuff in thier & some good starting points. You also need to read up on Normalisation aswell...

 

[Needhlp]

I agree we should never allow them to be entered but they get inevitably entered because the report that is rolled comes from the US Customs portal

The users are not the ones making duplicate entries. The duplicates are due to the fact that whenever you roll the report, some of the fields may have been updated while others may have not. The records are constantly repeated with or without new data.

Is there a way to go directly into Access via an Internet site and import directly?
Why we import 2-3 times a week? Because they need to obtain the liquidation date for the shipments.Couldn't you do this once per day? I suggested that to our customs expert today. I will see how it works.

If I REALLY analyzed what you I am doing? I tried to get as much information I could from the Users when I joined the co. but I don't necessarily understand all their process yet and they don't know the information they want to obtain but until they see a working db, they are not sure!
What end results are you going for? Track the invoices received from the various Customs Brokers and match them with the US Customs reports, then track the reasons for dispute until they can be sent to A/P for payment.

Hoping that my explanations are clear enough! Looking at the two tables would really give an idean of what's kind of information is being manipulated - I think!

 

[qdogfball]

I agree we should never allow them to be entered but they get inevitably entered because the report that is rolled comes from the US Customs portal

Why does it get entered more than once in the first place?
I know that you do not have control over this, but can you affect what you are given so you can make your life easier?

Do multiple customs agents send in the information?
Why cant they just submit the information one time?

The users are not the ones making duplicate entries. The duplicates are due to the fact that whenever you roll the report, some of the fields may have been updated while others may have not. The records are constantly repeated with or without new data.

If you had the records in an Access database or any other database, whether they updated some field or not would not create multiple records.

You might need to get out of the Excel "row thinking" that Access frees you from.

If I REALLY analyzed what you I am doing? I tried to get as much information I could from the Users when I joined the co. but I don't necessarily understand all their process yet and they don't know the information they want to obtain but until they see a working db, they are not sure!

If the people that you are creating something for do NOT really know what they want, you cannot give them what they want!

RUN!

You are setting yourself up to fail. You really need to sit down with the team or people that want this information and GRASP what the final result will be.
Then find yourself an answer to their problem.

Hoping that my explanations are clear enough! Looking at the two tables would really give an idean of what's kind of information is being manipulated - I think!

Without seeing the information that is downloaded and the final result that your company wants to have, nothing is clear!

If you cannot write out the process on paper, you will not be able to put it into a database.