Combing Records

[ASherbuck]

In the database I have inheritted I have records from two locations that use the same structured backend. The problem I have is I want to combine them. Example Below:

TableSale
SaleID *
Date
Total
Location


TableSaleDetail
DetailID *
SaleID <
ItemID
ItemPrice
Quantity

* = Primary Key
< = Foreign Key

The problem is that I can have a Sale #1 from both locations.

This is an H2O'd down version, but that's how the tables are set up. When condensing the records into a single back end I thought of making a new table with a new Primary key for Sales Records, taking the No Dups option off of the SaleID, removing the PK, and then just linking that to the SaleDetail table. By giving a location criteria in my queries it would eliminate pulling the wrong sale data, but it would pull the wrong detail data.

Flowing this data in is going to be a regular (weekly) event, I'm trying to figure something out. Any help is always appreciated.

 

[MSAccessRookie]

In the database I have inheritted I have records from two locations that use the same structured backend. The problem I have is I want to combine them. Example Below:

TableSale
SaleID *
Date
Total
Location


TableSaleDetail
DetailID *
SaleID <
ItemID
ItemPrice
Quantity

* = Primary Key
< = Foreign Key

The problem is that I can have a Sale #1 from both locations.

This is an H2O'd down version, but that's how the tables are set up. When condensing the records into a single back end I thought of making a new table with a new Primary key for Sales Records, taking the No Dups option off of the SaleID, removing the PK, and then just linking that to the SaleDetail table. By giving a location criteria in my queries it would eliminate pulling the wrong sale data, but it would pull the wrong detail data.

Flowing this data in is going to be a regular (weekly) event, I'm trying to figure something out. Any help is always appreciated.

Isn't that the way it is supposed to be? I mean that the TableSale has unique sales IDs and related Information, while the TableSaleDetail can have more than one SaleID that refer back to the other table.

For Instance, Sale #1 can be for 2 Widgets, 5 Thingamabobs, and 4 Doohickeys, each of which would have their own record in the Table TableSaleDetail indicating that they are part of Sale #1

 

[ASherbuck]

Yes, it is how it should work, but the dilemma is now with 2 seperate locations, with their own respective back ends.

Location1 has it's own data, it has a sale and the ID is 1. Now Location2 does a sale and it's ID is 1. Whilst you can seperate the Sale tables based on their location, the SaleDetail table would still have a 1 for both of those sales. So if the tables are combined you would pull all the records of both stores, regardless of which location you specify.

The problem arises when data is taken from both backends from the two locations and put together at our home location.

 

[WayPay]

The primary key for TableSale should be (Location, SaleID).
The primary key for TableSaleDetail should be (Location, SaleID, ItemID).
The TableSaleDetail.DetailID column would then be unnecessary.

In short, as it is, the TableSaleDetail misses a Location column (I hope that's a LocationID) to keep the rows unique.