Merge two tables

[gwog]

I use Access '97

I have three tables:

Table 1 (this is sort of a master table - that stays consistent):

Item
Location
Description
Catgory
LOB

table 2:
Item
Location
Sdate
Qty

Table 3:
Item
Location
Sdate
Base
Non-Base

What I want to do is take Table 1 which has every combination of Item/Location Possible (ie 1 item is at many loctions) and add to it the Qty from table 2 for each Sdate in Table 2. And add the Base & Non-Base for Each Sdate in Table 3. So in the end I have one table that has:

Item
Location
Sdate
Qty
Base
Non-base

My programming skills are not that great. I've tried to do this through a query - I have Item/Location as primary key in each table. But I end up with way too many records.

Any suggestions?

Thanks.

Lisa.

 

[jrbarnett]

A few queries

This one copies across the Qty and sdate fields to table1

Update table1 inner join table2 on table1.item = table2.item and table1.location = table2.location 
set table1.qty = table2.qty, table1.sdate = table2.sdate

Then a slight adaptation to copy across the data from table3:

Update table1 inner join table3 on table1.item = table3.item and table1.location = table3.location 
set table1.base = table3.base, table1.[non-base] = table2.[non-base]

This should put all your data in one table.

John

 

[gwog]

Ok, I've made an attempt at my problem above.

First I changed the 3 tables so they all have the same fields. But some fields are null in some of the tables.

Table One (HistFcst) has 1 item with 2 locations and 3 Sdate
ITEM - LOC 1 - Sdate 1
ITEM - LOC 1 - Sdate 2
ITEM - LOC 1 - Sdate 3
ITEM - LOC 2 - Sdate 1
ITEM - LOC 2 - Sdate 2
ITEM - LOC 2 - Sdate 3

Table Two (Hist) has 1 item - 3 location - 3 Sdate
ITEM - LOC 1 - Sdate 1
ITEM - LOC 1 - Sdate 2
ITEM - LOC 1 - Sdate 3
ITEM - LOC 2 - Sdate 1
ITEM - LOC 2 - Sdate 2
ITEM - LOC 2 - Sdate 3
ITEM - LOC 3 - Sdate 1
ITEM - LOC 3 - Sdate 2
ITEM - LOC 3 - Sdate 3

Table Three (ALLITEMLOC) has 1 item 3 locations
ITEM - LOC1
ITEM - LOC2
ITEM - LOC3

I created the following union query:

select ITEM, DESCRIPTION, LOCATION, LOB, CATEGORY, LAG, Fdate, Sdate, base, NonBase, qty FROM HistFcst

UNION select ITEM, DESCRIPTION, LOCATION, LOB, CATEGORY, LAG, Fdate, Sdate, base, NonBase, qty FROM Hist


UNION select ITEM, DESCRIPTION, LOCATION, LOB, CATEGORY, LAG, Fdate, Sdate, base, NonBase, qty FROM ALLITEMLOC
ORDER BY ITEM;

What I end up with is this
item - loc - with 7 Sdate (1 blank and a duplicate sdate1, sdate2, sdate3)

Any ideas?

Thanks.

 

[jrbarnett]

My suggestions were update queries based upon your original structure to pull everything into table1.

I don't quite understand what you are trying to get to with your Union query.

John

 

[gwog]

John,

I tried the queries you suggested.

The problem is I don't get all the data. I get 6 records total - 1 start date for each location.

What I need in the end is:

item1 loc1 sdate1
sdate2
sdate3
loc2 sdate1
sdate2
sdate3
loc3 sdate1
sdate2
sdate3

item2 loc1 sdate1
sdate2
sdate3
loc2 sdate1
sdate2
sdate3
loc3 sdate1
sdate2
sdate3

So each item will have 9 records, For a total of 18 records.

make sense?

Thanks.

Lisa.

 

[jrbarnett]

Lisa,

Having now looked at your structure, it tells me that while it is possible to put everything in one table, it would not be fully normalised (ie repeated data removed). You don't mention where the Qty, Base and Non-Base fields should appear in this as well, but the following structure will show the data you request:

Table: Items (itemno primary key)
Table: Locations (LocNo Primary key, Itemno foreign key into items.ItemNo)
Table: SDates (Itemno Foreign key to Locations.Locno and Items.ItemNo composite primary key; SDate )

Then it becomes easy to pull it off with the following SQL query:

Select Item.ItemNo, Location.LocNo, SDates.SDate
From Locations Inner Join Item On Locations.ItemNo = Item.ItemNo
(Inner Join SDates on Locations.LocNo = SDates.LocNo And Locations.ItemNo = SDates.ItemNo)
Order By Item.ItemNo, Location.LocNo, SDates.SDate

For your report, put groups on the ItemNo field, a second on Location and in the detail band put
the SDate field in the detail band, with item and location in each of the appropriate group headers.

John