collating data from one 40 tables to one

[rainbows]

hi ,

I have 40 tables of which are all the same regards headings etc

for example drawing number , description , drawn by ,

each table hold a drawing registor for certain activities for example

rotor , bearings, windings etc

I now need to put all this into one table.

what i would like to be able to do is when i put any drawing number into any one of the 40 tables it will go into the master table

any advice how I can go about this

thanks steve

 

[isladogs]

Why not just do this all at once?

Use an append query from each table to the master table in turn.
Then delete the original table as you go.

Mind you, the db should never have had 40 tables with the same structure in the first place

 

[DatabaseSolutions]

I agree with Colin here,

You should never have got to this situation in the first place. Looks like you want to continue with this structure but also have a master table? - I don't think its the best way to go with this.

Have your one table which an extra fields distinguishing between the types of parts (FieldName: PartType) and then a drop down selecting the relevant part, motor, bearing etc.

Visit us: access-database-solutions.com
Subscribe to our YouTube Channel: Access Database Solutions

 

[rainbows]

hi,

each table is called a name as you said for a reason, I table called be called " rotor " this holds all the drawing for rotors , 1 table called stator this hold all the drawings for stators

this is easy for giving them a unique number ie p111-001 p111-002 in rotors
says p222-001 and p222-002 for stators etc etc

and they are easy to find and allocate the number

but what I really need to do is have all that information available from one drop down box to select from and the only way I new was to create on table and query that one table , although I would have preferred some how to leave them all separate

so at present I have started to put them all in one table and query the code so that I can still put the unique number in


steve

 

[The_Doc_Man]

If all of the fields are the same definition in all 40 tables, you fix this by adding one field to describe which type of thing this record describes, whether it is a rotor, stator, or left-handed veeblefetzer. Then you can append the tables into a master table that contains all the original data including that new field that tells you what kind of thing was being described. Having 40 tables when one will do is a denormalized database. The really sad news is that the structure you could have used to merge the tables, a UNION query, has a limit of (I think) 16 member SELECT queries, whereas you need 40.