Merging table info (not union)

[JMG]

I have 4 tables of info to merge into one.
1. tblForecast
fields: style,color,size,week,units
2. tblWIP
fields: style,color,size,week,units
3. tblOnOrder
fields: style,color,size,week,units
4. tblOnHand
fields: style,color,size,week,units

I need to get all four tables into one
tblStatus
fields: style,color,size,week,FCunits,WIPunits,OHunits,OOunits

one row of info for each sku/week
I have a module that loops thru each week/sku and calculates the available to sell based on each weeks status and the previous weeks available.

any advice will be appreciated.

 

[gblack]

Ya

Use a Union query. Then you can create a make table query from it, to create the one big table you need. You can stack all the table into one, provided they all have the same fields.

The Union query can only be done in the SQL editor...not in the GUI, but its pretty easy to write.

 

[JMG]

thanks, GBlack, but

union query gives me too many records. The goal is to have one record per size/per week.
thanks for your response.

 

[ecniv]

I have 4 tables of info to merge into one.
1. tblForecast
fields: style,color,size,week,units
2. tblWIP
fields: style,color,size,week,units
3. tblOnOrder
fields: style,color,size,week,units
4. tblOnHand
fields: style,color,size,week,units

I need to get all four tables into one
tblStatus
fields: style,color,size,week,FCunits,WIPunits,OHunits,OOunits

one row of info for each sku/week
I have a module that loops thru each week/sku and calculates the available to sell based on each weeks status and the previous weeks available.

any advice will be appreciated.

If you can reorganise your tables then try this:
tblStyle
StyleID - auto
style
color
size

1. tblForecast
fields: styleid,week,units
2. tblWIP
fields: styleid,week,units
3. tblOnOrder
fields: styleid,week,units
4. tblOnHand
fields: styleid,week,units

Then these tables can be linked together on styleid
or

tblInformation
InformationID - auto
typeofinfo (for Forecast, on hand etc)
Styleid
week
units

The sql statement will probably be large for both but you can use the query builder.


Vince

 

[JMG]

Thanks very much for responding..

I have resolved the problem by--

1. union query pulling 4 tables together (this gave me up to 4 records for each sku / week.

2. select query (using the union query) and using
WIP:sum(Iif([x]="wip",[units],0))
OnHand:sum(Iif([x]="on hand",[units],0))
Forecast:sum(Iif([x]="forecast",[units],0))
OpenOrders:sum(Iif([x]="open orders",[units],0))

this gave me one record for each sku / week
sorry I have taken so long to post this but because of the density of bone matter upstairs it has taken me this long to solve. it would have taken longer without your input.

Thanks again.