[Challenge] Query with totals obscuring duplicates

[Jackroland]

Hello everyone, I would like to offer you a "challenge", I have a database where I have a main table in which I have placed


-ID
-First name
-Street
-City
-Image of the Persone


I created a Query to then put as source data in a sub-form, but there is a problem.
I wanted all the names present in New York +Image + the ID and the city itself to be displayed in the sub-form (I don't care about the street being displayed). But I have two people with the same name living in the same city, and therefore I get the same name twice. So I thought about putting a total in the query, the problem is that the total also involves the ID, and consequently it displays the same name twice. Explaining it with an example


exist
-Jhon with ID = 1 in New York at street x + mage.
-Jhon with ID = 5 in New York at street y + Image.
and in the subform I would like the same names not to be repeated. and so in this case I would like to display only a Jhon with ID and City.

[Micron]

Then I think you will have to leave the ID out of the query and set unique values to Yes. ID values (usually autonumbers) have no use to the user so there's not much point in displaying or using them.

[Jackroland]

Quindi penso che dovrai lasciare l'ID fuori dalla query e impostare valori univoci su Sì. I valori ID (di solito i numeri automatici) non servono all'utente, quindi non ha molto senso visualizzarli o utilizzarli.

I could also leave out the ID but not the image of the person, and this in the sub mask view makes me stay double Jhon even though I put a total in the query.

[isladogs]

Clearly there is a language issue due to translation but when you say 'sub mask' are you referring to a subquery or a subform?

[Jackroland]

Chiaramente c'è un problema di lingua dovuto alla traduzione, ma quando dici 'sottomaschera' ti riferisci a una sottoquery o a una sottomaschera?

sorry, I mean subForm

[Micron]

Not much of that is making sense. If you have 2 Richards living in New York at 2 different addresses and you have 2 different images then they are 2 different people, so why would you want to show only one image and ID?? And which ID and image would you pick?

[Jackroland]

Non ha molto senso. Se hai 2 Richard che vivono a New York in 2 indirizzi diversi e hai 2 immagini diverse, allora sono 2 persone diverse, quindi perché dovresti mostrare solo un'immagine e un ID? E quale ID e immagine sceglieresti?

i know it doesn't make much sense, but i'm interested in showing only one john that is accompanied by an image in case there are two two jhon displaying only one. I just wanted to know if it is technically possible to do this, so I put a challenge in the title

[CJ_London]

based on your requirement

I would like to display only a Jhon with ID and City.

have a rule as to which ID you want to use based on using one of first/last/min/max options for ID - something like

SELECT first(ID) as fID, Firstname, City
FROM myTable
GROUP BY Firstname, City

but then you said

but i'm interested in showing only one john that is accompanied by an image in case there are two two jhon displaying only one

so for this you use a separate query to get the entire record

SELECT A.ID, A.Firstname, A.image, A.City
FROM myTable A INNER JOIN (SELECT first(ID) AS FirstID FROM myTable GROUP BY firstname, city)  B ON A.ID=B.firstID

I just wanted to know if it is technically possible to do this

yes, very straightforward and the solution principle is a common requirement.

[Jackroland]

based on your requirement

have a rule as to which ID you want to use based on using one of first/last/min/max options for ID - something like

SELECT first(ID) as fID, Firstname, City
FROM myTable
GROUP BY Firstname, City

but then you said



so for this you use a separate query to get the entire record

SELECT A.ID, A.Firstname, A.image, A.City
FROM myTable A INNER JOIN (SELECT first(ID) AS FirstID FROM myTable GROUP BY firstname, city)  B ON A.ID=B.firstID

yes, very straightforward and the solution principle is a common requirement.

I tried what you told me, but it keeps showing me 2 Jhon, I think the problem is the image, which doesn't make me group correctly to have only one Jhon. I leave you the code that I put below, tell me if I am wrong something.

SELECT [Tab A].Firstname, [Tab A].Image, First([query 2].ID) AS FirstiID
FROM [Tab A] INNER JOIN [query 2] ON [Tab A].ID= [query 2].ID
GROUP BY [Tab A].Firstname;

the street removed it from the final view.

[CJ_London]

tell me if I am wrong something.

you are wrong, your sql is nothing like mine - and you haven't provided the sql to query2

I think the problem is the image, which doesn't make me group correctly to have only one Jhon

that is my second example - and there is no grouping on the image

[Jackroland]

you are wrong, your sql is nothing like mine - and you haven't provided the sql to query2

that is my second example - and there is no grouping on the image

You're right, sorry if I didn't follow your code well, but I didn't understand it, now I understand it well and it works! in the end this just served me as an exercise to better understand some dynamics. Thanks Ajax