How to move data from one source file with Many to another with one?

[wipackerfan]

I have a table with pricing history, up to 5 revisions, by part number (the part number can be listed either once or multiple times depending upon the number of revisions). I am attempting to create a new table with the price history which is keyed by Part Number (only one part number per record), which also has multiple other items from other tables. Most of the information is one-to-one, however the pricing is a one-to-many. I have not been able to figure out how to do a join, or query, or ??? to acccomplish. Sounds simple, but I am really stumped. Any ideas

 

[Trendsetter]

This "sounds" straightforward!
In the query builder bring both table to the grid - Product table with Part Number & the table of price revisions.
Drag the join from Part No in Products to Part No in Revisions.
In the grid drag in the fields you want to see Product Name, Part No, Date, Price, Unit etc.. etc..
Set the order by Part No then Price... hopefully this will give you what you are looking for.

 

[wipackerfan]

It does sound straight forward, but I am attempting to get multiple prices into a table with only one part number. So I am going from a table with the same part number listed multiple times to another table with the part number listed only once, but I also need all the price history into the table with the part number listed only once?

Sounds simple, and logical, but I have been struggling to accomplish this for several days, with help from others as well.

 

[Trendsetter]

It sounds to me as though you want the database to look like a spreadsheet! there you can put a header for information - Part no. and then alongside that list the history of the prices without repeating the Part no.

The situation you have in a database is data that is "normalised" - this means that you hold a table with a unique reference as a key field (no duplicates) and for multiple references to that part number, such as price fluctuations, you store that info in a related table. This way if you have a hundred fluctuations for one part number you can store them. If on the other hand you have no fluctuations for another part number then you do not waste any space by leaving room in case. This is the classic one to many table relationship and is the basis of relational database design.

 

[jds92257]

It sounds like you need a secondary key which had the revision number. Part No., Rev. No.

 

[MichaelRed]

Sounds MORE like de-normalization!

e.g. don't do it!

The price (or any historical tracking) should have a seperate record for each 'event', and the history would need to include the data / time of the change to be of any real use.

e.g. don't do it!



MichaelRed
m.red@att.net

Searching for employment in all the wrong places