Look up in query or For Each In Next?

[Guest]

Hi, I'm trying to figure out the best way to do this ...

I am building an audit database to record audit details and ISO clauses
attached to each audit.

What I want to do is identify which audits have not had clauses allocated.
The allocation form is a pop-up that opens with link criteria based on the
Audit ID. The source for this is a table (tblISOCoverage).

Can I Dlookup things in a query? I have set up qryISOCoverage which shows
all AuditIDs that have had a specific clause assigned. Since an audit that
has not had any clauses will not appear in this query, I thought of using Is
Null/DLookup for each AuditId in tblAuditDetails (the main table) but am not
sure how to do this. Would a recordset and For Each In Next work better?

Can anyone help?

Thanks

 

[pietlinden]

Hi, I'm trying to figure out the best way to do this ...

I am building an audit database to record audit details and ISO clauses
attached to each audit.

What I want to do is identify which audits have not had clauses allocated.
The allocation form is a pop-up that opens with link criteria based on the
Audit ID. The source for this is a table (tblISOCoverage).

Can I Dlookup things in a query? I have set up qryISOCoverage which shows
all AuditIDs that have had a specific clause assigned. Since an audit that
has not had any clauses will not appear in this query, I thought of using Is
Null/DLookup for each AuditId in tblAuditDetails (the main table) but am not
sure how to do this. Would a recordset and For Each In Next work better?

Can anyone help?

Thanks

use an outer join. (kind of hard to tell without your table
structures, though). If you use the "Find Unmatched" query wizard, it
will walk you through it. In a nutshell, it looks like this...

SELECT tableA.[FieldName],...
FROM tableA LEFT JOIN tableB ON [tableA].
[PrimaryKeyFieldName]=[tableB].[ForeignKeyFieldName]
WHERE tableB.[ForeignKeyFieldName] IS NULL;

if you do it in the query grid, add both tables to the grid, then
right-click the join line between them, and select option 2 or 3,
whichever join type you need.

 

[pietlinden]

Hi, I'm trying to figure out the best way to do this ...

I am building an audit database to record audit details and ISO clauses
attached to each audit.

What I want to do is identify which audits have not had clauses allocated.
The allocation form is a pop-up that opens with link criteria based on the
Audit ID. The source for this is a table (tblISOCoverage).

Can I Dlookup things in a query? I have set up qryISOCoverage which shows
all AuditIDs that have had a specific clause assigned. Since an audit that
has not had any clauses will not appear in this query, I thought of using Is
Null/DLookup for each AuditId in tblAuditDetails (the main table) but am not
sure how to do this. Would a recordset and For Each In Next work better?

Can anyone help?

Thanks

use an outer join. (kind of hard to tell without your table
structures, though). If you use the "Find Unmatched" query wizard, it
will walk you through it. In a nutshell, it looks like this...

SELECT tableA.[FieldName],...
FROM tableA LEFT JOIN tableB ON [tableA].
[PrimaryKeyFieldName]=[tableB].[ForeignKeyFieldName]
WHERE tableB.[ForeignKeyFieldName] IS NULL;

if you do it in the query grid, add both tables to the grid, then
right-click the join line between them, and select option 2 or 3,
whichever join type you need.

on second thought... if you create a table of characters from a-z, you
can just use a query to generate the records if you have a table
containing the parent record id (the part before the dash). Then you
could do something like

SELECT tblLetters.Letter, tblTest.PK, [PK] & [Letter] AS
ConcatenatedKey
FROM tblLetters, tblTest
WHERE (((tblLetters.Letter) Between "a" And [Forms]![Form2]!
[txtLastChar]) AND ((tblTest.PK)=[Forms]![Form2]!
[txtParentRecordID]));

turn that into an append query, and have it append to your records
table and you should be good to go... Figured there had to be an easy
SQL-only way...

Sharp as a marble some days...

 

[Guest]

Hi,

Sharp you are - unmatched query wizard worked perfectly!

(I've taken a note of the SQL so I can learn ...)

Many thanks

Hi, I'm trying to figure out the best way to do this ...

I am building an audit database to record audit details and ISO clauses
attached to each audit.

What I want to do is identify which audits have not had clauses allocated.
The allocation form is a pop-up that opens with link criteria based on the
Audit ID. The source for this is a table (tblISOCoverage).

Can I Dlookup things in a query? I have set up qryISOCoverage which shows
all AuditIDs that have had a specific clause assigned. Since an audit that
has not had any clauses will not appear in this query, I thought of using Is
Null/DLookup for each AuditId in tblAuditDetails (the main table) but am not
sure how to do this. Would a recordset and For Each In Next work better?

Can anyone help?

Thanks

use an outer join. (kind of hard to tell without your table
structures, though). If you use the "Find Unmatched" query wizard, it
will walk you through it. In a nutshell, it looks like this...

SELECT tableA.[FieldName],...
FROM tableA LEFT JOIN tableB ON [tableA].
[PrimaryKeyFieldName]=[tableB].[ForeignKeyFieldName]
WHERE tableB.[ForeignKeyFieldName] IS NULL;

if you do it in the query grid, add both tables to the grid, then
right-click the join line between them, and select option 2 or 3,
whichever join type you need.

on second thought... if you create a table of characters from a-z, you
can just use a query to generate the records if you have a table
containing the parent record id (the part before the dash). Then you
could do something like

SELECT tblLetters.Letter, tblTest.PK, [PK] & [Letter] AS
ConcatenatedKey
FROM tblLetters, tblTest
WHERE (((tblLetters.Letter) Between "a" And [Forms]![Form2]!
[txtLastChar]) AND ((tblTest.PK)=[Forms]![Form2]!
[txtParentRecordID]));

turn that into an append query, and have it append to your records
table and you should be good to go... Figured there had to be an easy
SQL-only way...

Sharp as a marble some days...