Union Query - Select Distinct 2 fields

[Vittles]

I have a union query that pulls up a list of peoples names and some details
from 2 tables. One of the tables may have duplicated names (with
non-duplicated other details). For example, the 2nd table may list 3 entries
for John Smith with different city/state/country entries when I need to only
combine one of John Smith's records (understanding that the other fields may
not be filled in/accurate).

Is there a way to add another 'where' statement for the [Core-Project Info]
data selection statement to only pick one record based only on the first &
last name fields or what is the best way?

My current union query:
SELECT [PI FName], [PI MI], [PI LName], [PI Suf], [PI Degree], [PI Dept],
[Inst Name], [City], [State], [Country], [LOI-AB ID] FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

UNION SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName], [CP
Leader Suf], [CP Leader Degree], [CP Leader Dept], [CP Institution], [CP
City], [CP State], [CP Country], [LOI-AB ID] FROM [Core-Project Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

Thanks.

 

[John W. Vinson]

Is there a way to add another 'where' statement for the [Core-Project Info]
data selection statement to only pick one record based only on the first &
last name fields or what is the best way?

A Totals query grouping by the name fields and selecting First() for each
additional field will pick one (essentially arbitrary) record for that name.

 

[Ken Sheridan]

Unless you know which set of columns is going to differ between rows with the
same first and last names you'll need to compare all of them, with the
exception of LOI-AB ID as this will be identical in all rows by virtue of the
parameter. You can then return an arbitrary row for each person by means of
a subquery which uses any aggregate function to limit its result to one row,
e.g.

SELECT [PI FName], [PI MI], [PI LName], [PI Suf],
[PI Degree], [PI Dept], [Inst Name], [City], [State],
[Country], [LOI-AB ID]
FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
UNION
SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName],
[CP Leader Suf], [CP Leader Degree], [CP Leader Dept],
[CP Institution], [CP City], [CP State], [CP Country], [LOI-AB ID]
FROM [Core-Project Info] AS CPI1
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
AND [CP Leader MI] & [CP Leader Suf] & [CP Leader Degree]
& [CP Leader Dept] & [CP Institution] & [CP City]
& [CP State] & [CP Country] =
(SELECT MAX([CP Leader MI] & [CP Leader Suf]
& [CP Leader Degree] & [CP Leader Dept]
& [CP Institution] & [CP City]
& [CP State] & [CP Country])
FROM [Core-Project Info] AS CPI2
WHERE CPI2.[CP Leader FName] = CPI1.[CP Leader FName]
AND CPI2.[CP Leader LName] = CPI1.[CP Leader LName]);

However, relying on names as keys like this is prone to error as names can
be duplicated, even within small groups of people. I once worked with two
Maggie Taylors in one office.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

I did wonder about doing that, but wasn't sure whether the FIRST operator
would always pick the same row for each column its applied to. Trying it
out, however, it looks like it does, so it’s a more efficient solution than a
subquery.

Doesn't get round the potential problem of two people with the same name
though.

Ken Sheridan
Stafford, England

Is there a way to add another 'where' statement for the [Core-Project Info]
data selection statement to only pick one record based only on the first &
last name fields or what is the best way?

A Totals query grouping by the name fields and selecting First() for each
additional field will pick one (essentially arbitrary) record for that name.

 

[Vittles]

Based on my experience any field can vary due to the field not being filled
in/applicable or various data entry errors. Where I work they usually get
the first/last names correct and in this case it really is a limited set of
data - only sub data related to one main table record.

Normally I would stick to using the ID field to differentiate different
people - that is the best way for other uses. In this case I am coming up
with a combined list of people and then appending that list to another table
also related to the main table record. Based on that use and the limited
data, one person's name will most likely be connected to one person and I
would rather have them add details or possibly one more record than delete 10
other records for the same name (which would be common).

I am going to play with the solutions listed today and will respond with
what worked best later. Thanks a lot for the ideas!

Unless you know which set of columns is going to differ between rows with the
same first and last names you'll need to compare all of them, with the
exception of LOI-AB ID as this will be identical in all rows by virtue of the
parameter. You can then return an arbitrary row for each person by means of
a subquery which uses any aggregate function to limit its result to one row,
e.g.

