Queries and Table Field with Multiple Entries

[MAX]

This is a little tricky. I have two tables. Table 1 "Bid
Packages" has a Field [Bid Package]and another Field [CE
Responsible]. Table 2 "Submittals" also has [Bid Package]
amongst other fields.

What I want to do is link the two tables by [Bid
Package]. Problem is that in Table 1 the [Bid Package]
are unique entries, 001, 002, 003 etc. Table 2 [Bid
Package]is multiple entries: 001/002/008, 004, etc. I can
run queries that filter out BPs by using like*"002"*.
What can I do to create a query that sorts the Table 1
[BP] with info from Table 2? Because the [BP] field in
Table 2 are not unique entries, Table 1 will not recognize
the data. Can anything be written to be able to pull out
the unique items in [BP] in Table 1 and link them to the
multiple items in [BP] in Table 1?

Many Thanks for any help on this one.

Max

 

[Brian Camire]

In the short term, you might try a query whose SQL looks something like
this:

SELECT
[Bid Packages].*,
[Submittals].*
FROM
[Bid Packages],
[Submittals]
WHERE
[Submittals].[Bid Package] = [Bid Packages].[Bid Package]
OR
[Submittals].[Bid Package] Like [Bid Packages].[Bid Package] & "/*"
OR
[Submittals].[Bid Package] Like "*/" & [Bid Packages].[Bid Package] & "/*"
OR
[Submittals].[Bid Package] Like "*/" & [Bid Packages].[Bid Package]

This will return combinations of records from the two tables where the Bid
Package in the Submittals table is equal to, starts with, contains, or ends
with the Bid Package in the Bid Packages table. This assumes the Bid
Packages in the Submittals table are delimited by the "/" character, and
that there are no leading or trailing delimiters.

In the long run, you might instead consider creating a third table (maybe
called Submittal Bid Packages). It might contain a Bid Package field, along
with the field(s) of the primary key of the Submittals table.

 

[MAX]

Hello,

I tried the code for two other tables, same principal, but
it didn't work:

Tables: [OCPAC CORRESPONDENCE LOG] AND [OCPAC FEI Employee
Codes]
Joined field: [ACTION BY]

The [OCPAC CORRESPONDENCE LOG] has around 10 fields, the
[OCPAC FEI Employee Codes] has 3 fields. They are joined
by the [ACTION BY] field. In the {[OCPAC CORRESPONDENCE
LOG] the [ACTION BY] can be multiples MY, or MY/TS, etc.
In the other table it is unique. The error I am getting
is invalid bracketing of name ".
Code is:
SELECT [OCPAC CORRESPONDENCE LOG] .*,
[OCPAC FEI Employee Codes] .*
FROM [OCPAC CORRESPONDENCE LOG], [OCPAC FEI Employee Codes]
WHERE [OCPAC CORRESPONDENCE LOG].[ACTION BY] = [OCPAC FEI
Employee Codes].[ACTION BY]
OR
[OCPAC CORRESPONDENCE LOG].[ACTION BY] LIKE [OCPAC FEI
Employee Codes].[ACTION BY] & "*/*"
OR
[OCPAC CORRESPONDENCE LOG].[ACTION BY] LIKE "*/" & [OCPAC
FEI Employee Codes].[ACTION BY] & "/*"
OR
[OCPAC CORRESPONDENCE LOG].[ACTION BY] LIKE "*/" & [OCPAC
FEI Employee Codes].[ACTION BY];

Any Ideas

-----Original Message-----
In the short term, you might try a query whose SQL looks something like
this:

SELECT
[Bid Packages].*,
[Submittals].*
FROM
[Bid Packages],
[Submittals]
WHERE
[Submittals].[Bid Package] = [Bid Packages].[Bid Package]
OR
[Submittals].[Bid Package] Like [Bid Packages].[Bid Package] & "/*"
OR
[Submittals].[Bid Package] Like "*/" & [Bid Packages]. [Bid Package] & "/*"
OR
[Submittals].[Bid Package] Like "*/" & [Bid Packages]. [Bid Package]

This will return combinations of records from the two tables where the Bid
Package in the Submittals table is equal to, starts with, contains, or ends
with the Bid Package in the Bid Packages table. This assumes the Bid
Packages in the Submittals table are delimited by the "/" character, and
that there are no leading or trailing delimiters.

In the long run, you might instead consider creating a third table (maybe
called Submittal Bid Packages). It might contain a Bid Package field, along
with the field(s) of the primary key of the Submittals table.

This is a little tricky. I have two tables. Table 1 "Bid
Packages" has a Field [Bid Package]and another Field [CE
Responsible]. Table 2 "Submittals" also has [Bid Package]
amongst other fields.

