Make report with records from 2 tables

[RobJ]

hello,

I'm trying to make a report with records of 2 tables. The report it should show the actual costs and budgeted costs.

The report selects on store and kind of costs. Selecting this information should result in de budgeted costs and actual costs for this store and these kind of costs.

The budgeted costs are in one table, the actual costs are in the second table.

Some actual costs have no budgeted costs with which they can be compared (and the otherway around).

Does someone know how i can make a report in which these costs are shown?

RobJ

 

[Robert Dunstan]

You need to create a query that pulls the records from the 2 tables and base your report on the query.

How are the 2 tables related to one another? Can you explain a bit further

Rob

 

[RobJ]

I posted an example of my tables. I want to make an report like this:

Sort by Storenumber,
Sort by articelnumber,

budgeted sales, sales

At storenumber I want all storenumbers shown (So the ones from tblbudgeted and from tblSales)

Tne I want for each storenumber the articelesnumbers which are known for these stores (again from both tables).

if a storenumber has a articlenumber in both the tables, the budgeted and the real sales should be shown.

if a storenumber has an article in just the budgeted table, only the budgeted sales on the report should be filt.

An the other way arround.

Do I make sence?

RobJ

 

[Robert Dunstan]

Hi RobJ

OK I've downloaded your db but I need to make sure I understand what you want.

One thing that springs to mind firstly is why do you need to keep budgeted sales and actual sales in seperate tables? Should the two tables be related to one another?

 

[RobJ]

they are seperated because I import them as a text file. So I import two different files. Second reason is because the db I uploaded is not the real database (to big for upload). In the real database there are a lot more columns in each table.

In the report I want to make, all the stores should have there own list with articlesnumber. after each articlenumber should stand the bugeted sales and after that the actual sales.

If there are budgeted sales but no actual sales, the budgeted sales should appear and the field of the actual sales should stay empty.

This is also the other way around. So budgeted sales emty and actual sales shown an ammount.

Is it clearer now?

RobJ

 

[Robert Dunstan]

Hi

Ok I understand what you want. It's that Friday feeling

I'm playing around with your db at the mo so I'll see what I can come up with. I'm due to leave work shortly so it may not be until Monday before I can send something back to you.

On further reflection you are trying to create 1 report on 2 unrelated tables. In order to do what you want, you could create 2 queries (1 for budgeted sales & 1 for actual sales) and then export the results to Excel and produce you report there.

On the other hand if you need to produce the report from Access then you would need to normalise your data. You are currently holding StoreNumber in 2 different tables.

You could have the following:

tblStore
StoreID (PK)
StoreNumber
Etc..

tblArticle
ArticleID (PK)
StoreID (Link to tblStore)
ArticleNumber
BudgetedSales
ActualSales

 

[RobJ]

thx so far, hope to hear from you after the weekend.

RobJ