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.
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.