Hi All,

I have two tables, one with person records (Tabel_Aanvragen) and one
with orders (VISA).
I want to show all fields from Tabel_Aanvragen and datum_aanvraag from
VISA.

I use this query: SELECT DISTINCTROW VISA.datum_aanvraag,
Tabel_Aanvragen.* FROM Tabel_Aanvragen
INNER JOIN VISA ON Tabel_Aanvragen = VISA.IDea

It shows me some of the names twice since they have two orders.
How can I change this so all the persons are just shown once.

Regards
Marco

Co wrote:
fields from some, but not all, of the tables used in the query". I'm not
sure which side of this join is the orders table (VISA?) but to only show
each name onece, you need to include only fields from the "one" side of the
join in your SELECT clause in order for DISTINCTROW to be of any use. This
is the reason DISTINCTROW is seldom used. You will typically see DISTINCT
used instead.

In this case, however, even DISTINCT is of no use because it compares all
the fields in each row to determine if the rows are distinct. If even one
field contains different data, the row is considered unique.

What you have to do is GROUP BY the "name" field, whatever it is, and use
aggregate functions on the rest of the fields in your select statement.
Aggregate functions are SUM, AVG, FIRST, LAST,COUNT. Since I don't know the
names of the fields in your tables I cannot get any more specific than this.
The idea is to group by the field or fields that needs to be unique in each
row of your resultset, and aggregate the rest of the fields.

"Co" <vonclausowitz (AT) gmail (DOT) com> wrote

You will need to replace DISTINCTROW with DISTINCT which only looks at
selected columns. Then only select columns that will be distinct, such as
person's name and ID. You are getting multiples because you are also
outputting order information.

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote in
news:i96odc$8uu$1 (AT) news (DOT) eternal-september.org:

Er, what? DISTINCTROW, despite its resemblence to DISTINCT is very,
very different. It is designed to return an editable recordset,
which DISTINCT can never do. Indeed, that's its only purpose, i.e.,
to tell Jet to figure out what unique records there are on each side
of the join, and allow all fields to be updated.

Until Access 95, SELECT DISTINCTROW was the default for the QBE.

DISTINCTROW really has nothing to do with aggregate/Group By
queries, since those are not editable and never can be. While you
are able to use DISTINCTROW in with GROUP BY, it has no effect
whatsoever, so far as I can tell.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

David-W-Fenton wrote:
I should have said "You will typically see DISTINCT used instead of
DISTINCTROW when the desire is to get distinct rows", which is what I
assumed the OP's goal was.

A95 was the first version I used :-)

Uh, I do know that ...

I'm not sure where I advised the OP to use both DISTINCTROW and GROUP BY,
and neither do I see where the OP specified that he needed an editable
recordset. I'm pretty sure he had no idea about the real purpose of
DISTINCTROW and was simply using it because he thought it would return
distinct rows as the name implies.

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote in
news:i97s5e$jnv$1 (AT) news (DOT) eternal-september.org:

I didn't suggest that you advised the OP to use it.

I, for one, have always found the query properties to be really
obtuse, with UNIQUE RECORDS vs. UNIQUE VALUES being completely
opaque to me as to what they mean. If they'd just change those to
DISTINCTROW and DISTINCT it would be a helluva lot more clear.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 16 okt, 03:46, "David-W-Fenton" <NoEm... (AT) SeeSignature (DOT) invalid>
wrote:
I have been trying the GROUP BY function but all I get are errors.
Maybe someone can help me out with this query.

I use this query: SELECT VISA.datum_aanvraag,
Tabel_Aanvragen.* FROM Tabel_Aanvragen
INNER JOIN VISA ON Tabel_Aanvragen = VISA.IDea

So my personal records are in Tabel_Aanvragen and my orders
are in VISA. I want to display every person only once.
Even if he has more orders.

Tabel_Aanvragen fields are: ID, voornaam, achternaam, dob

Marco












+

Co wrote:
You cannot show one record for each person without doing something to
condense (aggregate) the orders for that person into a single record.
As a guess:

SELECT ID, voornaam, achternaam, dob
,SUM(datum_aanvraag) as total_datum_aanvraag
FROM Tabel_Aanvragen
INNER JOIN VISA ON Tabel_Aanvragen = VISA.IDea
GROUP BY ID, voornaam, achternaam, dob

If datum_aanvraag is not summable, this will throw an error, but hopefully
it gives you the idea. If not, show us the data for one person (personal and
order data - just a couple order records), then show us what you want your
query to return in a single record for that person and why.

On 19 okt, 22:19, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
datum_aanvraag I guess is not summable. It is the date the order was
placed.

A record example would be (I changed the field names into English:

Tabel_Aanvragen
ID (autonum) 3
lastname (text) Johnson
firstname (text) Peter
dob (date) 12-10-1971
passport (text) NJ56F6DA

VISA
IDea (number) 3
date_order (text) 2-10-2010
approved (yes/no) False

VISA
IDea (number) 3
date_order (text) 3-1-2007
approved (yes/no) True

Marco

Co wrote:
Well then, it's not summable of course. You can get the MIN, MAX, FIRST or
LAST.

return the earliest order date? Or the last?
Construct a single record from this data to illustrate what you want the
query to return for this ID.

I just noticed an error in the join that has perpetuated from your first
post:

ON Tabel_Aanvragen = VISA.IDea

should be:
ON Tabel_Aanvragen.ID = VISA.IDea