SELECT [PI FName], [PI MI], [PI LName], [PI Suf],
[PI Degree], [PI Dept], [Inst Name], [City], [State],
[Country], [LOI-AB ID]
FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
UNION
SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName],
[CP Leader Suf], [CP Leader Degree], [CP Leader Dept],
[CP Institution], [CP City], [CP State], [CP Country], [LOI-AB ID]
FROM [Core-Project Info] AS CPI1
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
AND [CP Leader MI] & [CP Leader Suf] & [CP Leader Degree]
& [CP Leader Dept] & [CP Institution] & [CP City]
& [CP State] & [CP Country] =
(SELECT MAX([CP Leader MI] & [CP Leader Suf]
& [CP Leader Degree] & [CP Leader Dept]
& [CP Institution] & [CP City]
& [CP State] & [CP Country])
FROM [Core-Project Info] AS CPI2
WHERE CPI2.[CP Leader FName] = CPI1.[CP Leader FName]
AND CPI2.[CP Leader LName] = CPI1.[CP Leader LName]);

However, relying on names as keys like this is prone to error as names can
be duplicated, even within small groups of people. I once worked with two
Maggie Taylors in one office.

Ken Sheridan
Stafford, England

I have a union query that pulls up a list of peoples names and some details
from 2 tables. One of the tables may have duplicated names (with
non-duplicated other details). For example, the 2nd table may list 3 entries
for John Smith with different city/state/country entries when I need to only
combine one of John Smith's records (understanding that the other fields may
not be filled in/accurate).

Is there a way to add another 'where' statement for the [Core-Project Info]
data selection statement to only pick one record based only on the first &
last name fields or what is the best way?

My current union query:
SELECT [PI FName], [PI MI], [PI LName], [PI Suf], [PI Degree], [PI Dept],
[Inst Name], [City], [State], [Country], [LOI-AB ID] FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

UNION SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName], [CP
Leader Suf], [CP Leader Degree], [CP Leader Dept], [CP Institution], [CP
City], [CP State], [CP Country], [LOI-AB ID] FROM [Core-Project Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

Thanks.

 

[Vittles]

I played with both response variations and ended up mixing them a bit. I
still have my basic union query. Then I created a totals query based on the
union query similar to what was suggested, grouping by the main table ID
field and the 2 name fields, except that I used 'Max' instead of 'First' on
the rest which seems to return the record with the most filled in fields
(though I know the function is based on field size - so wrong results could
be listed).

The other suggestion while good, still listed more than one record for the
exact same first and last name - so I used the 'Max' part of that query for
the new one.

Here is the SQL of my final query:
SELECT DISTINCT [PI-CP COI UnionQ].[LOI-AB ID], [PI-CP COI UnionQ].[PI
FName] AS FName, [PI-CP COI UnionQ].[PI LName] AS LName, Max([PI-CP COI
UnionQ].[PI MI]) AS MI, Max([PI-CP COI UnionQ].[PI Suf]) AS Suf, Max([PI-CP
COI UnionQ].[PI Degree]) AS Degree, Max([PI-CP COI UnionQ].[PI Dept]) AS
Dept, Max([PI-CP COI UnionQ].[Inst Name]) AS Inst, Max([PI-CP COI
UnionQ].City) AS City, Max([PI-CP COI UnionQ].State) AS State, Max([PI-CP COI
UnionQ].Country) AS Country
FROM [PI-CP COI UnionQ]
GROUP BY [PI-CP COI UnionQ].[LOI-AB ID], [PI-CP COI UnionQ].[PI FName],
[PI-CP COI UnionQ].[PI LName];

Thanks to both for guiding me to this answer!

Based on my experience any field can vary due to the field not being filled
in/applicable or various data entry errors. Where I work they usually get
the first/last names correct and in this case it really is a limited set of
data - only sub data related to one main table record.

Normally I would stick to using the ID field to differentiate different
people - that is the best way for other uses. In this case I am coming up
with a combined list of people and then appending that list to another table
also related to the main table record. Based on that use and the limited
data, one person's name will most likely be connected to one person and I
would rather have them add details or possibly one more record than delete 10
other records for the same name (which would be common).

I am going to play with the solutions listed today and will respond with
what worked best later. Thanks a lot for the ideas!

Unless you know which set of columns is going to differ between rows with the
same first and last names you'll need to compare all of them, with the
exception of LOI-AB ID as this will be identical in all rows by virtue of the
parameter. You can then return an arbitrary row for each person by means of
a subquery which uses any aggregate function to limit its result to one row,
e.g.

SELECT [PI FName], [PI MI], [PI LName], [PI Suf],
[PI Degree], [PI Dept], [Inst Name], [City], [State],
[Country], [LOI-AB ID]
FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
UNION
SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName],
[CP Leader Suf], [CP Leader Degree], [CP Leader Dept],
[CP Institution], [CP City], [CP State], [CP Country], [LOI-AB ID]
FROM [Core-Project Info] AS CPI1
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
AND [CP Leader MI] & [CP Leader Suf] & [CP Leader Degree]
& [CP Leader Dept] & [CP Institution] & [CP City]
& [CP State] & [CP Country] =
(SELECT MAX([CP Leader MI] & [CP Leader Suf]
& [CP Leader Degree] & [CP Leader Dept]
& [CP Institution] & [CP City]
& [CP State] & [CP Country])
FROM [Core-Project Info] AS CPI2
WHERE CPI2.[CP Leader FName] = CPI1.[CP Leader FName]
AND CPI2.[CP Leader LName] = CPI1.[CP Leader LName]);

