Dlookup in report / or query

[Crowbar]

Hello Everybody!
I can usually gain any knowledge I need from just reading and searching the forums, but this time I can't find the answer I am looking for. I need help with a not too-complicated Dlookup.

The basics,
Working with a functioning database, written by someone else, so I can't change the table structures.
Goal is to make my own reports, and I have made a few of them already, and they work fine.

My issue:
I have a query to supply data for one of these reports, so far it works.
I made the query, and it has 3 tables,
[Orders], [OrdersTimesHistory], and [OrdersParts]
all needed to make the report. This gives me the desired results.

But I want to add a field to my report from a different but related table. This table [OrdersPartsProcess] contains some step numbers [SEQ], which would make my report much more useful.

When I add this table to my query I get many more results than I need, probably because of the way the relationships have been made. I can't change the relationships or that would probably screw up things real good, right? I tried deleting the relationships in the query itself, but still too many results.

So, I thought I was smart and would do a dlookup from either a calculated field in the query, or a dlookup on the report itself (same results either way).
I thought I did it correctly (code the dlookup that is), but I seem to get the same value (1) for every record.

My latest version of the dlookup is:

DLookUp("[Seq]","[OrdersPartsProcess]","[OrderTimesHistory]![OrderID]=" & "[OrdersPartsProcess]![WOID]" AND "[OrdersTimesHistory]![ProcID]='" & "[OrdersPartsProcess]![ProcID]")

And to further complicate things [OrdersTimesHistory]![ProcID] is text as opposed to [OrdersPartsProcess]![ProcID] which is numeric. Why the designer did this is beyond my comprehension, but I have to assume he had a good reason.

I need to get the [SEQ] on the report. I seem to be losing what is left of my hair !!!:banghead:

 

[plog]

You really need to do this in a sub-query. That will allow you to both fix the incorrect data type and obtain the correct value you want.

Most likely you will need to make it an aggregate query on OrdersPartsProcess, so that you can group multiple records together. Be sure to bring in all fields you need--Seq, WOID and ProcID, convert. Make that display the correct data, then link it in your query that is the datasource of your report.

 

[Crowbar]

Thank you, I will give it a shot right now.

 

[Crowbar]

Working on this all morning, and I think you are correct about the subquery, but I am having difficulties with where I insert the subquery. My SQL of the original is a bit complicated, and my first couple attempts generated an error.

SELECT Orders.OrderNum, Orders.CustomerName, Orders.Ref, Orders.OrderDate, Orders.ReqBy, Orders.ShipDate, Orders.PONum, OrdersParts.PartNum, OrdersParts.Desc, OrdersParts.DRWNum, OrdersParts.Qty, OrderTimesHistory.EmpNum, OrderTimesHistory.EmpName, OrderTimesHistory.Process, OrderTimesHistory.StartTime, TimeValue([StartTime]) AS realStartTime, OrderTimesHistory.EndTime, OrderTimesHistory.DateOUT, OrderTimesHistory.Horas, OrderTimesHistory.QtyCompleted, OrderTimesHistory.QtyRejected, OrderTimesHistory.ProcID, OrderTimesHistory.OrdPartID
FROM (Orders INNER JOIN OrdersParts ON Orders.WOid = OrdersParts.OrderID) INNER JOIN OrderTimesHistory ON OrdersParts.OPartID = OrderTimesHistory.OrdPartID
WHERE (((Orders.OrderNum)=[Forms]![frmWOProgress]![cboWoChoose]));

I was trying to put it in with the WHERE clause, but I am just guessing.....

 

[plog]

You put your sub-query in a JOIN clause.

 

[Crowbar]

I'm not getting any love, just syntax errors when I paste in my subquery code. Any suggestions on where to read more about subqueries?

 

[plog]

I wouldn't paste in your sub-query. Save it, then bring it in using Design view.

 

[Crowbar]

Now I'm extra-confused, I designed my sub-query in design mode, switched to SQL mode and copied the text. Then I went to my main query and switched to SQL mode again, and tried to insert it there into the Join clause. How would I insert the subquery into the main query in design mode?

 

[plog]

Let's call the existing query the report is built on "YourQuery". So, you build your sub-query in Design mode, then save it with the name "YourQuery_sub1". Now you go into design view of YourQuery, click the 'Show Table' icon in the ribbon, click the 'Queries' tab and add YourQuery_sub1 to it. Link it appropriately to the existing data sources in YourQuery and now its data is available in YourQuery.

 

