Filtering one side of a one-to-many relationship (1 Viewer)

[KitaYama]

I know both the title and the question is strange, But I hope I can find an answer to this.

This is a very simplified version of a query in a database.

When I run this query, the result contains three rows, because I have three records for this order in tblTasks.
My question is :
Is it possible to make this query show only one row without adding a filter from tblTasks. It doesn't matter which one of the three records of tblTask is shown.

It's obvious that adding DISTINCT to sql of this query causes error.

It's a part of a database that controls a manufacturing line. There's also a search form that is used to search orders.
If the search keywords that users submit to search form is only from tblOrders or tblParts they receive multiple rows for one order.


I appreciate any kind of advice.

 

[theDBguy]

I know you said it's a simplified query, but using a Totals query on it works with the following result. Perhaps you could try it with your more complex query.

 

[KitaYama]

I know you said it's a simplified query, but using a Totals query on it works with the following result. Perhaps you could try it with your more complex query.

View attachment 102836

Sorry, I'm not in front of a pc. Can you post the sql too...
thanks.

 

[theDBguy]

Sorry, I'm not in front of a pc. Can you post the sql too...
thanks.

Here you go.

SELECT tblParts.PartPK, tblParts.PartName, tblParts.DrawingNo, tblOrders.OrderPK, tblOrders.PartFK, tblOrders.Quantity, Min(tblTasks.TaskPK) AS TaskPK
FROM (tblParts INNER JOIN tblOrders ON tblParts.PartPK = tblOrders.PartFK) INNER JOIN tblTasks ON tblOrders.OrderPK = tblTasks.OrderFK
WHERE (((tblTasks.OrderFK)=1))
GROUP BY tblParts.PartPK, tblParts.PartName, tblParts.DrawingNo, tblOrders.OrderPK, tblOrders.PartFK, tblOrders.Quantity;

 

[plog]

It doesn't matter which one of the three records of tblTask is shown.

Is tblTask necessary in the query? If the value of TaskPK is irrelevant, why not just omit it and move your criteria to tblOrders?

 

[KitaYama]

Is tblTask necessary in the query? If the value of TaskPK is irrelevant, why not just omit it and move your criteria to tblOrders?

Yes it is. Because in some cases users need to search fields from tblTask too. For example when a user needs to search for a specific taskNo or a specific date for all tasks executed for a partID. The filter may change to something like
WHERE PartPK=1 AND TaskPK=15

OR

WHERE OrderFK=1234 AND PerformedOn>#2022/08/01#

 

[KitaYama]

Here you go.

SELECT tblParts.PartPK, tblParts.PartName, tblParts.DrawingNo, tblOrders.OrderPK, tblOrders.PartFK, tblOrders.Quantity, Min(tblTasks.TaskPK) AS TaskPK
FROM (tblParts INNER JOIN tblOrders ON tblParts.PartPK = tblOrders.PartFK) INNER JOIN tblTasks ON tblOrders.OrderPK = tblTasks.OrderFK
WHERE (((tblTasks.OrderFK)=1))
GROUP BY tblParts.PartPK, tblParts.PartName, tblParts.DrawingNo, tblOrders.OrderPK, tblOrders.PartFK, tblOrders.Quantity;

@theDBguy
Thanks. I'll give it a try as soon as I'm back to my desk.

 

[KitaYama]

@theDBguy

• Is there any limit in character count in a sql statement? All those 3 tables have a lot of fields.
• Would the count of records for a total query be of a concern in performance? tblTasks has more than a million record, the other two more than half a million.

 

[theDBguy]

@theDBguy

• Is there any limit in character count in a sql statement? All those 3 tables have a lot of fields.
• Would the count of records for a total query be of a concern in performance? tblTasks has more than a million record, the other two more than half a million.

You can have around 64,000 characters in a SQL statement. But you can only have a maximum of 255 columns in it.

If tblTask is properly indexed, the number of records shouldn't matter much in this Totals query, because we're simply pulling the matching Min record from it.

 

[MajP]

Number of enforced relationships	32 per table, minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships*
Number of tables in a query	32*
Number of joins in a query	16*
Number of fields in a recordset	255
Recordset size	1 gigabyte
Sort limit	255 characters in one or more fields
Number of levels of nested queries	50*
Number of characters in a cell in the query design grid	1,024
Number of characters for a parameter in a parameter query	255
Number of AND operators in a WHERE or HAVING clause	99*
Number of characters in an SQL statement	Approximately 64,000*

 