What I want to do is link the two tables by [Bid
Package]. Problem is that in Table 1 the [Bid Package]
are unique entries, 001, 002, 003 etc. Table 2 [Bid
Package]is multiple entries: 001/002/008, 004, etc. I can
run queries that filter out BPs by using like*"002"*.
What can I do to create a query that sorts the Table 1
[BP] with info from Table 2? Because the [BP] field in
Table 2 are not unique entries, Table 1 will not recognize
the data. Can anything be written to be able to pull out
the unique items in [BP] in Table 1 and link them to the
multiple items in [BP] in Table 1?

Many Thanks for any help on this one.

Max

.

 

[MAX]

OOOPs my bad. I had an extra space in the code. After
all this, I realize that the problem I have is not in the
query, but the grouping in the report. Same principal.
Under the [OCPAC FEI Employee] table I have a [Full
Name]. In the report I wanted to be able to pull in all
records that included the [ACTION BY] and group it by the
individual.

Any ideas.

-----Original Message-----
Hello,

I tried the code for two other tables, same principal, but
it didn't work:

Tables: [OCPAC CORRESPONDENCE LOG] AND [OCPAC FEI Employee]
Codes]
Joined field: [ACTION BY]

The [OCPAC CORRESPONDENCE LOG] has around 10 fields, the
[OCPAC FEI Employee Codes] has 3 fields. They are joined
by the [ACTION BY] field. In the {[OCPAC CORRESPONDENCE
LOG] the [ACTION BY] can be multiples MY, or MY/TS, etc.
In the other table it is unique. The error I am getting
is invalid bracketing of name ".
Code is:
SELECT [OCPAC CORRESPONDENCE LOG] .*,
[OCPAC FEI Employee Codes] .*
FROM [OCPAC CORRESPONDENCE LOG], [OCPAC FEI Employee Codes]
WHERE [OCPAC CORRESPONDENCE LOG].[ACTION BY] = [OCPAC FEI
Employee Codes].[ACTION BY]
OR
[OCPAC CORRESPONDENCE LOG].[ACTION BY] LIKE [OCPAC FEI
Employee Codes].[ACTION BY] & "*/*"
OR
[OCPAC CORRESPONDENCE LOG].[ACTION BY] LIKE "*/" & [OCPAC
FEI Employee Codes].[ACTION BY] & "/*"
OR
[OCPAC CORRESPONDENCE LOG].[ACTION BY] LIKE "*/" & [OCPAC
FEI Employee Codes].[ACTION BY];

Any Ideas

-----Original Message-----
In the short term, you might try a query whose SQL looks something like
this:

SELECT
[Bid Packages].*,
[Submittals].*
FROM
[Bid Packages],
[Submittals]
WHERE
[Submittals].[Bid Package] = [Bid Packages].[Bid Package]
OR
[Submittals].[Bid Package] Like [Bid Packages].[Bid Package] & "/*"
OR
[Submittals].[Bid Package] Like "*/" & [Bid Packages]. [Bid Package] & "/*"
OR
[Submittals].[Bid Package] Like "*/" & [Bid Packages]. [Bid Package]

This will return combinations of records from the two tables where the Bid
Package in the Submittals table is equal to, starts

with,
contains, or ends

with the Bid Package in the Bid Packages table. This assumes the Bid
Packages in the Submittals table are delimited by

the "/"
character, and

that there are no leading or trailing delimiters.

In the long run, you might instead consider creating a third table (maybe
called Submittal Bid Packages). It might contain a Bid Package field, along
with the field(s) of the primary key of the Submittals table.

This is a little tricky. I have two tables. Table 1 "Bid
Packages" has a Field [Bid Package]and another Field [CE
Responsible]. Table 2 "Submittals" also has [Bid Package]
amongst other fields.

What I want to do is link the two tables by [Bid
Package]. Problem is that in Table 1 the [Bid Package]
are unique entries, 001, 002, 003 etc. Table 2 [Bid
Package]is multiple entries: 001/002/008, 004, etc. I can
run queries that filter out BPs by using like*"002"*.
What can I do to create a query that sorts the Table 1
[BP] with info from Table 2? Because the [BP] field in
Table 2 are not unique entries, Table 1 will not recognize
the data. Can anything be written to be able to pull out
the unique items in [BP] in Table 1 and link them to the
multiple items in [BP] in Table 1?

Many Thanks for any help on this one.

Max

.

.