Duplicate query, (sort of)

[cdoyle]

I need to create a query for an error report.

What I need is a query that will find any duplicate records from field '1' in my table.

But then I want it to check another field '2' in the same table for any records that meet criteria 'A' but also have entries with a different critiera.


Not sure if I'm explaining that right.

So say we have a table like this

field 1..........field 2
XX.................A (the criteria we want)
XX.................C (oops, somone entered another entry with a different criteria)


So it would tell us that the 'code' in field 1, we have the criteria A and C at the same time. not good.

I hope that makes sense.

 

[ajetrumpet]

Doyle,

Create this using the query wizard. There is an option in it for querying duplicate records. From your description, it sounds as though there should only be ONE record for each "field1" value. The wizard will take care of this.

Wizard Options
**Fields containing duplicates = field 1
**Other Fields to List in Resultset = field 2, any others you want.

 

[cdoyle]

Ya I've been trying the wizard but it's not quite doing what I need.

Normally Field 1 can have duplicates that's OK, but not if field '2' has criteria 'A' selected in any of the records.

Once 'A' is selected I need to know of any other matching records from field '1' that have different criteria in field '2'. So if someone created a record for code xx, and selects criteria 'b' for field 2.

If the next person creates a record for code 'xx' and select criteria 'C' this is OK, nothing shoul appear in the query.

Then later on, someone created another record for code 'xx' but this time they select 'a' from field 2.

My query need to inform me of these conflicting entries.

 

[ajetrumpet]

I read this...

Normally Field 1 can have duplicates that's OK, but not if field '2' has criteria 'A' selected in any of the records.

Like this...

Field 1 can have duplicates, that's OK, but not if the COMBINATION of values in Field 1 & Field 2 ends up being duplicated from a previous record entry.

Is that another way of saying it Doyle??

Once 'A' is selected I need to know of any other matching records from field '1' that have different criteria in field '2'. My query need to inform me of these conflicting entries.

Are you looking for a warning message when a duplicate record on Field 1 and Field 2 (combined) is attempted to be entered??

 

[cdoyle]

Yes I think you have it.
Basically once 'A' is selected in field '2' we shouldn't have any other records from field '1' (codes) with any other criteria.

This query will be used on a report, to let us know of these conflicts so we can decide which ones need to be ended.

 

[ajetrumpet]

OK,

so the only way you can get these records ONLY in the query is to know what Criteria 'A' is...do you??

 

[cdoyle]

Yes, I was just saying 'A' to try and keep it simple in describing it.

'Error' would be criteria 'A'

 

[ajetrumpet]

query...

SELECT field 1, field 2
FROM table
GROUP BY field 1, field 2
HAVING COUNT(*) > 1 AND field 2 = "error";

 

[cdoyle]

query...

SELECT field 1, field 2
FROM table
GROUP BY field 1, field 2
HAVING COUNT(*) > 1 AND field 2 = "error";

Do I need anything else besides that?
where clause???

I'm getting this error

The LEVEL clause includes a reserved word or argument that is mispelled or or missing.

 

[ajetrumpet]

HAVING = WHERE when working with aggregate function (such as COUNT).

See my FAQ for details on this...
http://www.access-programmers.co.uk/forums/showthread.php?t=135763

What are your field names??

 

[cdoyle]

ah OK, just wanted to make sure I was suppose to add anything.
Table Name tbl_main
field names are CodeID and Edit_Type

 

[ajetrumpet]

Post the entire SQL string

 

[cdoyle]

SELECT T_tbl_main.Code_Info_ID, T_tbl_main.Edit_Type
FROM T_tbl_main;
GROUP BY Code_Info_ID, Edit_Type
HAVING COUNT(*) > 1 AND Edit_Type= "error";

 

[ajetrumpet]

SELECT T_tbl_main.Code_Info_ID, T_tbl_main.Edit_Type
FROM T_tbl_main;<--- Access reads the semicolon as "end of statement". I am guessing this is the reason for the "Level" error.
GROUP BY Code_Info_ID, Edit_Type
HAVING COUNT(*) > 1 AND Edit_Type= "error";

I've never heard of that error before, weird...

 

[ajetrumpet]

Did it work then??

 

[cdoyle]

right after I posted my SQL I noticed the extra ; Access must have put that in on it's own.

I removed it, but now I get a data mismatch error in criteria expression

 

[ajetrumpet]

I'm pretty sure "error" is a reserved word in Access, which means "don't" use it anywhere. I don't think it would do that here, because it's a value, but not sure.

Got a file to post??

There might be a tiny little nuisance somewhere else you are overlooking, but I have no idea what it could be without looking...

 

[cdoyle]

Will this work on a number field?

I forgot we changed the database and this field is now Edit_Type_ID
so it would be '6'

I tried your code out on a different 'text' field, and it worked. But it won't work on this number field.

 

[ajetrumpet]

Well, that's the problem then.

So, on the change of the database, "error" = 6??

If so, drop the " " marks around "6", because with them, you are specifying 6 as a text string, and it is a number.

6 IS a STRING, but NOT a TEXT STRING.

 

[cdoyle]

Thank You.
Got it working but it's not quite doing what i need to do.

I have several entries in the table for code 'XX', and each has an edit_type_id #.
I added a new record and gave it the entry type '6'. When I run the query it's only displaying the entry with the 6, and ignoring the rest.

It needs to list out all the codes for xx so we can decide which ones need to be ended.