doddwell
asked on
Append Multiple Tables to Temp Table
Hello
I have 7 Purchase Order Tables
tblPOCo1,
tblPOCo2,
to tblPOCo7
Each table has an identical structure.
I have another table tblJob. Each Purchase Order has a unique Job number from tblJob.
If I had just one Purchase Order table I could create an inner join between tblJob and tblPOCox.
How should I do the same but with 7 Purchase Order Tables (rows tblJob will have one matching PO in any 1 of the 7 PO tables) .
I know how to append all of the 7 tables into one using Union All but don't know if this is the right route and if it is what I should do next. If the answer lies with a temp table please descibe.
Many Thanks
I have 7 Purchase Order Tables
tblPOCo1,
tblPOCo2,
to tblPOCo7
Each table has an identical structure.
I have another table tblJob. Each Purchase Order has a unique Job number from tblJob.
If I had just one Purchase Order table I could create an inner join between tblJob and tblPOCox.
How should I do the same but with 7 Purchase Order Tables (rows tblJob will have one matching PO in any 1 of the 7 PO tables) .
I know how to append all of the 7 tables into one using Union All but don't know if this is the right route and if it is what I should do next. If the answer lies with a temp table please descibe.
Many Thanks
ASKER
We are an umbrella company. 'Inside' the umbrella there are 7 separate companies. I thought about putting all of the POs into one table when I designed the system but thought that this would not give me consequtive PO numbers for each company. In retrospect perhaps I should have looked into this further and found a way of creating PO numbers with a suffix. But it's too late now!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm usong Stored Procedures. How would you have achieved unique company specific consequtive numbers within the same table?
I have managed to merge the contents of all of the tables using a view (vwAllPOs) which uses Union All. I then execute an SP which is a Select of vwAllPOs Inner Joined to another table. This works for me - so now I'm even more confused about temporary tables and their benefit.
I use an Access Data Project as the front end. With my solution above I get to see all the records. In your last example you suggest a temp table....how do I see the records at the front end if I use a temporary table (I know how to create them and get records in there but don't know how to get data back out!)
Many Thanks
I have managed to merge the contents of all of the tables using a view (vwAllPOs) which uses Union All. I then execute an SP which is a Select of vwAllPOs Inner Joined to another table. This works for me - so now I'm even more confused about temporary tables and their benefit.
I use an Access Data Project as the front end. With my solution above I get to see all the records. In your last example you suggest a temp table....how do I see the records at the front end if I use a temporary table (I know how to create them and get records in there but don't know how to get data back out!)
Many Thanks
Adding a CompanyId to your Tables and making CompanyID+POId (or whatever id) a Unique Index.
To view Temp Variable tables back just use it like a regular table like:
SELECT tblPOCo, OtherField1 FROM @BigTable
Now. I'm not recommending you use temp table variables since I don't know what you need to do with the result.
To view Temp Variable tables back just use it like a regular table like:
SELECT tblPOCo, OtherField1 FROM @BigTable
Now. I'm not recommending you use temp table variables since I don't know what you need to do with the result.
You'd want to have the company ID, or something equivalent, in your single PO table so that you could identify which PO's were for which company (assuming that you don't already have that). To generate a separate sequence of PO numbers for each company, you'd just need to have a separate next (or last or current) PO number stored somewhere for each company. For example, if you had a table with company info (ID, name, etc.), you might put the field there. Then the software that assigns the PO numbers would just use the appropriate field for each company.
The concept is simple enough. How easy it would be to implement will depend on the specifics of your situation.
James
The concept is simple enough. How easy it would be to implement will depend on the specifics of your situation.
James
something like this will give you consequtive PO numbers for each company
select
case when tblPOCo1.ponumber is not null then tblPOCo1.ponumber
case when tblPOCo2.ponumber is not null then tblPOCo2.ponumber
case when tblPOCo3.ponumber is not null then tblPOCo3.ponumber
case when tblPOCo4.ponumber is not null then tblPOCo4.ponumber
case when tblPOCo5.ponumber is not null then tblPOCo5.ponumber
case when tblPOCo6.ponumber is not null then tblPOCo6.ponumber
case when tblPOCo7.ponumber is not null then tblPOCo7.ponumber
end ,
tbljob.column1,
tbljob.column2
from
tbljob
inner join tblpocox
on
tbljob.column1 = tblpocox.column1
left outer join tblPOCo1
on tblpocol.PO = tbljob.po
left outer join tblPOCo1
on tblpoco2.PO = tbljob.po
left outer join tblPOCo1
on tblpoco3.PO = tbljob.po
left outer join tblPOCo1
on tblpoco4.PO = tbljob.po
left outer join tblPOCo1
on tblpoco5.PO = tbljob.po
left outer join tblPOCo1
on tblpoco6.PO = tbljob.po
left outer join tblPOCo1
on tblpoco7.PO = tbljob.po
select
case when tblPOCo1.ponumber is not null then tblPOCo1.ponumber
case when tblPOCo2.ponumber is not null then tblPOCo2.ponumber
case when tblPOCo3.ponumber is not null then tblPOCo3.ponumber
case when tblPOCo4.ponumber is not null then tblPOCo4.ponumber
case when tblPOCo5.ponumber is not null then tblPOCo5.ponumber
case when tblPOCo6.ponumber is not null then tblPOCo6.ponumber
case when tblPOCo7.ponumber is not null then tblPOCo7.ponumber
end ,
tbljob.column1,
tbljob.column2
from
tbljob
inner join tblpocox
on
tbljob.column1 = tblpocox.column1
left outer join tblPOCo1
on tblpocol.PO = tbljob.po
left outer join tblPOCo1
on tblpoco2.PO = tbljob.po
left outer join tblPOCo1
on tblpoco3.PO = tbljob.po
left outer join tblPOCo1
on tblpoco4.PO = tbljob.po
left outer join tblPOCo1
on tblpoco5.PO = tbljob.po
left outer join tblPOCo1
on tblpoco6.PO = tbljob.po
left outer join tblPOCo1
on tblpoco7.PO = tbljob.po
/*slight syntax error in last post use this one*/
select
case when tblPOCo1.ponumber is not null then tblPOCo1.ponumber
when tblPOCo2.ponumber is not null then tblPOCo2.ponumber
when tblPOCo3.ponumber is not null then tblPOCo3.ponumber
when tblPOCo4.ponumber is not null then tblPOCo4.ponumber
when tblPOCo5.ponumber is not null then tblPOCo5.ponumber
when tblPOCo6.ponumber is not null then tblPOCo6.ponumber
when tblPOCo7.ponumber is not null then tblPOCo7.ponumber
end ,
tbljob.column1,
tbljob.column2
from
tbljob
inner join tblpocox
on
tbljob.column1 = tblpocox.column1
left outer join tblPOCo1
on tblpocol.PO = tbljob.po
left outer join tblPOCo2
on tblpoco2.PO = tbljob.po
left outer join tblPOCo3
on tblpoco3.PO = tbljob.po
left outer join tblPOCo4
on tblpoco4.PO = tbljob.po
left outer join tblPOCo5
on tblpoco5.PO = tbljob.po
left outer join tblPOCo6
on tblpoco6.PO = tbljob.po
left outer join tblPOCo7
on tblpoco7.PO = tbljob.po
select
case when tblPOCo1.ponumber is not null then tblPOCo1.ponumber
when tblPOCo2.ponumber is not null then tblPOCo2.ponumber
when tblPOCo3.ponumber is not null then tblPOCo3.ponumber
when tblPOCo4.ponumber is not null then tblPOCo4.ponumber
when tblPOCo5.ponumber is not null then tblPOCo5.ponumber
when tblPOCo6.ponumber is not null then tblPOCo6.ponumber
when tblPOCo7.ponumber is not null then tblPOCo7.ponumber
end ,
tbljob.column1,
tbljob.column2
from
tbljob
inner join tblpocox
on
tbljob.column1 = tblpocox.column1
left outer join tblPOCo1
on tblpocol.PO = tbljob.po
left outer join tblPOCo2
on tblpoco2.PO = tbljob.po
left outer join tblPOCo3
on tblpoco3.PO = tbljob.po
left outer join tblPOCo4
on tblpoco4.PO = tbljob.po
left outer join tblPOCo5
on tblpoco5.PO = tbljob.po
left outer join tblPOCo6
on tblpoco6.PO = tbljob.po
left outer join tblPOCo7
on tblpoco7.PO = tbljob.po
Why do you have 7 PO tables?
Regards,
Patrick