By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Use best data

P: 1
Hi,
I am trying to get supplier data into a new database. I have a table containing item no connected with supplier no's. Then I have another table containing supplier no and a number of fields with address, phone, name and such data. A third table has most of the suppliers but not all of them. On the other hand this table has been updated continuously. I want to create a table with the most detailed and recent address to all suppliers of items in the first table.

KSPRODOS.............KSPROD..................SUPPR CS
itemno......................suppno................ .....suppno
suppno.....................address................ ....address
...............................phone.............. .........phone


-If there is a number in KSPRODOS/suppno it should appear in the resulting table.

-If there is a record with matching suppno in KSPROD/address and KSPROD/phone it should be used.

-If there is a record with matching suppno in SUPPRCS/address and SUPPRCS/phone it should overwrite previous data.

I want a table with all items that have a supplier number. If there is a name and address in the matching fields it should be the most recent otherwise it should be empty. Somebody will have to try to find the supplier from other sources.
Oct 24 '08 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,271
We don't do the task for you, and we expect you to tell us where you got stuck in your own attempts at solving your problem.

However, I will jot down a basic outline to get you started. If you have any problems putting this in place then post precise details of where you are having trouble and we can help further.
  • Start with a blank table.
  • Use APPEND queries to add the data from the other tables. Make sure you add the highest priority tables first as (if the resultant table is set up properly with a unique index on [SuppNo]) any attempts to add records where an existing one exists will fail.
  • When all records from the other tables have been added in, run UPDATE queries on all the data where the required fields are Null (no data), to change them to the values you want from the tables that have the data.
If you are methodical and organised there should be little here to cause you problems.
Oct 24 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.