Is this possible?

[metrodub]

I'm unsure how to do this, or if this can even be done. I have four tables:

tblCompany
CompanyID (PK)
CompanyName
...company data

My company works with outside vendors (companies) to do facilitation. Before a vendor is brought in, we need to get authorization from Finance to increase the Purchase Order (PO). Each vendor has an unique PO Number that changes every year.

tblPO
POID (PK)
CompanyID (FK)
PONumber
POAmount
AmountPending
POYear

tblInvoices
InvoiceID (PK)
POID (FK)
InvoiceNumber
InvoiceDate
InvoiceDesc
InvoiceType
InvoiceAmount

tblProjects (Used to increase the PO)
ProjectID (PK)
POID (FK)
Project
Amount
Status
CreationDate

What I'd like to do is create a query that would position the data as such:

PONumber InvoiceDesc InvoiceAmount Project Amount
123456 Facilitation $2,000 XXXX XXXX
123456 XXXXXXXX XXXXX Work $5,000

The XXXX's represent no data. Is this possible to do? If so, what are the appropriate steps to get it to work?

Thanks in advance.

 

[stephenk1973]

Could use a union query, would look something like...

select PONumber, InvoiceDesc,InvoiceAmount, null as Project, null as Amount from tblInvoices
Union
select PONumber, null as InvoiceDesc,null as InvoiceAmount, Project,Amount from tblProjects

Hope this helps

Stephen