I'm getting in a real muddle...

[Lee]

Hi everyone,
Can anyone advise me on how I can do the following (by
using Design View rather than SQL):

I'm reporting something to the customer when a record is
archived. But, I don't want the report to include any
records where:
a) there are identical values in three of the 8 fields
AND
b) where at least ONE or these 'identical' records has
already been archived.
So if there are three 'identical' records that are not
yet archived, I DO want the report to include the one
that I'm currently archiving.
Say I'm about to archive a record and there's a similar
record already archived: How can I design the query so
that it does not include either record. (I can obviously
tell the query to ignore all records with an Archive flag
of True but that leaves the one that's currently False
which I don't want to include in the report!) I can't
seem to work out how to do this - any ideas?
I do hope you can help.
TIA
Lee

 

[John Vinson]

Hi everyone,
Can anyone advise me on how I can do the following (by
using Design View rather than SQL):

I can't imagine any way to do so.

Don't be scared of SQL. The Design View is NOT A QUERY - design view
is just a friendly way to build SQL, which *is* the real Query. In
fact you can learn to understand SQL by building a query in the grid
and toggling back and forth to SQL view. Some queries - such as the
one you request - are simply too complicated to be built directly in
the grid without (as I suggest below) typing in at least some SQL.

I'm reporting something to the customer when a record is
archived. But, I don't want the report to include any
records where:
a) there are identical values in three of the 8 fields
AND
b) where at least ONE or these 'identical' records has
already been archived.

You'll need an EXISTS subquery *in* a Subquery in your query. This
cannot be done in the grid, or only partially.

If you'ld care to post the structure of your table (at least the
Primary Key, how you flag records as archived (a Yes/No field named
[Archive] I gather?) and what three fields constitued a duplicate,
someone can probably help with the SQL - but you won't be able to do
this without SQL, it's just too complex.

 

[Lee]

Hello again John,
Thanks for your reply to this. I did post further info
under a new Post recently but haven't as yet got a
reply. I have infact now worked out how to do this (via
Design View) as I don't know enough SQL to write it
myself. I've ended up with four different queries and
two reports that a macro selects according to whether
there are any duplicates.........all rather
unconventional but it works so I'm feeling rather pleased
with myself now!!

Kind regards,

Lee

-----Original Message-----

Hi everyone,
Can anyone advise me on how I can do the following (by
using Design View rather than SQL):

I can't imagine any way to do so.

Don't be scared of SQL. The Design View is NOT A QUERY - design view
is just a friendly way to build SQL, which *is* the real Query. In
fact you can learn to understand SQL by building a query in the grid
and toggling back and forth to SQL view. Some queries - such as the
one you request - are simply too complicated to be built directly in
the grid without (as I suggest below) typing in at least some SQL.

I'm reporting something to the customer when a record is
archived. But, I don't want the report to include any
records where:
a) there are identical values in three of the 8 fields
AND
b) where at least ONE or these 'identical' records has
already been archived.

You'll need an EXISTS subquery *in* a Subquery in your query. This
cannot be done in the grid, or only partially.

If you'ld care to post the structure of your table (at least the
Primary Key, how you flag records as archived (a Yes/No field named
[Archive] I gather?) and what three fields constitued a duplicate,
someone can probably help with the SQL - but you won't be able to do
this without SQL, it's just too complex.



.

 

[John Vinson]

Hello again John,
Thanks for your reply to this. I did post further info
under a new Post recently but haven't as yet got a
reply. I have infact now worked out how to do this (via
Design View) as I don't know enough SQL to write it
myself. I've ended up with four different queries and
two reports that a macro selects according to whether
there are any duplicates.........all rather
unconventional but it works so I'm feeling rather pleased
with myself now!!

Good! I printed out your other message and spent a while late last
night struggling with it - not easy! I'd like to come up with a
pure-query solution (and I'm sure it can be done), but since you have
something working I'll just do it as an intellectual exercise.

 

[Lee]

Oh dear, sorry you've spent so long on this. If you
should get the better of it I'd be pleased to see what
you come up with. My email address is below (minus the
No Spam bit of course).

Kind Regards,

Lee