Incomplete Batches

[richard]

Hi

Need to select records where a batch is completed
Batch is made up of "Items" which are complete when field [Result] has a
value in it.
I need my query to select batch numbers for which all "Items" are completed
ignoring any part completed batches.

Any help greatly appreciated

Richard

 

[Allen Browne]

Use a subquery to check for any uncompleted Items.

Assuming you have:
- a table of Batches with a BatchID primary key
- a table of Items, with:
* a BatchID foreign key
* a Result field (blank until done)
then the query might look like this:

SELECT Batches.*
FROM Batches
WHERE NOT EXISTS (
SELECT BatchID FROM Items
WHERE Items.BatchID = Batches.BatchID
AND Items.Result Is Null)

If subqueries are new, here's a starting point:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

 

[richard]

Allen

Thanks this worked wonderfully, however the powers that be now want to be
able to set a date selection.
I have tried to include a date field [Analysis Date]in the selection from
the table [items], but this returns all the records for the completed batch,
when I want to avoid duplication of the records within the batch.
I was not aware of subqueries and the article was worthwhile. Could you
please give me a nudge in the right direction for the above problem because
the big thrill (sad I know) is actually getting to the solution yourself.

thanks

Richard

Use a subquery to check for any uncompleted Items.

Assuming you have:
- a table of Batches with a BatchID primary key
- a table of Items, with:
* a BatchID foreign key
* a Result field (blank until done)
then the query might look like this:

SELECT Batches.*
FROM Batches
WHERE NOT EXISTS (
SELECT BatchID FROM Items
WHERE Items.BatchID = Batches.BatchID
AND Items.Result Is Null)

If subqueries are new, here's a starting point:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Need to select records where a batch is completed
Batch is made up of "Items" which are complete when field [Result] has a
value in it.
I need my query to select batch numbers for which all "Items" are
completed
ignoring any part completed batches.

Any help greatly appreciated

Richard

 

[Allen Browne]

Not sure I understand the issue.

If you just want to deduplicate the results you may be able to do that by
adding DISTINCT to the query (immediately after SELECT.)

If you have the same table in the main query and in the subquery, you may
need to alias one of them so Access knows which one you mean.

If that's not the issue, post your SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Thanks this worked wonderfully, however the powers that be now want to be
able to set a date selection.
I have tried to include a date field [Analysis Date]in the selection from
the table [items], but this returns all the records for the completed
batch,
when I want to avoid duplication of the records within the batch.
I was not aware of subqueries and the article was worthwhile. Could you
please give me a nudge in the right direction for the above problem
because
the big thrill (sad I know) is actually getting to the solution yourself.

thanks

Richard

Use a subquery to check for any uncompleted Items.

Assuming you have:
- a table of Batches with a BatchID primary key
- a table of Items, with:
* a BatchID foreign key
* a Result field (blank until done)
then the query might look like this:

SELECT Batches.*
FROM Batches
WHERE NOT EXISTS (
SELECT BatchID FROM Items
WHERE Items.BatchID = Batches.BatchID
AND Items.Result Is Null)

If subqueries are new, here's a starting point:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Need to select records where a batch is completed
Batch is made up of "Items" which are complete when field [Result] has
a
value in it.
I need my query to select batch numbers for which all "Items" are
completed
ignoring any part completed batches.

Any help greatly appreciated

Richard

 

[richard]

SQL statement as requested. The DISTINCT worked for the deduplicating the
batches, however if a batch has records spanning more than one day then the
query returns a record for each batchnumber and date. Hope this is clearer.


SELECT DISTINCT [Batch Details].[Batch Number], [Sample Details].[Analysis
Date]
FROM [Batch Details] INNER JOIN [Sample Details] ON [Batch Details].[Batch
Number] = [Sample Details].[Batch Number]
WHERE ((([Sample Details].[Analysis Date]) Between [Start Date] And [End
Date]) AND ((Exists (SELECT[Batch Number] FROM [Sample Details] WHERE [Sample
Details].[Batch Number] = [Batch Details].[Batch Number] AND [Sample
Details].Result Is Null))=False))
ORDER BY [Batch Details].[Batch Number];

Not sure I understand the issue.

If you just want to deduplicate the results you may be able to do that by
adding DISTINCT to the query (immediately after SELECT.)

If you have the same table in the main query and in the subquery, you may
need to alias one of them so Access knows which one you mean.

If that's not the issue, post your SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Thanks this worked wonderfully, however the powers that be now want to be
able to set a date selection.
I have tried to include a date field [Analysis Date]in the selection from
the table [items], but this returns all the records for the completed
batch,
when I want to avoid duplication of the records within the batch.
I was not aware of subqueries and the article was worthwhile. Could you
please give me a nudge in the right direction for the above problem
because
the big thrill (sad I know) is actually getting to the solution yourself.

thanks

Richard

Use a subquery to check for any uncompleted Items.

Assuming you have:
- a table of Batches with a BatchID primary key
- a table of Items, with:
* a BatchID foreign key
* a Result field (blank until done)
then the query might look like this:

SELECT Batches.*
FROM Batches
WHERE NOT EXISTS (
SELECT BatchID FROM Items
WHERE Items.BatchID = Batches.BatchID
AND Items.Result Is Null)

