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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
Hamed NasrRetired IT ProfessionalCommented:
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
Hamed NasrRetired IT ProfessionalCommented:
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
ThomasianCommented:
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)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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.