unmatched query without PK (1 Viewer)

luzz

Registered User.
Local time
Today, 06:28
Joined
Aug 23, 2017
Messages
346
Hello all, is there other ways that i can compare data between two tables so that my subform will be able to show only data that are not added into the main table? I think i am not able to use the unmatched query in access as i do not have a primary key in both of my data.
Below is my data:
The first data is the image for the import data
The second data is the image for my main table in access
 

Attachments

  • Import.png
    Import.png
    8.6 KB · Views: 67
  • MainTable.png
    MainTable.png
    17.1 KB · Views: 63

Gasman

Enthusiastic Amateur
Local time
Today, 13:28
Joined
Sep 21, 2011
Messages
14,052
I don't think you have to match on keys, but you have to match on something, otherwise how can you find unmatched.?

Even if you concatenate fields to get something to match. From your pics I cannot see anything that is common to both tables?
 

luzz

Registered User.
Local time
Today, 06:28
Joined
Aug 23, 2017
Messages
346
I don't think you have to match on keys, but you have to match on something, otherwise how can you find unmatched.?

Even if you concatenate fields to get something to match. From your pics I cannot see anything that is common to both tables?

Oh, sorry. I think this images are better.

In import, GL lot is the same as GLA in the main table
 

Attachments

  • Import.png
    Import.png
    12.8 KB · Views: 101
  • MainTable.png
    MainTable.png
    15.1 KB · Views: 115

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
Then use an unmatched query linking the 2 tables by those fields using a left join and set GLA criteria to be Is Null.
You can use the wizard to make it really easy.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:28
Joined
Jan 23, 2006
Messages
15,364
I agree with the suggestions so far. My concern is with the StyleNo field and values in the Import file.
It appears to have multivalues and could be a problem --depending on what it is and how you use it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:28
Joined
Sep 21, 2011
Messages
14,052
Perhaps so, but they appear to be repeated for different fabrications/colours?

Oh, sorry. I think this images are better.

In import, GL lot is the same as GLA in the main table
 

luzz

Registered User.
Local time
Today, 06:28
Joined
Aug 23, 2017
Messages
346
Perhaps so, but they appear to be repeated for different fabrications/colours?

Yes, because 1 GLA number can consist of repeated for different fabrications/colours?
 

luzz

Registered User.
Local time
Today, 06:28
Joined
Aug 23, 2017
Messages
346
Then use an unmatched query linking the 2 tables by those fields using a left join and set GLA criteria to be Is Null.
You can use the wizard to make it really easy.

SELECT FabricPO.[Style NO], FabricPO.[GL Lot], FabricPO.Date, FabricPO.Description2, FabricPO.[Fabric Cuttable Width], FabricPO.GSMBeforeWash, FabricPO.[GMS Per SqYD], FabricPO.Color, FabricPO.[Our Qty], FabricPO.[Supplier Qty]
FROM FabricPO LEFT JOIN MainTable ON FabricPO.[GL Lot] = MainTable.[GLA]
WHERE (((MainTable.GLA) Is Null));

Hello, I tried using your method and use this as a subform on my main form to display data that are not matched and it works. However, when i add a data into the main table, the same record still display on the subform. How can i not show the data that have been added into the main table on the sub form? Thankyou
 

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
Requery the subform after adding the new record
 

luzz

Registered User.
Local time
Today, 06:28
Joined
Aug 23, 2017
Messages
346
Requery the subform after adding the new record

'Refresh data in list on form to show only data that are not save
Unmatchedsubform.Form.Requery
I put this requery statement under my "Save" button
 

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
I hope you mean you added that to the code for your Save button
It reads as though you've physically entered that text on your form underneath the Save button. Please tell me that's not what you meant!!

BTW why do you need a Save button.
Are you using an unbound form?
 

luzz

Registered User.
Local time
Today, 06:28
Joined
Aug 23, 2017
Messages
346
I hope you mean you added that to the code for your Save button
It reads as though you've physically entered that text on your form underneath the Save button. Please tell me that's not what you meant!!

BTW why do you need a Save button.
Are you using an unbound form?

I mean this! [I hope you mean you added that to the code for your Save button]
yes, i am using an unbound form
 

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
I'm sure you are aware how much more difficult you are making everything by using an unbound form. There is almost NEVER a good reason for doing so.

Anyway, so you've added the code but you haven't said whether it works.
If you've done it correctly, it would work in a BOUND form
If it doesn't work for you, you'll need to write specific code to fix the issue for your UNBOUND form

I'm signing off now.
 

luzz

Registered User.
Local time
Today, 06:28
Joined
Aug 23, 2017
Messages
346
I'm sure you are aware how much more difficult you are making everything by using an unbound form. There is almost NEVER a good reason for doing so.

Anyway, so you've added the code but you haven't said whether it works.
If you've done it correctly, it would work in a BOUND form
If it doesn't work for you, you'll need to write specific code to fix the issue for your UNBOUND form

I'm signing off now.

the code works for finding out unmatching records, but it does not work when i save a record in the main table, the record is still in the subform
 

Users who are viewing this thread

Top Bottom