Combining data

[laxster]

Alright,

So I have two different tables, Table 1 that includes basic order information and Table 2 that includes dimentions for each part number. Unfortunately, there is no primary key that I am able to use for each record.

In layman's terms, how can I create a Table 3 which combines the data for the basic order information and the parts within each order? The fields that are consistent are the part number and the order #. I am at a loss for how to do this, though.

If Job & Part # in Table 1 match Table 2, Table 3 will show the cubic volume and cubic weight of the part from Table 2.

Any help would be greatly appreciated!

 

[Scooterbug]

You dont need a third table.

Table 3 will show the cubic volume and cubic weight of the part from Table 2.

Cubic Volume and Weight are calculations based off of the part dimensions. Calculations can be performed in queries and it's never a good idea to store calculations.

As for relating the order information from Table1 to the parts in Table 2, you will need to join the tables by something in a query. Is the part number stored in the Order info table, or is the order number stored in Table 2?

Without having a primary key/foreign key setup, I would use the part number or order number to create the join.

 

[laxster]

You dont need a third table.

Well, the reason I have a 3rd table is because the data from the 3rd table gets run through a Macro which spits out the data into Excel. I'm "inheriting" this database and working around what's already been programmed and done.

Cubic Volume and Weight are calculations based off of the part dimensions. Calculations can be performed in queries and it's never a good idea to store calculations.[/quote]
There are no actual calculations being performed, this is just information we have stored that gets imported into the database from another source.

As for relating the order information from Table1 to the parts in Table 2, you will need to join the tables by something in a query. Is the part number stored in the Order info table, or is the order number stored in Table 2?

It's stored in both. The issue I am running into is when I try to build a query that establishes a relationship between both order number and part number, Access refuses to update the contents of Table 3 with the information from Table 2.

Without having a primary key/foreign key setup, I would use the part number or order number to create the join.

Well, each order has multiple parts. I can't do it solely off of order number. But I also can't do it solely off of part number either, because each part is in an order and there would be a lot of duplicate data that seems like would cause Access to error out. If remotely possible, it should be based off of a joining between both part and order.

I hope this makes sense? Thank you for the response, I really appreciate it!

 

[boblarson]

Well, the reason I have a 3rd table is because the data from the 3rd table gets run through a Macro which spits out the data into Excel. I'm "inheriting" this database and working around what's already been programmed and done.

You STILL don't need another table to do this. You may not realize this, but you can use a QUERY in 99.99% of the places you would use a table. So you would use a QUERY for the data and you would run the QUERY through the Macro which spits out the data into Excel.

 

[laxster]

I attached the query.

Overstock Kitting is Table 3. Bin Location is Table 2 and includes the dimensions.

Please omit the "Table 1" I provide as a reference above, Table 3 includes the information needed.

I tried linking both TargetPart and TargetJob from Overstock Kitting to their associated columns in the Bin Location table.
I tried linking TargetPart from Overstock Kitting to the Bin Location table.
I tried linking TargetJob from Overstock Kitting to the Bin Location table.

I've tried doing multiple things with what should be a simple update query, and nothing works. What should I be linking together here? I'm at a total loss, it seems like this should be easy and yet I can't figure it out.

 

[Scooterbug]

As Bob said...you dont need to create a table. This is where the problems are being encountered. You are focused on solving the problem of getting data into a table that the answer actually lies right next to you.

As for the query...you can have more than one join. Put joins on both the Order number and the part number. Make it a select query and see if that fixes the problem. If so, you can then use that query in the macro in order to export it into Excel.

 

[laxster]

I don't really want to go in-depth as to why there is a Table 3 (I agree with you guys), rest assured that it is necessary due to other manipulation that goes on and how the end user will be adding new data. I agree with you whole-heartedly, and the database is not designed how I would choose. However, I have to work within the restraints.

I tried linking both Job and Part together, and when I run the query it just gives me a lot of nothing -- the columns just don't update properly if I create a select query (they are still blank). It seems like something small should be under my nose. At the risk of sounding stupid, what should this look like, line-for-line in the query? Something is eluding me, and I'm not sure what...

 

[Scooterbug]

is there any way you can post the db with a bit of data in it (At least include the two tables that are being discussed)? If you are using A2007, save it in A2000-A2003 format, since it will get you the best results.

 

[laxster]

Sure thing. I've gone ahead and attached it in a Zip file.