However, relying on names as keys like this is prone to error as names can
be duplicated, even within small groups of people. I once worked with two
Maggie Taylors in one office.

Ken Sheridan
Stafford, England

I have a union query that pulls up a list of peoples names and some details
from 2 tables. One of the tables may have duplicated names (with
non-duplicated other details). For example, the 2nd table may list 3 entries
for John Smith with different city/state/country entries when I need to only
combine one of John Smith's records (understanding that the other fields may
not be filled in/accurate).

Is there a way to add another 'where' statement for the [Core-Project Info]
data selection statement to only pick one record based only on the first &
last name fields or what is the best way?

My current union query:
SELECT [PI FName], [PI MI], [PI LName], [PI Suf], [PI Degree], [PI Dept],
[Inst Name], [City], [State], [Country], [LOI-AB ID] FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

UNION SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName], [CP
Leader Suf], [CP Leader Degree], [CP Leader Dept], [CP Institution], [CP
City], [CP State], [CP Country], [LOI-AB ID] FROM [Core-Project Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

Thanks.

 

[Ken Sheridan]

Glad to hear you got what you want. I think what John had in mind was:

SELECT [PI FName], [PI MI], [PI LName], [PI Suf],
[PI Degree], [PI Dept], [Inst Name], [City], [State],
[Country], [LOI-AB ID]
FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
UNION
SELECT [CP Leader FName], FIRST([CP Leader MI]),
[CP Leader LName], FIRST([CP Leader Suf]),
FIRST([CP Leader Degree]), FIRST([CP Leader Dept]),
FIRST([CP Institution]), FIRST([CP City]), FIRST([CP State]),
FIRST([CP Country]), FIRST([LOI-AB ID])
FROM [Core-Project Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
GROUP BY[CP Leader LName],CP Leader FName];

The second part of the UNION operation would return only one row per name,
which if I understand you correctly, is what you were aiming for. I'd
considered this solution, but wasn't sure whether the FIRST operator would
return a set of values all from the same row per last/first name, but when I
tried it myself it does seem to do so consistently, so John's solution would,
I think, do the trick and be an efficient one in performance terms.

Ken Sheridan
Stafford, England

I played with both response variations and ended up mixing them a bit. I
still have my basic union query. Then I created a totals query based on the
union query similar to what was suggested, grouping by the main table ID
field and the 2 name fields, except that I used 'Max' instead of 'First' on
the rest which seems to return the record with the most filled in fields
(though I know the function is based on field size - so wrong results could
be listed).

The other suggestion while good, still listed more than one record for the
exact same first and last name - so I used the 'Max' part of that query for
the new one.

Here is the SQL of my final query:
SELECT DISTINCT [PI-CP COI UnionQ].[LOI-AB ID], [PI-CP COI UnionQ].[PI
FName] AS FName, [PI-CP COI UnionQ].[PI LName] AS LName, Max([PI-CP COI
UnionQ].[PI MI]) AS MI, Max([PI-CP COI UnionQ].[PI Suf]) AS Suf, Max([PI-CP
COI UnionQ].[PI Degree]) AS Degree, Max([PI-CP COI UnionQ].[PI Dept]) AS
Dept, Max([PI-CP COI UnionQ].[Inst Name]) AS Inst, Max([PI-CP COI
UnionQ].City) AS City, Max([PI-CP COI UnionQ].State) AS State, Max([PI-CP COI
UnionQ].Country) AS Country
FROM [PI-CP COI UnionQ]
GROUP BY [PI-CP COI UnionQ].[LOI-AB ID], [PI-CP COI UnionQ].[PI FName],
[PI-CP COI UnionQ].[PI LName];

Thanks to both for guiding me to this answer!

Based on my experience any field can vary due to the field not being filled
in/applicable or various data entry errors. Where I work they usually get
the first/last names correct and in this case it really is a limited set of
data - only sub data related to one main table record.

Normally I would stick to using the ID field to differentiate different
people - that is the best way for other uses. In this case I am coming up
with a combined list of people and then appending that list to another table
also related to the main table record. Based on that use and the limited
data, one person's name will most likely be connected to one person and I
would rather have them add details or possibly one more record than delete 10
other records for the same name (which would be common).

I am going to play with the solutions listed today and will respond with
what worked best later. Thanks a lot for the ideas!

Unless you know which set of columns is going to differ between rows with the
same first and last names you'll need to compare all of them, with the
exception of LOI-AB ID as this will be identical in all rows by virtue of the
parameter. You can then return an arbitrary row for each person by means of
a subquery which uses any aggregate function to limit its result to one row,
e.g.

SELECT [PI FName], [PI MI], [PI LName], [PI Suf],
[PI Degree], [PI Dept], [Inst Name], [City], [State],
[Country], [LOI-AB ID]
FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
UNION
SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName],
[CP Leader Suf], [CP Leader Degree], [CP Leader Dept],
[CP Institution], [CP City], [CP State], [CP Country], [LOI-AB ID]
FROM [Core-Project Info] AS CPI1
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
AND [CP Leader MI] & [CP Leader Suf] & [CP Leader Degree]
& [CP Leader Dept] & [CP Institution] & [CP City]
& [CP State] & [CP Country] =
(SELECT MAX([CP Leader MI] & [CP Leader Suf]
& [CP Leader Degree] & [CP Leader Dept]
& [CP Institution] & [CP City]
& [CP State] & [CP Country])
FROM [Core-Project Info] AS CPI2
WHERE CPI2.[CP Leader FName] = CPI1.[CP Leader FName]
AND CPI2.[CP Leader LName] = CPI1.[CP Leader LName]);