[Crowbar]

Excellent! Thank you for that, I was over-complicating as usual.
I am much closer now, but still not there yet. Keep getting blanks for the SEQ, but I will keep plodding along.

 

[Crowbar]

Hi Plog,
You have pushed me in the right direction, but I am still having issues.
As you can see in the WHERE clause, the query accepts input from a form, which narrows the results down.
Before I add the subquery I type in my test number when prompted, and I get 4 results in the query (YourQuery), now when I add the subquery (YourQuery_sub1) I get 92 results.
I think I understand that the JOIN statement is causing this, but I can't seem to tweak the JOIN to get me back to the 4 results.
Any suggestions?

 

[plog]

It's probably your sub-query not producing unique results for the data you need. Can you post the SQL of both queries?

 

[Crowbar]

Here is the SQL for the smaller subquery
It seems to work correctly. It shows all the records in the OrdersPartsProcess table, and each one only once. When I filter the results for my test number, I get the correct amount rows which is 23.

SELECT OrdersPartsProcess.Seq, OrdersPartsProcess.ProcID, OrdersPartsProcess.WoID
FROM OrdersPartsProcess
GROUP BY OrdersPartsProcess.Seq, OrdersPartsProcess.ProcID, OrdersPartsProcess.WoID;

Here is the SQL for the main query
originally I got 3096 rows, until I created a join (in the design window) which narrowed it down to the 92 rows I see now.

SELECT Orders.OrderNum, Orders.CustomerName, Orders.Ref, Orders.OrderDate, Orders.ReqBy, Orders.ShipDate, Orders.PONum, OrdersParts.PartNum, OrdersParts.Desc, OrdersParts.DRWNum, OrdersParts.Qty, OrderTimesHistory.EmpNum, OrderTimesHistory.EmpName, OrderTimesHistory.Process, OrderTimesHistory.StartTime, TimeValue([StartTime]) AS realStartTime, OrderTimesHistory.EndTime, OrderTimesHistory.DateOUT, OrderTimesHistory.Horas, OrderTimesHistory.QtyCompleted, OrderTimesHistory.QtyRejected, OrderTimesHistory.ProcID, OrderTimesHistory.OrdPartID, CInt([OrderTimesHistory]![ProcID]) AS ProcIDNum, qrySubProgress.Seq
FROM qrySubProgress INNER JOIN ((Orders INNER JOIN OrdersParts ON Orders.WOid = OrdersParts.OrderID) INNER JOIN OrderTimesHistory ON OrdersParts.OPartID = OrderTimesHistory.OrdPartID) ON qrySubProgress.WoID = OrderTimesHistory.OrderID
WHERE (((Orders.OrderNum)=[Forms]![frmWOProgress]![cboWoChoose]));

Thank you for looking at this, as I am still pretty confused.

 

[plog]

Let's simplify what's happening with these 2 tables:

T1
Alpha, Num
A, 8
A, 11
B, 3
B, 6
C, 1
E, 10

T2
Alpha, Num
A, 14
A, 11
B, 6
C, 9
D, 8

If we bring those in a query and INNER JOIN them on Alpha. We are going to get 7 results:

4 A's (2 in T1 * 2 in T2)
2 B's (2 in T1 * 1 in T2)
1 C's (1 in T1 * 1 in T2)
0 D's (0 in T1 * 1 in T2)
0 E's (1 in T1 * 0 in T2)

That's what happened in your query. On one side of a join you had multiple records for the value you were linking and it brought in every match. That subquery, made the datasource with the multiple records unique for the values you were using and made it contain only 1 value per permutation.

Tune in next time to learn about LEFT JOINS where we make that E record appear, FULL OUTER JOINS where we make both the E and D records appear and UNION queries where we just merge the 2 datasets together.

 

[Crowbar]

I am absorbing what you said so far reading about it furiously, and of course staying tuned !

 

[Crowbar]

It seems to me that this would be a much easier thing to do if I did not have multiple tables involved. Still banging my head against the wall.

 

[plog]

Can you post your database and then what you expect your query to produce based on the data in your tables?

 

[Crowbar]

I think I can, first have to ask the boss if he cares that I post it. Probably on Monday