really need help with this!!

  • Thread starter Thread starter michelle1234
  • Start date Start date
M

michelle1234

Guest
i have two tables and i want to create another table that contains both the information in the two alrady established tables, and whenever the previously established tables are altered the table containing data from both tables is automatically updated
 
Why do you need to replicate the data into a 3rd table?

Col
 
still needing help

ok...i have a table that contains all our customers details, i have a table that contains all our distibuters details, and i now need a table taht contains all of the customers and distributrs details, but so that if a new customer or distributer is added to the corresponding table it is automatically updated in the table containing both the customer and the distributorsdeatils, i dont know why...this is just something my boss wants..and as yet i havent been able to figure out how...but it seems very simple...which is very infuriating!!
 
If there is a common link between the two then a query will do.

I still can't quite get the reasoning behind this though.

Col
 
if you want to list the customers and distributors all in one go try a union query.
 
michelle1234 said:
ok...i have a table that contains all our customers details, i have a table that contains all our distibuters details, and i now need a table taht contains all of the customers and distributrs details, but so that if a new customer or distributer is added to the corresponding table it is automatically updated in the table containing both the customer and the distributorsdeatils

You don't do this as distributors and customers are different although I don't know about what structure you have went with to comment much further at the moment. As Loolaa says, a UNION query will let you combine similar fields between the two tables should you just be looking for a list of contact details.

this is just something my boss wants

Sometimes they can want but they don't understand the rules, etc. that underpin the creation of a database and, I see, neither do you. It is essential to understand firstly what it is you are trying to model in Access and then to understand the concepts of data normalisation (a subject on which there have been numerous posts on the forum [search function and Google are always handy ;)]).

..and as yet i havent been able to figure out how...but it seems very simple...which is very infuriating!!

It's not simple; it's wrong.
 
Do you use forms to enter the new data?

An append querie solves this problem , however it may be easier to approach the problem in reverse. Address book table perhaps where you can select either distibutor or customer? Using a select query will then filter the main table or use the make table query if he insists on a table. Then base a report or form on the query or made table?

Use the append query to update the fields in the third table but you will have run this twice to update your current records in "Table 3"
With all your data in one table then split data of from it then its far easier.

Hope this suggestion helps :) :)
 
i have two tables and i want to create another table that contains both the information in the two alrady established tables, and whenever the previously established tables are altered the table containing data from both tables is automatically updated

If this is what your boss wants at the technical level, find a new job. He's an idiot. (You may show him this and quote me if you decide that you need a new job.) But on the odd chance that he gave you a results requirement rather than a technical requirement,...

Normalization rules would prevent you from doing what you described because they would lead to duplicate data. You would spend the rest of your life trying to maintain a database that no one would love. You would be frustrated as hell. And your database would be bloated beyond belief.

BUT... if you put a prime key in your customer table (perhaps even a simple autonumber field) and a prime key in your distributor table (perhaps another simple autonumber field), your THIRD table only needs to copy the keys of the customer and distributor, not the rest of the data. Updates to either table are available to the third table in potential.

Now, when you USE the third table, use it with JOIN queries back to the customer and/or distributor tables. This way, if an update has occurred in either table, it is there when you call up that data through the JOIN.

Just as a side note, in any table that shows both customers and distributors in the same table, you have the potential for a many-to-many relationship, which would vex you worse than the mutual update issues. You seriously need to learn more about normalization and data relationships because in the situation you describe, you are going to need it.
 

Users who are viewing this thread

Back
Top Bottom