Hi,
I'm struggling to figure out this one but I hope there's some help here =]
I'm creating an order system thing for a computer repair shopand basically I've got a table of 'transactions' (where money is paid for stuff) and a table of 'jobs' (a record of computers that need to be fixed). Each job can have multiple transactions (more than one replacement part for example).
What I want to do is create a subform that shows the 'job history' of an individual customer. Because it will be in a subform, the records will be limited to the currently selected customer automatically.
I want the headings "Date Added", "Staff Name", "Problem", "Total Cost", "Date Completed".
I am having trouble with the getting the 'total cost' for the job. As far as I can see, I need to filter for only jobs that have the same CustID as the selected record in the main form.
For each of these jobs I need to then grab the 'SubTotal' of each transaction (linked by JobID), sum them all and display that.
I wouldn't find it so troublesome and I would be able to do it if it was via PHP / MySQL or anything but it is quite urgent (deadline is soon) and I don't see how to do the multi-layered processing bit...
I get the feeling that it will need more than one query to do this but all help is appreciated...
Thanks...
PS, I attached the relationship diagram of the database.. It shows the setup of the database in a little more detail...
I'm struggling to figure out this one but I hope there's some help here =]
I'm creating an order system thing for a computer repair shopand basically I've got a table of 'transactions' (where money is paid for stuff) and a table of 'jobs' (a record of computers that need to be fixed). Each job can have multiple transactions (more than one replacement part for example).
What I want to do is create a subform that shows the 'job history' of an individual customer. Because it will be in a subform, the records will be limited to the currently selected customer automatically.
I want the headings "Date Added", "Staff Name", "Problem", "Total Cost", "Date Completed".
I am having trouble with the getting the 'total cost' for the job. As far as I can see, I need to filter for only jobs that have the same CustID as the selected record in the main form.
For each of these jobs I need to then grab the 'SubTotal' of each transaction (linked by JobID), sum them all and display that.
I wouldn't find it so troublesome and I would be able to do it if it was via PHP / MySQL or anything but it is quite urgent (deadline is soon) and I don't see how to do the multi-layered processing bit...
I get the feeling that it will need more than one query to do this but all help is appreciated...
Thanks...
PS, I attached the relationship diagram of the database.. It shows the setup of the database in a little more detail...