Join Query - Subtract Two Sets 2 Field Key

[BlueWolvering]

Hello,

I am using Access 2003 with no ability to switch versions.

I have two tables. 1 is the current inventory of Fuel Cards, and the second
is the new inventory of Fuel Cards. I want to find all the NEW Fuel Cards
that exist only in the second table.

SO I have list A and List B. I want to find all the entries in B that are
not in A.

A has a two field Primary key. (Fuel Card # and FuelCard Provider)
B does not have a key but for all intents and purposes, it has the same key
( though it is not defined within access).

Here is the SQL code the wizard gave me.

SELECT ex_FuelCardInput.[Fuel Card Source], ex_FuelCardInput.[Fuel Card
Provider], ex_FuelCardInput.[Fuel Card Number], ex_FuelCardInput.VIN
FROM ex_FuelCardInput LEFT JOIN t_FuelCardInventory ON
ex_FuelCardInput.[Fuel Card Number] = t_FuelCardInventory.FuelCardNo
WHERE (((t_FuelCardInventory.FuelCardNo) Is Null));

The problem is, and this is evident from the design mode, is that this only
is keying on the FuelCardNo. It does not look at the Fuel Card Provider. So
I repeated a FuelCardNo with a different Provider and it returned the Card
(bravo) but showed the repeated number as Null.

FCNo FCPRov FCBlahblah
1 Bob jalskhfa
2 Tom akgjgh
(1) Tom agfhsfgh


the (1) did not display. that cell was blank. But the record was included
in the list.

I tried adding another column with t_FuelCardInventory.FCProvider = Is Null
and that didn't work (DID NOT CHANGE ANYTHING)

I tried making the criteria on FCNo = is null AND
IsNull(t_Fuelcardinventory.fcprovider) and that made the query return ZERO
results.

Thanks!

 

[KARL DEWEY]

You identify the table in 3 ways -
ex_FuelCardInput --
[Fuel Card Source]
[Fuel Card Provider]
[Fuel Card Number]
VIN
All you gave for t_FuelCardInventory was FuelCardNo. What are the other
fields?

Too many peices missing.

--
KARL DEWEY
Build a little - Test a little

Hello,

I am using Access 2003 with no ability to switch versions.

I have two tables. 1 is the current inventory of Fuel Cards, and the second
is the new inventory of Fuel Cards. I want to find all the NEW Fuel Cards
that exist only in the second table.

SO I have list A and List B. I want to find all the entries in B that are
not in A.

A has a two field Primary key. (Fuel Card # and FuelCard Provider)
B does not have a key but for all intents and purposes, it has the same key
( though it is not defined within access).

Here is the SQL code the wizard gave me.

SELECT ex_FuelCardInput.[Fuel Card Source], ex_FuelCardInput.[Fuel Card
Provider], ex_FuelCardInput.[Fuel Card Number], ex_FuelCardInput.VIN
FROM ex_FuelCardInput LEFT JOIN t_FuelCardInventory ON
ex_FuelCardInput.[Fuel Card Number] = t_FuelCardInventory.FuelCardNo
WHERE (((t_FuelCardInventory.FuelCardNo) Is Null));

The problem is, and this is evident from the design mode, is that this only
is keying on the FuelCardNo. It does not look at the Fuel Card Provider. So
I repeated a FuelCardNo with a different Provider and it returned the Card
(bravo) but showed the repeated number as Null.

FCNo FCPRov FCBlahblah
1 Bob jalskhfa
2 Tom akgjgh
(1) Tom agfhsfgh


the (1) did not display. that cell was blank. But the record was included
in the list.

I tried adding another column with t_FuelCardInventory.FCProvider = Is Null
and that didn't work (DID NOT CHANGE ANYTHING)

I tried making the criteria on FCNo = is null AND
IsNull(t_Fuelcardinventory.fcprovider) and that made the query return ZERO
results.

Thanks!

 

[BlueWolvering]

ex_FuelCardInput has the new cards
4 fields - No key
FCNo
FCProvider
FCSource
VIN

t_FuelCardInventory has many fields, which I can deal with later
KEY:
FCNo and FCProvider

If I can get to the list of FCNo PCProvider COmbinations in table "ex" but
not on "t" I can pull the other columns my self, that seems straightforward.

I hope that helps.

You identify the table in 3 ways -
ex_FuelCardInput --
[Fuel Card Source]
[Fuel Card Provider]
[Fuel Card Number]
VIN
All you gave for t_FuelCardInventory was FuelCardNo. What are the other
fields?

Too many peices missing.

--
KARL DEWEY
Build a little - Test a little

Hello,

I am using Access 2003 with no ability to switch versions.

I have two tables. 1 is the current inventory of Fuel Cards, and the second
is the new inventory of Fuel Cards. I want to find all the NEW Fuel Cards
that exist only in the second table.

SO I have list A and List B. I want to find all the entries in B that are
not in A.

A has a two field Primary key. (Fuel Card # and FuelCard Provider)
B does not have a key but for all intents and purposes, it has the same key
( though it is not defined within access).

Here is the SQL code the wizard gave me.

SELECT ex_FuelCardInput.[Fuel Card Source], ex_FuelCardInput.[Fuel Card
Provider], ex_FuelCardInput.[Fuel Card Number], ex_FuelCardInput.VIN
FROM ex_FuelCardInput LEFT JOIN t_FuelCardInventory ON
ex_FuelCardInput.[Fuel Card Number] = t_FuelCardInventory.FuelCardNo
WHERE (((t_FuelCardInventory.FuelCardNo) Is Null));

The problem is, and this is evident from the design mode, is that this only
is keying on the FuelCardNo. It does not look at the Fuel Card Provider. So
I repeated a FuelCardNo with a different Provider and it returned the Card
(bravo) but showed the repeated number as Null.

FCNo FCPRov FCBlahblah
1 Bob jalskhfa
2 Tom akgjgh
(1) Tom agfhsfgh


the (1) did not display. that cell was blank. But the record was included
in the list.

I tried adding another column with t_FuelCardInventory.FCProvider = Is Null
and that didn't work (DID NOT CHANGE ANYTHING)

I tried making the criteria on FCNo = is null AND
IsNull(t_Fuelcardinventory.fcprovider) and that made the query return ZERO
results.

Thanks!

 

[KARL DEWEY]

Try this ---
SELECT t_FuelCardInventory.FCNo, t_FuelCardInventory.FCProvider
FROM ex_FuelCardInput RIGHT JOIN t_FuelCardInventory ON
(ex_FuelCardInput.FCProvider = t_FuelCardInventory.FCProvider) AND
(ex_FuelCardInput.FCNo = t_FuelCardInventory.FCNo)
WHERE (((ex_FuelCardInput.FCNo) Is Null) AND ((ex_FuelCardInput.FCProvider)
Is Null));

--
KARL DEWEY
Build a little - Test a little

ex_FuelCardInput has the new cards
4 fields - No key
FCNo
FCProvider
FCSource
VIN

t_FuelCardInventory has many fields, which I can deal with later
KEY:
FCNo and FCProvider

If I can get to the list of FCNo PCProvider COmbinations in table "ex" but
not on "t" I can pull the other columns my self, that seems straightforward.

I hope that helps.

You identify the table in 3 ways -

I have two tables. 1 is the current inventory of Fuel Cards, and the second

is the new inventory of Fuel Cards.

SO I have list A and List B.
ex_FuelCardInput AND t_FuelCardInventory

ex_FuelCardInput --
[Fuel Card Source]
[Fuel Card Provider]
[Fuel Card Number]
VIN

B does not have a key but for all intents and purposes, it has the same key (though it is not defined within access).

All you gave for t_FuelCardInventory was FuelCardNo. What are the other
fields?

Too many peices missing.

--
KARL DEWEY
Build a little - Test a little

Hello,

I am using Access 2003 with no ability to switch versions.

I have two tables. 1 is the current inventory of Fuel Cards, and the second
is the new inventory of Fuel Cards. I want to find all the NEW Fuel Cards
that exist only in the second table.

SO I have list A and List B. I want to find all the entries in B that are
not in A.

A has a two field Primary key. (Fuel Card # and FuelCard Provider)
B does not have a key but for all intents and purposes, it has the same key
( though it is not defined within access).

Here is the SQL code the wizard gave me.

SELECT ex_FuelCardInput.[Fuel Card Source], ex_FuelCardInput.[Fuel Card
Provider], ex_FuelCardInput.[Fuel Card Number], ex_FuelCardInput.VIN
FROM ex_FuelCardInput LEFT JOIN t_FuelCardInventory ON
ex_FuelCardInput.[Fuel Card Number] = t_FuelCardInventory.FuelCardNo
WHERE (((t_FuelCardInventory.FuelCardNo) Is Null));

The problem is, and this is evident from the design mode, is that this only
is keying on the FuelCardNo. It does not look at the Fuel Card Provider. So
I repeated a FuelCardNo with a different Provider and it returned the Card
(bravo) but showed the repeated number as Null.

FCNo FCPRov FCBlahblah
1 Bob jalskhfa
2 Tom akgjgh
(1) Tom agfhsfgh


the (1) did not display. that cell was blank. But the record was included
in the list.

I tried adding another column with t_FuelCardInventory.FCProvider = Is Null
and that didn't work (DID NOT CHANGE ANYTHING)

I tried making the criteria on FCNo = is null AND
IsNull(t_Fuelcardinventory.fcprovider) and that made the query return ZERO
results.

Thanks!

 

[BlueWolvering]

This query is flipped, it returned everything in t not in ex, but it was
simple enough to fix in design view. Thank you for your help

Try this ---
SELECT t_FuelCardInventory.FCNo, t_FuelCardInventory.FCProvider
FROM ex_FuelCardInput RIGHT JOIN t_FuelCardInventory ON
(ex_FuelCardInput.FCProvider = t_FuelCardInventory.FCProvider) AND
(ex_FuelCardInput.FCNo = t_FuelCardInventory.FCNo)
WHERE (((ex_FuelCardInput.FCNo) Is Null) AND ((ex_FuelCardInput.FCProvider)
Is Null));

--
KARL DEWEY
Build a little - Test a little

ex_FuelCardInput has the new cards
4 fields - No key
FCNo
FCProvider
FCSource
VIN

t_FuelCardInventory has many fields, which I can deal with later
KEY:
FCNo and FCProvider

If I can get to the list of FCNo PCProvider COmbinations in table "ex" but
not on "t" I can pull the other columns my self, that seems straightforward.

I hope that helps.

You identify the table in 3 ways -
I have two tables. 1 is the current inventory of Fuel Cards, and the second
is the new inventory of Fuel Cards.
SO I have list A and List B.
ex_FuelCardInput AND t_FuelCardInventory

ex_FuelCardInput --
[Fuel Card Source]
[Fuel Card Provider]
[Fuel Card Number]
VIN

B does not have a key but for all intents and purposes, it has the same key (though it is not defined within access).
All you gave for t_FuelCardInventory was FuelCardNo. What are the other
fields?

Too many peices missing.

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I am using Access 2003 with no ability to switch versions.

I have two tables. 1 is the current inventory of Fuel Cards, and the second
is the new inventory of Fuel Cards. I want to find all the NEW Fuel Cards
that exist only in the second table.

SO I have list A and List B. I want to find all the entries in B that are
not in A.

A has a two field Primary key. (Fuel Card # and FuelCard Provider)
B does not have a key but for all intents and purposes, it has the same key
( though it is not defined within access).

Here is the SQL code the wizard gave me.

SELECT ex_FuelCardInput.[Fuel Card Source], ex_FuelCardInput.[Fuel Card
Provider], ex_FuelCardInput.[Fuel Card Number], ex_FuelCardInput.VIN
FROM ex_FuelCardInput LEFT JOIN t_FuelCardInventory ON
ex_FuelCardInput.[Fuel Card Number] = t_FuelCardInventory.FuelCardNo
WHERE (((t_FuelCardInventory.FuelCardNo) Is Null));

The problem is, and this is evident from the design mode, is that this only
is keying on the FuelCardNo. It does not look at the Fuel Card Provider. So
I repeated a FuelCardNo with a different Provider and it returned the Card
(bravo) but showed the repeated number as Null.

FCNo FCPRov FCBlahblah
1 Bob jalskhfa
2 Tom akgjgh
(1) Tom agfhsfgh


the (1) did not display. that cell was blank. But the record was included
in the list.

I tried adding another column with t_FuelCardInventory.FCProvider = Is Null
and that didn't work (DID NOT CHANGE ANYTHING)

I tried making the criteria on FCNo = is null AND
IsNull(t_Fuelcardinventory.fcprovider) and that made the query return ZERO
results.

Thanks!