combining 2 tables into 1 using queries

jrhessey

Registered User.
Local time
Today, 01:02
Joined
Nov 3, 2004
Messages
29
I've been asked to make a DB for our production. We have 2 sources, of which have similar field names and collect completely different data, but can not be linked. I don't want them to be linked either, I don't think. The only thing I want to do is to take the 2 tables I have from my 2 sources and combine them into one. I'm using the first table in a make table query and then creating blank fields in the query also, so I can append the data from the second source in the "all production" table. The only problem I'm running into is that I can only get to 26 fields in the make table query, I need 31 fields to get everything. I tried creating a new query and not using any tables, and I was able to get to 16 fields. Is there something I'm doing wrong, or is there another way I can get a table created will all fields I need and append the data from both sources? I would need to be able to re-run this daily which is why I was trying to do it through a query. Thanks for any help/ideas. It's greatly appreciated!
 
Not at all clear what you are trying to do. Could you have another go at explaining, perhaps with some sample data?
 
ok, here's how I have it set up.

Run the macro called "Create_ProMas90_Table"
that creates the tables "BMI_ProductionHistoryFile" and
"WO1_WorkOrderMaster", then opens the queries "Make_ProMas90_with_BMI"
and "Append Pro_Mas90_with_WO1" which makes the table called
"Production_Mas90".

Now what I need to do is get the tables "Production_Mas90" and "Production_Database" combined into one table. The data between the 2 is not related in anyway, therefore I am not trying to link the tables. I was trying to using a make table then append table query to accomplish it but I can't fit all the fields in the query. I need all of the fields from the "Production_Mas90" table and all but 3 fields, because of similar field names, from the "Production_Database" table.

Hope this explains it, should be a lot easier once you see the DB also. Thanks for your help!

production.zip
 
OK, I got the file. I don't think you've done a repair and compact for a while, have you? It reduced the file size by 75%

You have got a lot of work to do. First, you should not need to be running make table and append queries to handle your data. Any place you can use a table, you can use a query. So all you are doing is duplicating data.

I haven't got my head round how this is supposed to work, so I'll try and take a look in the morning with a fresh mind.
 
wow! That did reduce the file size quite a bit! I figured I had a way to go, I've haven't done to much with Access. Try looking at it like this. All I need are the "BMI_ProductionHistoryFile", "WO1_WorkOrderMaster", and "Production_Data" with all of the fields that are in each table combined into one table. That will give me all of my production data from a given day. Does this help any? Thanks!
 
I thought this might help, so I zipped everything we have now in excel sheets. The 82406.xls has the daily sheet and mtd sheet in it. It also has all of the job sheets that link to the daily and mtd sheet. The "production items.xls" is the BMI table in the DB. The "items master.xls" is the WO1 table in the DB. You can look at the formulas and who we are pulling the data into the daily and monthly reports.

Excel-Production
 

Users who are viewing this thread

Back
Top Bottom