However, relying on names as keys like this is prone to error as names can
be duplicated, even within small groups of people. I once worked with two
Maggie Taylors in one office.

Ken Sheridan
Stafford, England

:

I have a union query that pulls up a list of peoples names and some details
from 2 tables. One of the tables may have duplicated names (with
non-duplicated other details). For example, the 2nd table may list 3 entries
for John Smith with different city/state/country entries when I need to only
combine one of John Smith's records (understanding that the other fields may
not be filled in/accurate).

Is there a way to add another 'where' statement for the [Core-Project Info]
data selection statement to only pick one record based only on the first &
last name fields or what is the best way?

My current union query:
SELECT [PI FName], [PI MI], [PI LName], [PI Suf], [PI Degree], [PI Dept],
[Inst Name], [City], [State], [Country], [LOI-AB ID] FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

UNION SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName], [CP
Leader Suf], [CP Leader Degree], [CP Leader Dept], [CP Institution], [CP
City], [CP State], [CP Country], [LOI-AB ID] FROM [Core-Project Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

Thanks.

 

[Vittles]

The data quirk I (just figured out) found is that there may be a person's
name from the main table [Admin Book Info] that is repeated on the
[Core-Project Info] table. The SQL statement listed below works great for
dealing with duplicates on the sub table, but not when combining both tables.
I still ended up with 2 listings for the same person, 1 from the main table
and the 'First' or 'Max' from the subtable. The 2nd post-union query
combines all of these values under the same name/ID. Always learning
something!

Glad to hear you got what you want. I think what John had in mind was:

SELECT [PI FName], [PI MI], [PI LName], [PI Suf],
[PI Degree], [PI Dept], [Inst Name], [City], [State],
[Country], [LOI-AB ID]
FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
UNION
SELECT [CP Leader FName], FIRST([CP Leader MI]),
[CP Leader LName], FIRST([CP Leader Suf]),
FIRST([CP Leader Degree]), FIRST([CP Leader Dept]),
FIRST([CP Institution]), FIRST([CP City]), FIRST([CP State]),
FIRST([CP Country]), FIRST([LOI-AB ID])
FROM [Core-Project Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
GROUP BY[CP Leader LName],CP Leader FName];

The second part of the UNION operation would return only one row per name,
which if I understand you correctly, is what you were aiming for. I'd
considered this solution, but wasn't sure whether the FIRST operator would
return a set of values all from the same row per last/first name, but when I
tried it myself it does seem to do so consistently, so John's solution would,
I think, do the trick and be an efficient one in performance terms.

Ken Sheridan
Stafford, England

I played with both response variations and ended up mixing them a bit. I
still have my basic union query. Then I created a totals query based on the
union query similar to what was suggested, grouping by the main table ID
field and the 2 name fields, except that I used 'Max' instead of 'First' on
the rest which seems to return the record with the most filled in fields
(though I know the function is based on field size - so wrong results could
be listed).

The other suggestion while good, still listed more than one record for the
exact same first and last name - so I used the 'Max' part of that query for
the new one.

Here is the SQL of my final query:
SELECT DISTINCT [PI-CP COI UnionQ].[LOI-AB ID], [PI-CP COI UnionQ].[PI
FName] AS FName, [PI-CP COI UnionQ].[PI LName] AS LName, Max([PI-CP COI
UnionQ].[PI MI]) AS MI, Max([PI-CP COI UnionQ].[PI Suf]) AS Suf, Max([PI-CP
COI UnionQ].[PI Degree]) AS Degree, Max([PI-CP COI UnionQ].[PI Dept]) AS
Dept, Max([PI-CP COI UnionQ].[Inst Name]) AS Inst, Max([PI-CP COI
UnionQ].City) AS City, Max([PI-CP COI UnionQ].State) AS State, Max([PI-CP COI
UnionQ].Country) AS Country
FROM [PI-CP COI UnionQ]
GROUP BY [PI-CP COI UnionQ].[LOI-AB ID], [PI-CP COI UnionQ].[PI FName],
[PI-CP COI UnionQ].[PI LName];

Thanks to both for guiding me to this answer!

Based on my experience any field can vary due to the field not being filled
in/applicable or various data entry errors. Where I work they usually get
the first/last names correct and in this case it really is a limited set of
data - only sub data related to one main table record.

Normally I would stick to using the ID field to differentiate different
people - that is the best way for other uses. In this case I am coming up
with a combined list of people and then appending that list to another table
also related to the main table record. Based on that use and the limited
data, one person's name will most likely be connected to one person and I
would rather have them add details or possibly one more record than delete 10
other records for the same name (which would be common).

I am going to play with the solutions listed today and will respond with
what worked best later. Thanks a lot for the ideas!

:

Unless you know which set of columns is going to differ between rows with the
same first and last names you'll need to compare all of them, with the
exception of LOI-AB ID as this will be identical in all rows by virtue of the
parameter. You can then return an arbitrary row for each person by means of
a subquery which uses any aggregate function to limit its result to one row,
e.g.

SELECT [PI FName], [PI MI], [PI LName], [PI Suf],
[PI Degree], [PI Dept], [Inst Name], [City], [State],
[Country], [LOI-AB ID]
FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
UNION
SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName],
[CP Leader Suf], [CP Leader Degree], [CP Leader Dept],
[CP Institution], [CP City], [CP State], [CP Country], [LOI-AB ID]
FROM [Core-Project Info] AS CPI1
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]
AND [CP Leader MI] & [CP Leader Suf] & [CP Leader Degree]
& [CP Leader Dept] & [CP Institution] & [CP City]
& [CP State] & [CP Country] =
(SELECT MAX([CP Leader MI] & [CP Leader Suf]
& [CP Leader Degree] & [CP Leader Dept]
& [CP Institution] & [CP City]
& [CP State] & [CP Country])
FROM [Core-Project Info] AS CPI2
WHERE CPI2.[CP Leader FName] = CPI1.[CP Leader FName]
AND CPI2.[CP Leader LName] = CPI1.[CP Leader LName]);

However, relying on names as keys like this is prone to error as names can
be duplicated, even within small groups of people. I once worked with two
Maggie Taylors in one office.

Ken Sheridan
Stafford, England

:

I have a union query that pulls up a list of peoples names and some details
from 2 tables. One of the tables may have duplicated names (with
non-duplicated other details). For example, the 2nd table may list 3 entries
for John Smith with different city/state/country entries when I need to only
combine one of John Smith's records (understanding that the other fields may
not be filled in/accurate).

Is there a way to add another 'where' statement for the [Core-Project Info]
data selection statement to only pick one record based only on the first &
last name fields or what is the best way?

My current union query:
SELECT [PI FName], [PI MI], [PI LName], [PI Suf], [PI Degree], [PI Dept],
[Inst Name], [City], [State], [Country], [LOI-AB ID] FROM [Admin Book Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

UNION SELECT [CP Leader FName], [CP Leader MI], [CP Leader LName], [CP
Leader Suf], [CP Leader Degree], [CP Leader Dept], [CP Institution], [CP
City], [CP State], [CP Country], [LOI-AB ID] FROM [Core-Project Info]
WHERE [LOI-AB ID]=[Forms]![Main Review Form]![LOI-AB ID]

Thanks.