complex query help

kwah

New member
Local time
Today, 17:08
Joined
Mar 23, 2009
Messages
5
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...
 

Attachments

  • pdoibrob.PNG
    pdoibrob.PNG
    63.1 KB · Views: 93
I don't know if I fully understand the question, but here is my suggestion.

You can always create a field for the SubTotal (visible = false), assuming this is the detail line where "Date Added", "Staff Name", "Problem", "Total Cost", "Date Completed" are displayed.

On your subform, let's say footer, create a text box and enter =SUM([SubTotal]) into the Control Source.
 
This might be a clearer explanation of what I need to do...


Code:
Step 1) Get all customers..
 
Step 2) For each customer, get all jobs that are attached to that customer...
 
Step 3) For each job, get the charges for each transaction attached to that job
Step 3a) --> Return the sum of all transaction charges
 

Users who are viewing this thread

Back
Top Bottom