Best way to display "profit" per job (1 Viewer)

Henryblah

New member
Local time
Today, 09:29
Joined
Nov 25, 2020
Messages
2
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

I’m here to help
Staff member
Local time
Today, 01:29
Joined
Oct 29, 2018
Messages
14,860
Hi. Welcome to AWF!

Have you tried using a Totals query?

Sent from phone...
 

bastanu

Active member
Local time
Today, 01:29
Joined
Apr 13, 2010
Messages
735
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,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:29
Joined
Feb 19, 2002
Messages
31,689
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.
 

Henryblah

New member
Local time
Today, 09:29
Joined
Nov 25, 2020
Messages
2
Tables.png

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

Active member
Local time
Today, 01:29
Joined
Apr 13, 2010
Messages
735
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:29
Joined
Feb 19, 2002
Messages
31,689
Use the report wizard if you don't know how to create a report yourself. There are several options on how to lay it out. Try each. You need to make TWO separate reports. If you want to limit the columns just select only the ones you want using the wizard. Once both are created, open the Job table report (which should be in single view) in design mode. Enlarge the detail section to make room for the subreport. Drag the expense report (which should be in DS or continuous view) into the open area. Set the master/child links. Access automatically sets these if you have created a relationship between the tables but if you haven't, then you need to set the link yourself. This is what Access uses to synchronize the two sets of data so that only the expenses for a particular job show for that job.
 

Users who are viewing this thread

Top Bottom