SELECT DISTINCTROW query showing double names

#11

On 19 okt, 22:54, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:

Quote:

Co wrote:
On 19 okt, 22:19, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
Co 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

As I said in my initial reply, you have to aggregate the order
information. 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.

datum_aanvraag I guess is not summable. It is the date the order was
placed.

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

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

You didn't tell us what you wanted the query to return. Do you wnat it to
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

I just want the query to return all the records from Tabel_Aanvragen
(once).
But because I use conditional formatting I need the fields date_order
and approved from the table VISA.
Because when the date_order < date()-12 and approved = False I want
the row (in the subform) to turn red.

Marco

#12

Co wrote:

Quote:

I just want the query to return all the records from Tabel_Aanvragen
(once).

So why is VISA involved in the query if you don't want the query to return
any information from VISA?

Quote:

But because I use conditional formatting I need the fields date_order
and approved from the table VISA.

Show us a single record for a person containing that information from your
sample data provided. Do you see the problem yet? You have to decide WHICH
order's data to return.

Quote:

Because when the date_order < date()-12 and approved = False I want
the row (in the subform) to turn red.

Well, then you are going about it wrong. You need a master-detail form with
the person data in the form and the order data in the subform. The Form
Wizard should walk you through the steps for doing this ...

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SELECT DISTINCTROW query showing double names

comp.databases.ms-access comp.databases.ms-access

Re: SELECT DISTINCTROW query showing double names - 10-19-2010 , 04:04 PM

Re: SELECT DISTINCTROW query showing double names - 10-19-2010 , 04:11 PM