Two databases to create one report

[ash128kwil]

Hi I am having some trouble getting my head around the following,



One database displays bought items, model no, price, date etc. Another contains items sold.

I want to create a report to show the stock level of an item so bought minus sold and also a report on say profits.

How do I go about doing this in access as I'm fairly new to the software.

Thanks a lot
Ash

[June7]

Do you have a table of all products in your inventory?

Do aggregate (GROUP BY, Totals) queries on the Purchases and Sales tables.

Join the aggregate queries to the Products table.

Use that query as the RecordSource for a report.

It will get a bit more complicated if you want to restrict the data over a specific period and need 'Beginning Balance' value for the calcs.

[jake32008]

I'm by no means an expert as I am also fairly new compared to many in this forum but you can pull in the relevant tables with the information you need from the other database from selecting 'External Data'=>'Import & Link'=>'Access'=>Browse for table=>'Link' so the table is dynamic and updates as the other database updates. Once you pull this in you'll be able to pull in the data your wanting for the report. This is by no means comprehensive but I hope it is clear enough to help.

[ash128kwil]

Hi again,

All i have currently is two databases.

Bought and Sold

I am really quite new to access and the things you have said I have no idea what they mean! I will do some research into what you have said and see what I come up with!

If you have anymore "Noob friendly" advice I would be grateful!

Thanks again,
Ash

[June7]

ash, do you have two databases (meaning two separate files) or two TABLES (meaning two datasets in the same file)?

Did you attempt my suggestion? Access Help has detailed guidance on building aggregate (Totals) query. This is basic Access functionality and any introductory reference book should also have info.

[ash128kwil]

Hi, I currently have 2 tables (sorry my access lingo is not up to scratch!)

I will have a look into the building aggregate thanks for the suggestion!

Ash