Query bringing back multiple rows not just one

[jeffwest2]

I have the attached code in a query.

It should be bringing back just one row for each record, however, if I have anything in any column other than Call_NUmber_int it brings back multiples if that record, I can't seem to get this right, any chance anyone can see what I have wrong in this. :banghead:

 

[plog]

It should be bringing back just one row for each record

Not to be pedandtic, but a row is a record, so I fail to see the issue. How about you provide a sample data showing what you mean. Show us what is showing and then what should be shown.

My guess is this is a case of where your definition of 'unique' isn't the same as Access'.

 

[jeffwest2]

Attached is what I am seeing

The second column is the unique identifier, but if you look I have multiples, with the last column being the one that is pulling back too much data

 

[Old Man Devin]

The easiest solution is to just not include that last column in the query at all. Then you need to set the GROUP BY on all of the other fields so for sets of records with the info all being the same, it only displays it once. Should do the trick, unless you need that last column in some way, in which case I don't think it will be possible to have one record per person, which is what I assume you're doing here, although things like that are often possible using lots of queries linked to each other so all hope is not lost.

 

[jeffwest2]

Unfortunately I do need to bring back the last column, so I guess I will need to try and do a second query to bring this back

 

[Old Man Devin]

Unfortunately I do need to bring back the last column, so I guess I will need to try and do a second query to bring this back

It will be a struggle, as to show the multiple records from the last column, you in principle have to show multiple records for the rest of it, just so it has somewhere to show them!

Possibly you could make a field which combines all the result of that result column for each person using concatenation, so that there really is only one record that contains all the information from that last column for each person.

What exactly is this query going to be used for? Is it a recordset?

 

[jeffwest2]

If I try and explain what this is being used for it might help.

This logs calls to people, they have three routes they can go down, initial, 2A and 2B, each has up to three calls, but different outcome criteria.

The system reads the outcome code (from a contact table) and should turn that into the full description, the result from the query is passed into a list box as a recordset.

 

[plog]

Turns out I was correct about you and Access disagreeing about what 'unique' means. I have to agree with Access, the sample data you posted is indeed unique: there are no duplicate rows of data.

Based on that sample data you posted, what would you like the results to be?

 

[jeffwest2]

Ideally something like this.

 

[jeffwest2]

ok managed to solve this in a really long winded way but it works

See code attached