Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I am looking to combine 21 tables in access so that 1 table shows all the information. I do not want to use a query, as the tables do not get updated if new info is added. Is there a way of getting 21 tables to be made into one, without copy and pasting, i want to have relationships made up. any help would be great.
You create relationships by clicking on a field type and dragging it across to the relevant table. For each table to update, each table will have to be linked by a 1 to Many relationship.When creating a relationship you will be given options check boxes Enforce Referential Integrity and Cascade Update Related Fields.
So say you were to create a database called say Cottage Holidays with the Tables Booking, Client and Property and each of the unique identifiers(Primary Keys) say BookingCode, ClientCode and PropertyCode.
link the tables by Booking as each Booking will have a Client and a Property, so you could link the Primary key PropertyCode and ClientCode to the Fields in Booking named PropertyCode and ClientCode. Enforce Referential Integrity and Cascade Update Related Fields all tables will have a 1 to Many relationship and can be updated from anywhere.
A Client has 1 or many Bookings
A Property has 1 or Many Bookings
and so on I hope this is of some help as I have confused myself now.
M
0 
To give a better explanation possibly, I would need more information about the database you are trying to create. What tables you are using and the table fields.
M
0
If you just literally want to stack data, rather than create relationships, then use a "Union query".
[for eg. Say you have price data for stock X in one table for year 2000, another for year 2001 and another for 2002 and you just want them all put together.]
Union queries are SQL specific, but not exactly rocket science.
Post back if thats what you meant.
Tom
0
I have 21 tables with different peoples names in and email address, what i want is one table with all the names and emails in which can be updated by the smaller 21 tables. The new table will be the main table holding all the info. Do you understand now?
0
I think so.
A table cannot update another table, though. I presume then, that you mean that you want a user to be able to change data in one table and that will change the data in the main table.
If that is the case, then you have many choices, two of which are:
1) The 'by far the most sensible' choice, is not to have 21 different tables, but have a form which shows the contents of the main table filtered by a specific cut which a particular user has control of.
2) Have 21 seperate tables and a union query which you run to combine the data together whenever you want it to.It all depends on exactly how you want the system to work. Which data is "master", how many users, who can see what, what feeds what (ie. Does the holding table populate the 21 before the users update them, or are the 21 tables really the masters which feed a summary table for you.) etc etc etc
I know this seems vague. It is.Tom
0
The form sounds the solution, well done. One question that you may be able to help with, is can you delete information in the table through the form. we need to delete info on the tables, but it would be easier if there was a way of deleting them in the form, rather than searching the tables.
Thanks for the help
Skint
0
Yes - thats exactly the sort of thing that Access is such a usable tool for.
Have a form which is an interface, and a subform which is based on your table data (sounds like you would want Datasheet view).
Any changes to the visible table immediately take place in the actual underlying table, since they are one and the same.
Make a simple structure for yourself to experiment with, then when you are happy with it, start to think of how you want the data table to be filtered. Then, rather than have a subform based on a table, you have it based on a select query which filters your main table. The end result is the same, but its one further step of knowledge.
[When you have done all this, then you can start to worry about ways of backing up the data before some idiot trashes it...!]
Tom
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
