Best way to display "profit" per job

[Henryblah]

I've created a very simple database with 2 tables.

One is called sales which has an autonumber primary key and each entry corresponds to a job which has a total value, customer details etc. The other table is called expenses and also has a autonumber as a primary key. Again each entry corresponds to an expense with total value etc. However, there is an extra field with job id and in here I put the primary key of the sale it is linked to (if it exists, blank if it's just a normal expense not linked to a specific job). I have a relation joining the job id in expenses with primary key in sales.

Currently the relation is working as expected, access has automatically created a subdatasheet, so when I click the plus sign to the left of a sales entry, it shows a list of expenses related to that invoice.

What I am trying to achieve is a query or whatever is appropriate that displays most of the sales table (minus a few unneeded columns) with two extra columns, one that sums up the totals of all expenses per job and another column that calculates the profit per sale. In essence it would sort of be like a pivot table in excel. I've tried playing around with crosstab queries but I just can't get it to work.

 

[theDBguy]

Hi. Welcome to AWF!

Have you tried using a Totals query?

Sent from phone...

 

[bastanu]

Could you post a small sample of your db? Maybe build a small crosstab with SalesID for the rows and TotalExpenses and Profit for the columns then join that with the Sales table in another query.

Cheers,

 

[Henryblah]

Batanu, here is what my tables look like if that helps.

What you are describing is a report. Use the job table as the recordsource for the main report and the expense table as the recordsource for the subreport.

I have tried using reports as you have suggested but what comes up is a report that looks like the sales table with the subdatasheet expanded. How do I make the subreport into a column (and how to sum up the total expense per job?)

 

[bastanu]

Did you try theDBGuy's suggestion of creating a totals query (choose GroupBy for the JobRef field and Sum for NetValue, VAT and Total)?
Cheers,
Vlad