Basically, what happens is that someone imports the Overstock Report from an application we have, and imports it into tblOverstockReport. They then import the Bin Locations.

Macros are set to run several queries, which "massage" the data a bit (You'll see what I'm talking about).

I did attempt to take your suggestion of just creating a Select Query, combining everything I need for the Overstock Kitting into 1, but I still face the same issue. You can find this query titled "qryOverstockKitting - Select".

Regardless of if it's a query or a table, I just need to get the TCube & TWgt to match the part/order numbers in the final result. Right now it simply refuses to see it.

Thank you!!!

 

[Scooterbug]

Ok...think I found out how to do this. Correct me if I'm wrong. After looking at the data, it appears that there are three criteria that dictate the dimensions of a part number. Those would be:

Bin
Job
part

I made a query:

SELECT DISTINCT [Bin Location].Bin, [Bin Location].Part, [Bin Location].Job, [Bin Location].Len, [Bin Location].Wid, [Bin Location].Hgt, [Bin Location].Wgt, [Bin Location].PCube, [Bin Location].TCube
FROM [Bin Location];

this gives you a list of the dimensions (Length, Width, Height, etc) of the parts, based on bin location and Job. The DISTINCT is in there so that it only displays one row if there are duplicate entries.

You can use this query, along with tblOverstockReport. Place joins on the following:

Bin <--> BinLocation
Part <--> TargetPart
Job <--> TargetJob

You can then add the dimension fields from the new query to find what you need. Either use the query for the Macro to export to Excel or use it to update whatever fields you need. I havent checked to see if any of the fields in the new query are updatable or not. Dunno enough about what your data represents or how it's used to test.

Regardless, You can use the newly created query just like a table to find dimension data. Remember that you need three things to do so: Bin, Job and PartNumber.

 

[laxster]

Thank you! You have really made my day!

PS: Thanks for pushing at me to get some of that data out of tables and into queries. Saved a lot of headaches, now I just need to re-write the macros. My day has gone from frustrating to happy!

 

[Scooterbug]

Glad it got worked out. And I think you will find it much easier to manage the db with the use of queries

 

[laxster]

Thanks Scooter!

While I do find it easier to manage now, I'm unable to export the queries to a 4-sheet Microsoft Excel document. When it was using tables, I was able to use the "TransferSpreadsheet" macro function to export to multiple sheets within a document. Now, my only option is to export using the "OutputTo" function, and it wants to create a new spreadsheet file for every querie instead of multiple sheets on a single document.

Is there anyway for me to accomplish this?

 

[boblarson]

Thanks Scooter!

While I do find it easier to manage now, I'm unable to export the queries to a 4-sheet Microsoft Excel document. When it was using tables, I was able to use the "TransferSpreadsheet" macro function to export to multiple sheets within a document. Now, my only option is to export using the "OutputTo" function, and it wants to create a new spreadsheet file for every querie instead of multiple sheets on a single document.

Is there anyway for me to accomplish this?

Why can't you use the TransferSpreadsheet method? It works with queries too.

 

[dbDamo]

You can use TransferSpreadsheet to export queries

 

[laxster]

Well, the problem I am encountering with that is that it spits back at me the error "Operation must use an updateable query". I'm trying to pinpoint what it is that is causing it to occur, but to no avail.

 

[dbDamo]

From Googling it seems that you either have a bad SQL statement or it is a permissions issue. Can you post the SQL statement so I can have a look at it?

 

[Scooterbug]

Well, the problem I am encountering with that is that it spits back at me the error "Operation must use an updateable query". I'm trying to pinpoint what it is that is causing it to occur, but to no avail.

I still have a copy of the db you posted...which queries are you trying to export? I was able to export the query based on the sql I posted for you earlier in this thread.

 

[laxster]

Well, most things are sorted out now. However, we do have a remaining issue with duplicates. Some jobs have two coordinators, and thus it is creating duplicate entries when this occurs. How can we sort it so that only ONE coordinator shows instead of making 2 line item entries that are identical except for the coordinator?

 

[Scooterbug]

Off the top of my head...

If you have a record that needs to record multiple Coordinators, your best bet is to create a table to record those cordinators.

tblCordinators
CordinatorLineID (PK)
CordinatorID (FK)
JobID (FK)

This way, you can have multiple Cordinators per Job...but wont have to duplicate the data. You mearly store the CordinatorLineID in the Job Table.