Query criteria I think

[hnorgaar]

In a table I have several records with duplicates based on 1 field. Here is examples. Thx for any input.
I was thinking to have a translation table, but so far Im having troubles with empty fields or null maybe

Record 1 "With sugar"
Record 1 (Duplicate) Empty or "Not with sugar"
Record 2 "With jam"
Record 2 (Duplicate) Empty
Record 3 "With sugar"
Record 3 (Duplicate) "With jam"

My result in a query should be identical for both records to reduce records by using unique values

Record 1 "With and without sugar"
Record 2 "With and without jam"


Record 3 "With sugar and jam"

[June7]

Maybe this is relevant http://allenbrowne.com/func-concat.html

[Gicu]

Are the records exactly as you show them here? Meaning do they always start with "With" (or "Not with") and have one extra word which is the attribute?
You can easily deal with nulls using the Nz function Record 2 Nz([YourField],"Without"). Then you would use one of the available concatenating function (recently I like theDBguy's SimpleCSV http://www.accessmvp.com/thedbguy/co...itle=simplecsv) to merge them into one record, with probably some more string manipulations needed.

Cheers,
Vlad

[hnorgaar]

No they are not, just eamples, but the issue is to find records where only 1 field in the records differ with text empty or a list of 6-7 different texts, and then change the text to reduce records in the query. In excel I made a point system to find them, using empty as 1 and different texts 10, 105, 1005 and so on, and summed them up, giving all with 106 would be "With and without something", as that will equal 105 + 1, empty plus a text "something" with lookup and if sum is 105, that will be original text

[Gicu]

So use the SimpleCSV along with Nz() to get a list in one record (you can use " and " as the delimiter).

Cheers,

[hnorgaar]

Dont think I can, I have attached a spreadsheet with the texts im looking for and the point system im using, and row A is the original texts I need to seperate in a query WHEN they are duplicates except for the row these texts are in, and the texts in row E is the new combined text I will like to replace both duplicates with. Its a base for car spare parts and to avoid the custemer to see the same article 2 times because it fits the same car with manual and automatic gear, whereas other articles from same group doesnt

Sorry the text is in DK

[Gicu]

Have you tried to create a totals query grouping the the record id and summing using your same point system. Then create a lookup table with your final texts (column E) and link that to the sum field. It would help if you can upload a sample db with the actual table, not an Excel file.

Cheers,

[hnorgaar]

Yes I did but I will try again as I gave up first time and maybe with NZ I can get it to work. If not I will return with new questions from that base. Thx