[MajP]

Well that is impossible and makes no sense. You cannot have one query that does both. In the first case you asked to return one record regardless of the amount of tasks and not using a filter.

Is it possible to make this query show only one row without adding a filter from tblTasks. It doesn't matter which one of the three records of tblTask is shown

In the second case. you want to filter by task.

Yes it is. Because in some cases users need to search fields from tblTask too. For example when a user needs to search for a specific taskNo or a specific date for all tasks executed for a partID. The filter may change to something like
WHERE PartPK=1 AND TaskPK=15

So you need to change the recordsource to two different queries. So if you are going to do that then get rid of tasks in the first query.

 

[KitaYama]

So you need to change the recordsource to two different queries

Search result form shows fields from tblTask too, because users may search for fields from tblTask too.
If we choose to use two queries, then in some cases some fields on search result show #NAME because the query doesn't contain those fields.
Then we have to hide the fields that show #NAME in result form.
Or we should have two result form.
That makes the case more complicated.

At present if the user doesn't include keywords from tblTask, we add a "AND TaskPK=1" to WHERE string.
We were looking forward finding a more appropriate way.

 

[mike60smart]

It would help if you included your Search Form and also state what you expect to search for and what is the expected result.

 

[KitaYama]

It would help if you included your Search Form and also state what you expect to search for and what is the expected result.

@mike60smart I'm away from my PC. I asked a friend to set up something simple that shows our case.
Open the database.
Type 1 in OrderPK text box.
Click Search button.
You receive 3 rows. I have only one record with OrderPK=1
I need to change the query to show only one row(Instead of 3 rows)



@theDBguy I passed your solution to the person in charge of the database and am waiting to hear back.
It's Saturday here and I may need to wait until Wednesday for their reply.
Thanks again.

 

[mike60smart]

Hi

The only way to achieve what you need is for your Search Results to have a Main Form and a Subform.

The Unbound Search Form will allow you to only Search on the following fields:-

OrderPK - This is an Autonumber Primary Key and should not be used for doing anything in the database
PartName
DrawingNo

This will then produce your Search Results Form as shown below:-

See the modified Db attached.

 

[KitaYama]

The only way to achieve what you need is for your Search Results to have a Main Form and a Subform.

Thanks for the advice. But that solution is not possible for us.
Again thanks for trying.

 

[mike60smart]

Thanks for the advice. But that solution is not possible for us.
Again thanks for trying.

Can you tell us Why that method is not possible for you?

Please note that if you have a query based on multiple tables, in your Case - Orders and Tasks.
If you look for OrderID from the Orders table and you have Multiple Tasks associated with the OrderID selected
it will always return Multiple Records.

 

[MajP]

Search result form shows fields from tblTask too, because users may search for fields from tblTask too.
If we choose to use two queries, then in some cases some fields on search result show #NAME because the query doesn't contain those fields.
Then we have to hide the fields that show #NAME in result form.
Or we should have two result form.
That makes the case more complicated.

NOT what I am saying at all. In your code you need to use two different sqls based on if task is selected. If task is not selected you need a group by query returning the min task as shown. If not you need one showing the selected task.

If srchTaskNo & "" = "" Then
   .RecordSource = "SELECT  * FROM qryDBguy WHERE " & fltr
else
        .RecordSource = "SELECT * FROM qry WHERE " & fltr
end if

 

[KitaYama]

Can you tell us Why that method is not possible for you?

For a lot of reasons.
for example In most cases we want to see who (UserPK) has been responsible for TaskNo=21 during a specific date.
A main form is always a single form with an embedded sub form. It means that it shows only one record of the orders table.
As I said it's a simplified version of tables. There are other information that need to be side by side for all searched keywords.

If you look for OrderID from the Orders table and you have Multiple Tasks associated with the OrderID selected
it will always return Multiple Records.

Yes I know. I asked this question in hope of finding a new way.

Thanks again for your help.

 

[KitaYama]

NOT what I am saying at all. In your code you need to use two different sqls based on if task is selected. If task is not selected you need a group by query returning the min task as shown. If not you need one showing the selected task.

If srchTaskNo & "" = "" Then
   .RecordSource = "SELECT  * FROM qryDBguy WHERE " & fltr
else
        .RecordSource = "SELECT * FROM qry WHERE " & fltr
end if

We will work on this too. I may be back to you if I hit a wall.
Thanks for the advice.