If subqueries are new, here's a starting point:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Need to select records where a batch is completed
Batch is made up of "Items" which are complete when field [Result] has
a
value in it.
I need my query to select batch numbers for which all "Items" are
completed
ignoring any part completed batches.

Any help greatly appreciated

Richard

 

[Allen Browne]

Do you need the date? Or could you just uncheck the Show box under the date
field?

If you do want the date, which date when there are multiples?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SQL statement as requested. The DISTINCT worked for the deduplicating the
batches, however if a batch has records spanning more than one day then
the
query returns a record for each batchnumber and date. Hope this is
clearer.


SELECT DISTINCT [Batch Details].[Batch Number], [Sample
Details].[Analysis
Date]
FROM [Batch Details] INNER JOIN [Sample Details] ON [Batch Details].[Batch
Number] = [Sample Details].[Batch Number]
WHERE ((([Sample Details].[Analysis Date]) Between [Start Date] And [End
Date]) AND ((Exists (SELECT[Batch Number] FROM [Sample Details] WHERE
[Sample
Details].[Batch Number] = [Batch Details].[Batch Number] AND [Sample
Details].Result Is Null))=False))
ORDER BY [Batch Details].[Batch Number];

Not sure I understand the issue.

If you just want to deduplicate the results you may be able to do that by
adding DISTINCT to the query (immediately after SELECT.)

If you have the same table in the main query and in the subquery, you may
need to alias one of them so Access knows which one you mean.

If that's not the issue, post your SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Thanks this worked wonderfully, however the powers that be now want to
be
able to set a date selection.
I have tried to include a date field [Analysis Date]in the selection
from
the table [items], but this returns all the records for the completed
batch,
when I want to avoid duplication of the records within the batch.
I was not aware of subqueries and the article was worthwhile. Could you
please give me a nudge in the right direction for the above problem
because
the big thrill (sad I know) is actually getting to the solution
yourself.

thanks

Richard

:

Use a subquery to check for any uncompleted Items.

Assuming you have:
- a table of Batches with a BatchID primary key
- a table of Items, with:
* a BatchID foreign key
* a Result field (blank until done)
then the query might look like this:

SELECT Batches.*
FROM Batches
WHERE NOT EXISTS (
SELECT BatchID FROM Items
WHERE Items.BatchID = Batches.BatchID
AND Items.Result Is Null)

If subqueries are new, here's a starting point:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Need to select records where a batch is completed
Batch is made up of "Items" which are complete when field [Result]
has
a
value in it.
I need my query to select batch numbers for which all "Items" are
completed
ignoring any part completed batches.

Any help greatly appreciated

Richard

 

[richard]

I don't need the date and just unchecking the show box has worked. I really
do feel silly as I tried that yesterday before you came up with the DISTINCT
answer.

Many thanks for your help the brownie points at work will grow

Richard

Do you need the date? Or could you just uncheck the Show box under the date
field?

If you do want the date, which date when there are multiples?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SQL statement as requested. The DISTINCT worked for the deduplicating the
batches, however if a batch has records spanning more than one day then
the
query returns a record for each batchnumber and date. Hope this is
clearer.


SELECT DISTINCT [Batch Details].[Batch Number], [Sample
Details].[Analysis
Date]
FROM [Batch Details] INNER JOIN [Sample Details] ON [Batch Details].[Batch
Number] = [Sample Details].[Batch Number]
WHERE ((([Sample Details].[Analysis Date]) Between [Start Date] And [End
Date]) AND ((Exists (SELECT[Batch Number] FROM [Sample Details] WHERE
[Sample
Details].[Batch Number] = [Batch Details].[Batch Number] AND [Sample
Details].Result Is Null))=False))
ORDER BY [Batch Details].[Batch Number];

Not sure I understand the issue.

If you just want to deduplicate the results you may be able to do that by
adding DISTINCT to the query (immediately after SELECT.)

If you have the same table in the main query and in the subquery, you may
need to alias one of them so Access knows which one you mean.

If that's not the issue, post your SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Thanks this worked wonderfully, however the powers that be now want to
be
able to set a date selection.
I have tried to include a date field [Analysis Date]in the selection
from
the table [items], but this returns all the records for the completed
batch,
when I want to avoid duplication of the records within the batch.
I was not aware of subqueries and the article was worthwhile. Could you
please give me a nudge in the right direction for the above problem
because
the big thrill (sad I know) is actually getting to the solution
yourself.

thanks

Richard

:

Use a subquery to check for any uncompleted Items.

Assuming you have:
- a table of Batches with a BatchID primary key
- a table of Items, with:
* a BatchID foreign key
* a Result field (blank until done)
then the query might look like this:

SELECT Batches.*
FROM Batches
WHERE NOT EXISTS (
SELECT BatchID FROM Items
WHERE Items.BatchID = Batches.BatchID
AND Items.Result Is Null)

If subqueries are new, here's a starting point:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Need to select records where a batch is completed
Batch is made up of "Items" which are complete when field [Result]
has
a
value in it.
I need my query to select batch numbers for which all "Items" are
completed
ignoring any part completed batches.

Any help greatly appreciated

Richard