Unmatched Qry (1 Viewer)

[Gismo]

Hi,

I have a table with standard issue items
when I run a unmatched qry i want to see all my items from this table and only items that has these items ordered show as quantity ordered with the registration number from the other table

if the item has not been ordered by registration number, the qty field should be blank

Joined property options does not seem to give me all the items from Table 1

 

[theDBguy]

Hi. That doesn't look like a normal unmatched query. Have you tried using the Wizard?

 

[Gismo]

i tried the wizard as well, still the same result
i need to see all the records from the first table. and the qty of the second tble if ordered
it does not give me the blanks on the 2nd table
i don't have any criteria except for the type of data i need in the first table and the registration i am comparing too in the second tbl

 

[theDBguy]

Hi. Since you're filtering the left table, that means the query won't return all the records from that table.

 

[Gismo]

Hi. Since you're filtering the left table, that means the query won't return all the records from that table.

by filtering the left table with "new aircraft assembly", i must have 6 records in the left table

 

[theDBguy]

by filtering the left table with "new aircraft assembly", i must have 6 records in the left table

To verify that, try removing the right table from your query.

 

[Gismo]

To verify that, try removing the right table from your query.

 

[theDBguy]

View attachment 85328


View attachment 85329

Just as an experiment, try saving that as a new query and then create an unmatched query from that query by joining the new query to the other table.

 

[Gismo]

Just as an experiment, try saving that as a new query and then create an unmatched query from that query by joining the new query to the other table.

Tried that as well

 

[theDBguy]

Tried that as well

View attachment 85330


View attachment 85331

Next step then is for you to share your data, so we can help you troubleshoot it further. Can you do that?

 

[theDBguy]

Next step then is for you to share your data, so we can help you troubleshoot it further. Can you do that?

Wait a minute... I just noticed in your last post, you seem to be filtering the Right table, instead of the Left.

 

[Gismo]

Wait a minute... I just noticed in your last post, you seem to be filtering the Right table, instead of the Left.

on the left, i am filtering to only show items that is standard issue to new aircraft assembly
on the right, i am filtering the aircraft registration as i only need to see for the specific registration

 

[arnelgp]

abandon the Join query and instead use Dcount() to Count the items in another table:

select MaterialNo, DCount("1", "[SQ01 Tbl]", "MaterialNo = '" & [MaterialNo] & "'") As OrderedQty From MaterialNo;

 

[Gismo]

abandon the Join query and instead use Dcount() to Count the items in another table:

select MaterialNo, DCount("1", "[SQ01 Tbl]", "MaterialNo = '" & [MaterialNo] & "'") As OrderedQty From MaterialNo;

not sure this will work

i need to see all the items in the left table and the qty ordered in the right or blank if not ordered, all this on the right filtered by the registration number

 

[theDBguy]

on the left, i am filtering to only show items that is standard issue to new aircraft assembly
on the right, i am filtering the aircraft registration as i only need to see for the specific registration

So, filtering the left table, you get back 6 rows. Then, filtering the right table, that 6 rows gets reduced down to 2 rows. That's got to be correct then. Otherwise, you may be joining ir filtering on the wrong fields.

 

[arnelgp]

i am sure it will work.
for those without orders, it will come as zero (0).
but you can hide the zero qty by using the Right Format on that Column.

 

[Gismo]

So, filtering the left table, you get back 6 rows. Then, filtering the right table, that 6 rows gets reduced down to 2 rows. That's got to be correct then. Otherwise, you may be joining ir filtering on the wrong fields.

the end result is correct, the 2 lines, but i would like to still see the 6 lines, even though the right may not have any orders so the field qty should be blank

 

[Gismo]

i am sure it will work.
for those without orders, it will come as zero (0).
but you can hide the zero qty by using the Right Format on that Column.

tnx, let my try again

 

[Gismo]

all

tnx, let my try again

have a qty of zero and some materialNo fields are blank

 

[arnelgp]

see Query1 of what i am talking about.