How do I get a single record query to return multiple records?

Hi guys, I hope I have the title of the question right, but here goes my explanation:

I have the tables set up in the attached image.  I can run the following parameter query:
SELECT tblJobResources.Job_ID,Sum([JobQtyUsedCost]) AS TotalMaterialCostForJob FROM (SELECT Sum(tblJobMaterials.Quantity) AS [JobQtyUsed],(tblMaterials.Cost/tblMaterials.Quantity_Received) AS [JobPerUnitCost],[JobQtyUsed]*[JobPerUnitCost] AS [JobQtyUsedCost]
FROM tblJobMaterials, tblMaterials
WHERE tblJobMaterials.Material_ID=tblMaterials.Material_ID
AND tblJobMaterials.Job_ID=[Enter job number]
GROUP BY tblMaterials.Cost/tblMaterials.Quantity_Received),tblJobResources
WHERE tblJobResources.Job_ID=[Enter job number]
GROUP BY tblJobResources.Job_ID;

And when I enter a Job ID when it says [Enter job number], it will add up the calculated cost from tblMaterials where the Job ID is what I enter.

When I enter 1, for example, I get this:
Job_ID      TotalMaterialCostForJob
1      350

which is perfect. Exactly what I want for one job.

Now, in tblJobMaterials there are many job_id's, because a job can have multiple materials.  In tblJobResources, the job_id is unique.

What I want to do is modify the above query so that it will return a record for every job_id in tblJobResources, but I just can't figure out how!  I want this data:
Job_ID      TotalMaterialCostForJob
1      350
2      100
3      50
5      600

Would anyone be kind enough to provide this SQL for me, or even tell me that my above query is not very good and rewrite that too?

Thanks alot,

Rob.
Relationships.PNG
LVL 65
RobSampsonAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
Here's an article about using JOINS

http://www.w3schools.com/sql/sql_join_inner.asp

I have attached an equivalent query below.


