Hi,

I am a 'newbee' in Access.
I am looking for a way to produce a report where in one report I can see my sales an purchase orders (tables that are not/cannot be linked)

Sales table fields:
product, Customer, qty, Salesprice

Purchase tabel fields:
Product, Vendor, qty, PurPrice

Report 'fields':
Product, Relation, qty, Price

Any idea on how to do this?

Thanks,
Richard

What do you mean that the tables cannot be linked? If you want data from two tables, your choices are

1. main report based on a query that joins the two tables
2. main report based on a union query that brings the separate recordsets into a single recordset
3. unbound main report with two subreports. One subreport for each separate RecordSource.

The tables have no logical common data; Sales and Purchase are not linked.
The result of the query should be a set of rows that contain the data (both sales and purchase) as I mentioned.
I am not sure which option of the ones you mentioned I should use.
Could you give me a short example on how I should do this? I looked through the Microsoft examples, but could not find anything that makes sense to me.

Many Many thanks in advance.

Richard

Of course they're related, via Product. They are also related on the financials, but that's a different story.

Since we don't know what you're trying to accomplish with this report (i.e. the real live business requirements), we cannot advise you on how to join the data. What did the business user say they wanted?

ok
The end users want a query that contains both the salesorder details as well as the purchase order details. It is for them a method to fool around with liquidity and stuff.

I added a sample on how this should look; the first row comes from the sales details table and has the fields: Customer, Product, QTY, SalesPrice,Orderdate
The second row comes from the purchase details table and has the fields: Vendor, Product, QTY, PurchasePrice, Purchasedate

Relation Product QTY Price Date
Cust1 SalesProd 1 25 6/28/2008
Vendor1 PurProduct 1 -20 6/27/2008

Hope this helps. Many thanks in advance.

Richard

Try a union query.

select Customer, product, qty, Salesprice, x.salesdate
from Sales inner join tablewithsalesdate as x on x.fk = Sales.key
union
select Product, Vendor, qty, PurPrice, y.purchasedate
from Purchase inner join tablewithpurchasedate as y on y.fk = Purchase.key

Please note: this is "air" code...it will not work. It is here to illustrate how to solve your problem using SQL view. You didn't mention you wanted dates in your first post and did not show where dates come from (so I made it up based on...nothing).

If you need more help, post more details about your table structure along with your request. In the meantime, look up "union" and "union all" in help.

HTH.

Hi George,

Thanks for the input.
Found it and it worked.

Reg,
Richard

Excellent! Glad we could help.