Design help wanted

[kirkm]

I want to extract all records from a table where 5 columns contain identical values.

The 5 values can/do change but it's groups of identical ones I want.

What would be the best approach ? Ideally I'd process each "batch": in turn.

Many thanks for any advice.

 

[plog]

You gave a good low level explanation of what you want. Now go up a level or two and also back a step in the process:

1. Why? What's the end goal for this data?

2. It sounds like this is going to be recurring--how do you get the data? External source? Frequency?

3. Give a big picture explanation of what you are doing.

 

[kirkm]

Hi plog, I must be on a different wavelength to you, but after a bit of Googling and experimenting I have a solution

SELECT tblMain4.[Field1], tblMain4.[Field2], tblMain4.[Field3], tblMain4.[Field4], tblMain4.[Field5], Sum(1) AS CNT
FROM tblMain4
WHERE (((tblMain4.Year)='1955'))GROUP BY tblMain4.[Field1], tblMain4.[Field2], tblMain4.[Field3], tblMain4.[Field4], tblMain4.[Field5] HAVING (((Sum(1))>1));

I had not used (or know of) things like CNT or HaVING but it does what's required.

 

[Galaxiom]

Hi plog, I must be on a different wavelength to you, but after a bit of Googling and experimenting I have a solution

I had not used (or know of) things like CNT or HaVING but it does what's required.

It isn't a solution when you have no idea what the code you are using does.

 

[kirkm]

> It isn't a solution when you have no idea what the code you are using does.

Right, possibly. But I can see what it does, and that's what is required.

 

[Galaxiom]

No, you can see the results with the data you have but, from your comments, you clearly have no idea.

 

[Galaxiom]

I can see what you are doing now. Your original question "where all five columns contain identical values" is ambiguous. I took this to mean that you wanted records where all the field values in the record were the same.

Note that the expression Sum(1) is quite unconventional. Normally Count(*) would be used to get the same result more efficiently.

CNT is just an alias name for the Sum field. Just about any string could have been used instead.