Your existing query (and capricorn's), first computes the total quantity for each material, the material's unit cost, multiplies the 2 values, then joins them to the jobresources table and gets the sum.

While my query joins the 3 tables, computes the cost for each record in jobmaterial (using the formula JM.Quantity * M.Cost/M.Quantity_Received) and gets the sum in a single column.
SELECT J.Job_ID, SUM(JM.Quantity * M.Cost/M.Quantity_Received)  AS TotalMaterialCostForJob
FROM tblJobResources J, tblJobMaterials JM, tblMaterials M
WHERE J.Job_ID = JM.Job_ID AND JM.Material_ID = M.Material_ID
GROUP BY J.Job_ID

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try this

SELECT tblJobResources.Job_ID,Sum([JobQtyUsedCost]) AS TotalMaterialCostForJob FROM
(SELECT Sum(tblJobMaterials.Quantity) AS [JobQtyUsed],(tblMaterials.Cost/tblMaterials.Quantity_Received) AS [JobPerUnitCost],[JobQtyUsed]*[JobPerUnitCost] AS [JobQtyUsedCost]
FROM tblJobMaterials, tblMaterials,tblJobResources
WHERE tblJobMaterials.Material_ID=tblMaterials.Material_ID
AND tblJobMaterials.Job_ID=tblJobResources.Job_ID
GROUP BY tblMaterials.Cost/tblMaterials.Quantity_Received)
GROUP BY tblJobResources.Job_ID;

0
 
RobSampsonAuthor Commented:
Thanks.  When I ran that, I am asked for the parameter value for tblJobResources.Job_ID.  When I enter, say 1, I get
Job_ID      TotalMaterialCostForJob
1      800

Where 800 is actually the total cost for all materials over all jobs.

Interestingly, if I run my first query as a subreport, linked to the Job_ID field of the simple main report query, it displays it in the correct groupings.  In VBA, I would just run through the simple "SELECT Job_ID FROM tblJobResources" recordset and substitute my parameter with each Job_ID. I just can't figure out how to do that with one query.

Rob.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
sorry, that was a guess,.. it is not easy to create query without the db

can you upload a copy of your db?

0
 
RobSampsonAuthor Commented:
Sure can! I figured I might need to do that.  It's only a subset of a larger project, but I've provided all that is required.

The query I'm trying to put this in is called qryCalculateTotalMaterialCostForAllJobs
You will find the query in my question called qryCalculateTotalMaterialCostForSpecificJob

There is also a query called qryCalculateTotalMaterialCostPerJobByMaterialName which will list the costs for each materials per job.  What I'm aiming at is removing the non-unique values from that query (Material_Name,Qty Used,Quantity_Received,Cost, and Per Unit Cost), and summing the Cost For Used Qty.

The ultimate goal is a very quick total material cost per job.

Hopefully this is even possible.....and I haven't designed my database wrong.....

I thought about putting a field called Total_Material_Cost in tblJobResources, and using VBA to calculate the value and INSERT it in, when the Save button on my form is clicked, but I didn't really want to "store" a calculated value.

Thanks again,

Rob.
Farm-For-EE.accdb
0
 
RobSampsonAuthor Commented:
It may be that this is not possible.  As this is only for reporting purposes, maybe I should use a dlookup text box against the qryCalculateTotalMaterialCostForSpecificJob query....

Rob.
0
 
hnasrCommented:
Is this near what you want?
SELECT tblJobMaterials.Job_ID, First(tblMaterials.Cost) AS FirstOfCost
FROM tblMaterials INNER JOIN tblJobMaterials ON tblMaterials.Material_ID = tblJobMaterials.Material_ID
GROUP BY tblJobMaterials.Job_ID
ORDER BY tblJobMaterials.Job_ID;
0
 
hnasrCommented:
The output of previous query is:
Job_ID      FirstOfCost
1      ¿ 300.000
2      500.000
9      .¿ 500.000
11      1,000.000
If different from the expected output ammend the output format!
0
 
ThomasianConnect With a Mentor Commented:
Here you go
SELECT J.Job_ID, SUM(JM.Quantity * M.Cost/M.Quantity_Received)  AS TotalMaterialCostForJob
FROM (tblJobResources J 
      INNER JOIN tblJobMaterials JM ON J.Job_ID = JM.Job_ID) 
      INNER JOIN tblMaterials M ON JM.Material_ID = M.Material_ID
GROUP BY J.Job_ID

Open in new window

0
 
RobSampsonAuthor Commented:
hnasr, no those values are not correct.

Here is the output of qryCalculateTotalMaterialCostPerJobByMaterialName
Job_ID      Material_Name      Qty Used      Quantity_Received      Cost      Per Unit Cost      Cost For Used Qty
1      Fertiliser      1000      20000      $1,000.00      0.05      50
1      Peas      1000      1000      $300.00      0.3      300
2      Broccoli      3000      5000      $500.00      0.1      300
9      Broccoli      500      5000      $500.00      0.1      50
11      Fertiliser      2000      20000      $1,000.00      0.05      100

So what I want shortened to is
Job_ID      TotalMaterialCost
1      350
2      300
9      50
11      100

As you can, currently only job 1 has multiple materials, but that's only test data.

Rob.
0
 
RobSampsonAuthor Commented:
Thomasian, that appears to work!  It looks far too short a query! LOL!  Let me digest how that's working for a minute.......

Rob.
0
 
RobSampsonAuthor Commented:
I'm not familiar enough with INNER JOIN's is this possible to create with the WHERE <table>.<field> = <table>.<field> notation, or do I need a crash course on INNER JOINs? (yikes).

Rob.
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
oh sorry, got busy anyway.. try this too


SELECT JR.Job_ID , Sum(X.[JobQtyUsedCost]) AS TotalMaterialCostForJob
FROM tblJobResources AS JR INNER JOIN (SELECT Sum(tblJobMaterials.Quantity) AS JobQtyUsed, (tblMaterials.Cost/tblMaterials.Quantity_Received) AS JobPerUnitCost, [JobQtyUsed]*[JobPerUnitCost] AS JobQtyUsedCost, (tblMaterials.Cost/tblMaterials.Quantity_Received) As CostPerQty, tblJobMaterials.Job_ID
FROM tblJobMaterials INNER JOIN tblMaterials ON tblJobMaterials.Material_ID = tblMaterials.Material_ID
GROUP BY tblMaterials.Cost/tblMaterials.Quantity_Received, tblJobMaterials.Job_ID)  AS X ON JR.Job_ID = X.Job_ID
Group By JR.Job_ID
0
 
RobSampsonAuthor Commented:
capricorn1, that works great too.....and shows the difference in possible complexity!  Thomasian, can you elaborate on why yours is so much shorter?

Rob.
0
 
RobSampsonAuthor Commented:
PS. I see you've both used aliases for the tables, which I tried, but obviously got wrong, but it seems to be key to getting nested SELECT statements to work together....
0
 
ThomasianCommented:
>>PS. I see you've both used aliases for the tables, which I tried, but obviously got wrong, but it seems to be key to getting nested SELECT
>>statements to work together....

When using a subquery (nested select) in the FROM clause, it is necessary to assign an alias name to be able reference its columns. But in my query, I didn't use any subqueries so an alias is not required. I only assigned them to make the query shorter and easier to read.

i.e.
Without aliases, my query will be:

SELECT tblJobResources.Job_ID, SUM(tblJobMaterials.Quantity....
0
 
RobSampsonAuthor Commented:
Oh I see now!  So I was thinking about backwards....kinda.....I guess I need to trust that the relationships will do their job.

I have also shortened my original "specific job" query to this:

SELECT J.Job_ID, SUM(JM.Quantity * M.Cost/M.Quantity_Received)  AS TotalMaterialCostForJob
FROM tblJobResources J, tblJobMaterials JM, tblMaterials M
WHERE J.Job_ID = JM.Job_ID AND JM.Material_ID = M.Material_ID AND J.Job_ID=[Enter Job ID]
GROUP BY J.Job_ID;

which is done just by adding the parameter "AND J.Job_ID=[Enter Job ID]" to it.  That's just great!  I should be able to go from here for a while. Thanks very much!

Rob.
0
 
RobSampsonAuthor Commented:
Fantastic, thanks Thomasian and Capricorn1!

Rob.
0
 
RobSampsonAuthor Commented:
Thanks alot! I love it when the answer is much more simple than I anticipated! On top of that, it looks like my relationships are OK! Cheers guys!
0
 
ThomasianCommented:
>>I guess I need to trust that the relationships will do their job.
Yes, that's what relationships are for. ;)

>>I have also shortened my original "specific job" query to this:
I was just going to suggest the same thing.


Btw, I suggest that you check out the link I posted. Using the JOIN syntax has a few advantages. For one, it is easier to read since you will be able to easily tell how each tables are related to each other and which conditions are added to filter the results.
0
 
RobSampsonAuthor Commented:
Sure, will do....thanks again!
0
 
Rey Obrero (Capricorn1)Commented:
sorry, didn't have the time to scrutinize the original query.. just made it work accordingly.

anyway, Thomasian did a great job!!! cheers
0
 
RobSampsonAuthor Commented:
No problem capricorn....I would have taken it to work anyway I could....it's embarassing how long I spent on it.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.