Sort out one address

  • Thread starter Thread starter Tom R.
  • Start date Start date
T

Tom R.

Hello!

Is there a way to sort out just one address so that I don't send the same
info several times to one household. I have many people in a familiy and they
have of course the same address, but I only want to send them my info once.

Thank you!
 
Hi Tom,
the post doesn't say how your table is set up or how much experience you
have with queries, so I am just guessing on how much info and explanation to
include.
Do you have your names and addresses in a query?
If you create a new query that concatenates the street no, the street name
and city into a single field, you could set Unique Values to Yes on the
query's property sheet. That would give you a list of all addresses needed
without duplicates.

Jeanette Cunningham
 
Hey.

I have made a query to make address labels from and i have an address field
in it.
But now when I run the query, I get all the people on an address. I just
want one person from each address.
Is there also possible to sort it out so I get's the oldest person in that
house hold to?
Have a field called [Fødselsdato] that holds the persons birthdate.
 
Tom,
sounds like you need a sub query.
Here is an example

SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress
WHERE (((tblAddress.BirthDate)=(SELECT Max(tblAddress.BirthDate) AS
MaxOfBirthDate
FROM tblAddress)) ;

to explain a bit:
assume your main query is the following:
SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress

the sub query is
(SELECT Max(tblAddress.BirthDate) AS MaxOfBirthDate
FROM tblAddress))

in the main query in design view, type the subquery into the criteria row of
the main row in the field for BirthDate

Jeanette Cunningham


Tom R. said:
Hey.

I have made a query to make address labels from and i have an address
field
in it.
But now when I run the query, I get all the people on an address. I just
want one person from each address.
Is there also possible to sort it out so I get's the oldest person in that
house hold to?
Have a field called [Fødselsdato] that holds the persons birthdate.
--
Tom R. Thorstensen


Jeanette Cunningham said:
Hi Tom,
the post doesn't say how your table is set up or how much experience you
have with queries, so I am just guessing on how much info and explanation
to
include.
Do you have your names and addresses in a query?
If you create a new query that concatenates the street no, the street
name
and city into a single field, you could set Unique Values to Yes on the
query's property sheet. That would give you a list of all addresses
needed
without duplicates.

Jeanette Cunningham
Click to expand...
Click to expand...
 
I am nearly there, but when I used it I only got one person from database,
and that was the youngest person???

My table name is Tbl_Import_fra_Excel
Fields:
Fornavn (First name)
Etternavn (Last name)
Adresse (Address)
Postnr (Zip code)
Stedsnavn (Area?) from a table called Tbl_Poststed
Fødselsdato1 (Bithdate)

Here is my query:

SELECT Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn,
Tbl_Import_fra_Excel.Adresse, Tbl_Import_fra_Excel.Postnr,
Tbl_Poststed.Stedsnavn, Tbl_Import_fra_Excel.Fødselsdato1
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr
WHERE (((Tbl_Import_fra_Excel.Fødselsdato1)=(SELECT
Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)));

The Subquery in the field Fødselsdato1(Birthdate) says:

(SELECT Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)

Is this how I shoul do it, or am I doing something wrong.
--
Tom R. Thorstensen


Jeanette Cunningham said:
Tom,
sounds like you need a sub query.
Here is an example

SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress
WHERE (((tblAddress.BirthDate)=(SELECT Max(tblAddress.BirthDate) AS
MaxOfBirthDate
FROM tblAddress)) ;

to explain a bit:
assume your main query is the following:
SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress

the sub query is
(SELECT Max(tblAddress.BirthDate) AS MaxOfBirthDate
FROM tblAddress))

in the main query in design view, type the subquery into the criteria row of
the main row in the field for BirthDate

Jeanette Cunningham


Tom R. said:
Hey.

I have made a query to make address labels from and i have an address
field
in it.
But now when I run the query, I get all the people on an address. I just
want one person from each address.
Is there also possible to sort it out so I get's the oldest person in that
house hold to?
Have a field called [Fødselsdato] that holds the persons birthdate.
--
Tom R. Thorstensen


Jeanette Cunningham said:
Hi Tom,
the post doesn't say how your table is set up or how much experience you
have with queries, so I am just guessing on how much info and explanation
to
include.
Do you have your names and addresses in a query?
If you create a new query that concatenates the street no, the street
name
and city into a single field, you could set Unique Values to Yes on the
query's property sheet. That would give you a list of all addresses
needed
without duplicates.

Jeanette Cunningham


Hello!

Is there a way to sort out just one address so that I don't send the
same
info several times to one household. I have many people in a familiy
and
they
have of course the same address, but I only want to send them my info
once.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
I am nearly there, but when I used it I only got one person from database,
and that was the youngest person???
Click to expand...

Is there a field for the HouseholdID? If you need the oldest (*minimum*
birthdate, not maximum) within that household, modify Jeanette's query like

SELECT Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn,
Tbl_Import_fra_Excel.Adresse, Tbl_Import_fra_Excel.Postnr,
Tbl_Poststed.Stedsnavn, Tbl_Import_fra_Excel.Fødselsdato1
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr
WHERE (((Tbl_Import_fra_Excel.Fødselsdato1)=(SELECT
Min(Tbl_Import_fra_Excel.Fødselsdato1) AS MinOfFødselsdato1
FROM Tbl_Import_fra_excel AS X WHERE X.[HouseholdID] =
[Tbl_Import_fra_Excel.[HouseholdID])));

If there is no unique household ID you'll have to put criteria in the subquery
to match on address, postnr, and stedsnavn.

John W. Vinson [MVP]
 
Tom,
do this with 3 queries.

1st query
---------------------
SELECT [Addresse] & " " & [Postnr] & " " & [Stedsnavn] AS FullAddresse,
tbl_Import_fra_Excel.Fødselsdato1, tbl_Import_fra_Excel.Etternavn,
tbl_Import_fra_Excel.Fornavn
FROM tbl_Import_fra_Excel;
--------------------
Save the above query as qAddressA and we will use it in the next queries.

2nd query
-------------------
SELECT qAddressA.FullAddresse, Min(qAddressA.Fødselsdato1) AS
MinOfFødselsdato1
FROM [SELECT qAddressA.FullAddresse
FROM qAddressA
GROUP BY qAddressA.FullAddresse]. AS Q INNER JOIN qAddressA ON
Q.FullAddresse = qAddressA.FullAddresse
GROUP BY qAddressA.FullAddresse;
------------------
Save the above query as qAddressC

3rd query
-----------------
SELECT qAddressA.Etternavn, qAddressA.FullAddresse
FROM qAddressA INNER JOIN qAddressC ON (qAddressA.Fødselsdato1 =
qAddressC.MinOfFødselsdato1) AND (qAddressA.FullAddresse =
qAddressC.FullAddresse);
-----------------

Jeanette Cunningham

Tom R. said:
I am nearly there, but when I used it I only got one person from database,
and that was the youngest person???

My table name is Tbl_Import_fra_Excel
Fields:
Fornavn (First name)
Etternavn (Last name)
Adresse (Address)
Postnr (Zip code)
Stedsnavn (Area?) from a table called Tbl_Poststed
Fødselsdato1 (Bithdate)

Here is my query:

SELECT Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn,
Tbl_Import_fra_Excel.Adresse, Tbl_Import_fra_Excel.Postnr,
Tbl_Poststed.Stedsnavn, Tbl_Import_fra_Excel.Fødselsdato1
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr
WHERE (((Tbl_Import_fra_Excel.Fødselsdato1)=(SELECT
Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)));

The Subquery in the field Fødselsdato1(Birthdate) says:

(SELECT Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)

Is this how I shoul do it, or am I doing something wrong.
--
Tom R. Thorstensen


Jeanette Cunningham said:
Tom,
sounds like you need a sub query.
Here is an example

SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress
WHERE (((tblAddress.BirthDate)=(SELECT Max(tblAddress.BirthDate) AS
MaxOfBirthDate
FROM tblAddress)) ;

to explain a bit:
assume your main query is the following:
SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress

the sub query is
(SELECT Max(tblAddress.BirthDate) AS MaxOfBirthDate
FROM tblAddress))

in the main query in design view, type the subquery into the criteria row
of
the main row in the field for BirthDate

Jeanette Cunningham


Tom R. said:
Hey.

I have made a query to make address labels from and i have an address
field
in it.
But now when I run the query, I get all the people on an address. I
just
want one person from each address.
Is there also possible to sort it out so I get's the oldest person in
that
house hold to?
Have a field called [Fødselsdato] that holds the persons birthdate.
--
Tom R. Thorstensen


:

Hi Tom,
the post doesn't say how your table is set up or how much experience
you
have with queries, so I am just guessing on how much info and
explanation
to
include.
Do you have your names and addresses in a query?
If you create a new query that concatenates the street no, the street
name
and city into a single field, you could set Unique Values to Yes on
the
query's property sheet. That would give you a list of all addresses
needed
without duplicates.

Jeanette Cunningham


Hello!

Is there a way to sort out just one address so that I don't send the
same
info several times to one household. I have many people in a familiy
and
they
have of course the same address, but I only want to send them my
info
once.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Thank you Janette for all your helt.
I did some changes to your first query, and it worked:

1. (Added a comma , between Adresse and Postnr Stedsnavn)
SELECT [Adresse] & " , " & [Tbl_Import_fra_Excel.Postnr] & " " &
[Tbl_Poststed.Stedsnavn] AS FullAddresse, Tbl_Import_fra_Excel.Fødselsdato1,
Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr;

2.
SELECT qAddressA.FullAddresse, Min(qAddressA.Fødselsdato1) AS
MinOfFødselsdato1
FROM (SELECT qAddressA.FullAddresse
FROM qAddressA
GROUP BY qAddressA.FullAddresse) AS Q INNER JOIN qAddressA ON
Q.FullAddresse = qAddressA.FullAddresse
GROUP BY qAddressA.FullAddresse;

3.
SELECT qAddressA.Fornavn, qAddressA.Etternavn, qAddressA.FullAddresse
FROM qAddressA INNER JOIN qAddressC ON (qAddressA.Fødselsdato1 =
qAddressC.MinOfFødselsdato1) AND (qAddressA.FullAddresse =
qAddressC.FullAddresse);


Now my FullAddress field outputs:
Grethesvei 17, 3960 Stathelle

Is there now any way to split the FullAddresse field again?

Grethesvei 17 in one field
and 3960 in another field.

I seperated them with a comma ( , ) in the first SQL.
It is nessesary to do so when I shall print of Adress labels.

Thank you again!
--
Tom R. Thorstensen


Jeanette Cunningham said:
Tom,
do this with 3 queries.

1st query
---------------------
SELECT [Addresse] & " " & [Postnr] & " " & [Stedsnavn] AS FullAddresse,
tbl_Import_fra_Excel.Fødselsdato1, tbl_Import_fra_Excel.Etternavn,
tbl_Import_fra_Excel.Fornavn
FROM tbl_Import_fra_Excel;
--------------------
Save the above query as qAddressA and we will use it in the next queries.

2nd query
-------------------
SELECT qAddressA.FullAddresse, Min(qAddressA.Fødselsdato1) AS
MinOfFødselsdato1
FROM [SELECT qAddressA.FullAddresse
FROM qAddressA
GROUP BY qAddressA.FullAddresse]. AS Q INNER JOIN qAddressA ON
Q.FullAddresse = qAddressA.FullAddresse
GROUP BY qAddressA.FullAddresse;
------------------
Save the above query as qAddressC

3rd query
-----------------
SELECT qAddressA.Etternavn, qAddressA.FullAddresse
FROM qAddressA INNER JOIN qAddressC ON (qAddressA.Fødselsdato1 =
qAddressC.MinOfFødselsdato1) AND (qAddressA.FullAddresse =
qAddressC.FullAddresse);
-----------------

Jeanette Cunningham

Tom R. said:
I am nearly there, but when I used it I only got one person from database,
and that was the youngest person???

My table name is Tbl_Import_fra_Excel
Fields:
Fornavn (First name)
Etternavn (Last name)
Adresse (Address)
Postnr (Zip code)
Stedsnavn (Area?) from a table called Tbl_Poststed
Fødselsdato1 (Bithdate)

Here is my query:

SELECT Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn,
Tbl_Import_fra_Excel.Adresse, Tbl_Import_fra_Excel.Postnr,
Tbl_Poststed.Stedsnavn, Tbl_Import_fra_Excel.Fødselsdato1
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr
WHERE (((Tbl_Import_fra_Excel.Fødselsdato1)=(SELECT
Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)));

The Subquery in the field Fødselsdato1(Birthdate) says:

(SELECT Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)

Is this how I shoul do it, or am I doing something wrong.
--
Tom R. Thorstensen


Jeanette Cunningham said:
Tom,
sounds like you need a sub query.
Here is an example

SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress
WHERE (((tblAddress.BirthDate)=(SELECT Max(tblAddress.BirthDate) AS
MaxOfBirthDate
FROM tblAddress)) ;

to explain a bit:
assume your main query is the following:
SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress

the sub query is
(SELECT Max(tblAddress.BirthDate) AS MaxOfBirthDate
FROM tblAddress))

in the main query in design view, type the subquery into the criteria row
of
the main row in the field for BirthDate

Jeanette Cunningham


Hey.

I have made a query to make address labels from and i have an address
field
in it.
But now when I run the query, I get all the people on an address. I
just
want one person from each address.
Is there also possible to sort it out so I get's the oldest person in
that
house hold to?
Have a field called [Fødselsdato] that holds the persons birthdate.
--
Tom R. Thorstensen


:

Hi Tom,
the post doesn't say how your table is set up or how much experience
you
have with queries, so I am just guessing on how much info and
explanation
to
include.
Do you have your names and addresses in a query?
If you create a new query that concatenates the street no, the street
name
and city into a single field, you could set Unique Values to Yes on
the
query's property sheet. That would give you a list of all addresses
needed
without duplicates.

Jeanette Cunningham


Hello!

Is there a way to sort out just one address so that I don't send the
same
info several times to one household. I have many people in a familiy
and
they
have of course the same address, but I only want to send them my
info
once.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Thank you for the tips. I did som changes to the query, and it works just
fine now.

1. (Added a comma , between Adresse and Postnr Stedsnavn)
SELECT [Adresse] & " , " & [Tbl_Import_fra_Excel.Postnr] & " " &
[Tbl_Poststed.Stedsnavn] AS FullAddresse, Tbl_Import_fra_Excel.Fødselsdato1,
Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr;

2.
SELECT qAddressA.FullAddresse, Min(qAddressA.Fødselsdato1) AS
MinOfFødselsdato1
FROM (SELECT qAddressA.FullAddresse
FROM qAddressA
GROUP BY qAddressA.FullAddresse) AS Q INNER JOIN qAddressA ON
Q.FullAddresse = qAddressA.FullAddresse
GROUP BY qAddressA.FullAddresse;

3.
SELECT qAddressA.Fornavn, qAddressA.Etternavn, qAddressA.FullAddresse
FROM qAddressA INNER JOIN qAddressC ON (qAddressA.Fødselsdato1 =
qAddressC.MinOfFødselsdato1) AND (qAddressA.FullAddresse =
qAddressC.FullAddresse);

BUT!
Now my FullAddress field outputs:
Grethesvei 17, 3960 Stathelle

Is there any way to split the FullAddresse field again?

Grethesvei 17 in one field
and 3960 in another field.

I seperated them with a comma ( , ) in the first SQL.
It is nessesary to split them because I shall print of Address labels.

--
Tom R. Thorstensen


John W. Vinson said:
I am nearly there, but when I used it I only got one person from database,
and that was the youngest person???
Click to expand...

Is there a field for the HouseholdID? If you need the oldest (*minimum*
birthdate, not maximum) within that household, modify Jeanette's query like

SELECT Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn,
Tbl_Import_fra_Excel.Adresse, Tbl_Import_fra_Excel.Postnr,
Tbl_Poststed.Stedsnavn, Tbl_Import_fra_Excel.Fødselsdato1
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr
WHERE (((Tbl_Import_fra_Excel.Fødselsdato1)=(SELECT
Min(Tbl_Import_fra_Excel.Fødselsdato1) AS MinOfFødselsdato1
FROM Tbl_Import_fra_excel AS X WHERE X.[HouseholdID] =
[Tbl_Import_fra_Excel.[HouseholdID])));

If there is no unique household ID you'll have to put criteria in the subquery
to match on address, postnr, and stedsnavn.

John W. Vinson [MVP]
Click to expand...
 
This gives you the 2 address fields as you need for your mailing labels.

1st query
--------------
SELECT tbl_Import_fra_Excel.Fornavn, tbl_Import_fra_Excel.Etternavn,
tbl_Import_fra_Excel.Addresse, [tbl_Import_fra_Excel].[Postnr] & " " &
[Stedsnavn] AS A, tbl_Import_fra_Excel.Fødselsdato1
FROM tbl_Poststed INNER JOIN tbl_Import_fra_Excel ON tbl_Poststed.Postnr =
tbl_Import_fra_Excel.Postnr;
-------------

2nd query
------------
SELECT qAddresse1.Addresse, qAddresse1.A, Min(qAddresse1.Fødselsdato1) AS
MinOfFødselsdato1
FROM qAddresse1
GROUP BY qAddresse1.Addresse, qAddresse1.A;
------------


3rd query
-----------
SELECT qAddresse1.Fornavn, qAddresse1.Etternavn, qAddresse1.Addresse,
qAddresse1.A, qAddresse1.Fødselsdato1
FROM qAddresse1 INNER JOIN qAddresse2 ON (qAddresse1.Fødselsdato1 =
qAddresse2.MinOfFødselsdato1) AND (qAddresse1.A = qAddresse2.A) AND
(qAddresse1.Addresse = qAddresse2.Addresse);
------------

Jeanette Cunningham








Tom R. said:
Thank you Janette for all your helt.
I did some changes to your first query, and it worked:

1. (Added a comma , between Adresse and Postnr Stedsnavn)
SELECT [Adresse] & " , " & [Tbl_Import_fra_Excel.Postnr] & " " &
[Tbl_Poststed.Stedsnavn] AS FullAddresse,
Tbl_Import_fra_Excel.Fødselsdato1,
Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr;

2.
SELECT qAddressA.FullAddresse, Min(qAddressA.Fødselsdato1) AS
MinOfFødselsdato1
FROM (SELECT qAddressA.FullAddresse
FROM qAddressA
GROUP BY qAddressA.FullAddresse) AS Q INNER JOIN qAddressA ON
Q.FullAddresse = qAddressA.FullAddresse
GROUP BY qAddressA.FullAddresse;

3.
SELECT qAddressA.Fornavn, qAddressA.Etternavn, qAddressA.FullAddresse
FROM qAddressA INNER JOIN qAddressC ON (qAddressA.Fødselsdato1 =
qAddressC.MinOfFødselsdato1) AND (qAddressA.FullAddresse =
qAddressC.FullAddresse);


Now my FullAddress field outputs:
Grethesvei 17, 3960 Stathelle

Is there now any way to split the FullAddresse field again?

Grethesvei 17 in one field
and 3960 in another field.

I seperated them with a comma ( , ) in the first SQL.
It is nessesary to do so when I shall print of Adress labels.

Thank you again!
--
Tom R. Thorstensen


Jeanette Cunningham said:
Tom,
do this with 3 queries.

1st query
---------------------
SELECT [Addresse] & " " & [Postnr] & " " & [Stedsnavn] AS FullAddresse,
tbl_Import_fra_Excel.Fødselsdato1, tbl_Import_fra_Excel.Etternavn,
tbl_Import_fra_Excel.Fornavn
FROM tbl_Import_fra_Excel;
--------------------
Save the above query as qAddressA and we will use it in the next queries.

2nd query
-------------------
SELECT qAddressA.FullAddresse, Min(qAddressA.Fødselsdato1) AS
MinOfFødselsdato1
FROM [SELECT qAddressA.FullAddresse
FROM qAddressA
GROUP BY qAddressA.FullAddresse]. AS Q INNER JOIN qAddressA ON
Q.FullAddresse = qAddressA.FullAddresse
GROUP BY qAddressA.FullAddresse;
------------------
Save the above query as qAddressC

3rd query
-----------------
SELECT qAddressA.Etternavn, qAddressA.FullAddresse
FROM qAddressA INNER JOIN qAddressC ON (qAddressA.Fødselsdato1 =
qAddressC.MinOfFødselsdato1) AND (qAddressA.FullAddresse =
qAddressC.FullAddresse);
-----------------

Jeanette Cunningham

Tom R. said:
I am nearly there, but when I used it I only got one person from
database,
and that was the youngest person???

My table name is Tbl_Import_fra_Excel
Fields:
Fornavn (First name)
Etternavn (Last name)
Adresse (Address)
Postnr (Zip code)
Stedsnavn (Area?) from a table called Tbl_Poststed
Fødselsdato1 (Bithdate)

Here is my query:

SELECT Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn,
Tbl_Import_fra_Excel.Adresse, Tbl_Import_fra_Excel.Postnr,
Tbl_Poststed.Stedsnavn, Tbl_Import_fra_Excel.Fødselsdato1
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON
Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr
WHERE (((Tbl_Import_fra_Excel.Fødselsdato1)=(SELECT
Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)));

The Subquery in the field Fødselsdato1(Birthdate) says:

(SELECT Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)

Is this how I shoul do it, or am I doing something wrong.
--
Tom R. Thorstensen


:

Tom,
sounds like you need a sub query.
Here is an example

SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress
WHERE (((tblAddress.BirthDate)=(SELECT Max(tblAddress.BirthDate) AS
MaxOfBirthDate
FROM tblAddress)) ;

to explain a bit:
assume your main query is the following:
SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress

the sub query is
(SELECT Max(tblAddress.BirthDate) AS MaxOfBirthDate
FROM tblAddress))

in the main query in design view, type the subquery into the criteria
row
of
the main row in the field for BirthDate

Jeanette Cunningham


Hey.

I have made a query to make address labels from and i have an
address
field
in it.
But now when I run the query, I get all the people on an address. I
just
want one person from each address.
Is there also possible to sort it out so I get's the oldest person
in
that
house hold to?
Have a field called [Fødselsdato] that holds the persons birthdate.
--
Tom R. Thorstensen


:

Hi Tom,
the post doesn't say how your table is set up or how much
experience
you
have with queries, so I am just guessing on how much info and
explanation
to
include.
Do you have your names and addresses in a query?
If you create a new query that concatenates the street no, the
street
name
and city into a single field, you could set Unique Values to Yes on
the
query's property sheet. That would give you a list of all addresses
needed
without duplicates.

Jeanette Cunningham


Hello!

Is there a way to sort out just one address so that I don't send
the
same
info several times to one household. I have many people in a
familiy
and
they
have of course the same address, but I only want to send them my
info
once.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much for your help.
That was just what I was looking for. =)
--
Tom R. Thorstensen


Jeanette Cunningham said:
This gives you the 2 address fields as you need for your mailing labels.

1st query
--------------
SELECT tbl_Import_fra_Excel.Fornavn, tbl_Import_fra_Excel.Etternavn,
tbl_Import_fra_Excel.Addresse, [tbl_Import_fra_Excel].[Postnr] & " " &
[Stedsnavn] AS A, tbl_Import_fra_Excel.Fødselsdato1
FROM tbl_Poststed INNER JOIN tbl_Import_fra_Excel ON tbl_Poststed.Postnr =
tbl_Import_fra_Excel.Postnr;
-------------

2nd query
------------
SELECT qAddresse1.Addresse, qAddresse1.A, Min(qAddresse1.Fødselsdato1) AS
MinOfFødselsdato1
FROM qAddresse1
GROUP BY qAddresse1.Addresse, qAddresse1.A;
------------


3rd query
-----------
SELECT qAddresse1.Fornavn, qAddresse1.Etternavn, qAddresse1.Addresse,
qAddresse1.A, qAddresse1.Fødselsdato1
FROM qAddresse1 INNER JOIN qAddresse2 ON (qAddresse1.Fødselsdato1 =
qAddresse2.MinOfFødselsdato1) AND (qAddresse1.A = qAddresse2.A) AND
(qAddresse1.Addresse = qAddresse2.Addresse);
------------

Jeanette Cunningham








Tom R. said:
Thank you Janette for all your helt.
I did some changes to your first query, and it worked:

1. (Added a comma , between Adresse and Postnr Stedsnavn)
SELECT [Adresse] & " , " & [Tbl_Import_fra_Excel.Postnr] & " " &
[Tbl_Poststed.Stedsnavn] AS FullAddresse,
Tbl_Import_fra_Excel.Fødselsdato1,
Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr;

2.
SELECT qAddressA.FullAddresse, Min(qAddressA.Fødselsdato1) AS
MinOfFødselsdato1
FROM (SELECT qAddressA.FullAddresse
FROM qAddressA
GROUP BY qAddressA.FullAddresse) AS Q INNER JOIN qAddressA ON
Q.FullAddresse = qAddressA.FullAddresse
GROUP BY qAddressA.FullAddresse;

3.
SELECT qAddressA.Fornavn, qAddressA.Etternavn, qAddressA.FullAddresse
FROM qAddressA INNER JOIN qAddressC ON (qAddressA.Fødselsdato1 =
qAddressC.MinOfFødselsdato1) AND (qAddressA.FullAddresse =
qAddressC.FullAddresse);


Now my FullAddress field outputs:
Grethesvei 17, 3960 Stathelle

Is there now any way to split the FullAddresse field again?

Grethesvei 17 in one field
and 3960 in another field.

I seperated them with a comma ( , ) in the first SQL.
It is nessesary to do so when I shall print of Adress labels.

Thank you again!
--
Tom R. Thorstensen


Jeanette Cunningham said:
Tom,
do this with 3 queries.

1st query
---------------------
SELECT [Addresse] & " " & [Postnr] & " " & [Stedsnavn] AS FullAddresse,
tbl_Import_fra_Excel.Fødselsdato1, tbl_Import_fra_Excel.Etternavn,
tbl_Import_fra_Excel.Fornavn
FROM tbl_Import_fra_Excel;
--------------------
Save the above query as qAddressA and we will use it in the next queries.

2nd query
-------------------
SELECT qAddressA.FullAddresse, Min(qAddressA.Fødselsdato1) AS
MinOfFødselsdato1
FROM [SELECT qAddressA.FullAddresse
FROM qAddressA
GROUP BY qAddressA.FullAddresse]. AS Q INNER JOIN qAddressA ON
Q.FullAddresse = qAddressA.FullAddresse
GROUP BY qAddressA.FullAddresse;
------------------
Save the above query as qAddressC

3rd query
-----------------
SELECT qAddressA.Etternavn, qAddressA.FullAddresse
FROM qAddressA INNER JOIN qAddressC ON (qAddressA.Fødselsdato1 =
qAddressC.MinOfFødselsdato1) AND (qAddressA.FullAddresse =
qAddressC.FullAddresse);
-----------------

Jeanette Cunningham

I am nearly there, but when I used it I only got one person from
database,
and that was the youngest person???

My table name is Tbl_Import_fra_Excel
Fields:
Fornavn (First name)
Etternavn (Last name)
Adresse (Address)
Postnr (Zip code)
Stedsnavn (Area?) from a table called Tbl_Poststed
Fødselsdato1 (Bithdate)

Here is my query:

SELECT Tbl_Import_fra_Excel.Fornavn, Tbl_Import_fra_Excel.Etternavn,
Tbl_Import_fra_Excel.Adresse, Tbl_Import_fra_Excel.Postnr,
Tbl_Poststed.Stedsnavn, Tbl_Import_fra_Excel.Fødselsdato1
FROM Tbl_Poststed INNER JOIN Tbl_Import_fra_Excel ON
Tbl_Poststed.Postnr =
Tbl_Import_fra_Excel.Postnr
WHERE (((Tbl_Import_fra_Excel.Fødselsdato1)=(SELECT
Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)));

The Subquery in the field Fødselsdato1(Birthdate) says:

(SELECT Max(Tbl_Import_fra_Excel.Fødselsdato1) AS MaxOfFødselsdato1
FROM Tbl_Import_fra_excel)

Is this how I shoul do it, or am I doing something wrong.
--
Tom R. Thorstensen


:

Tom,
sounds like you need a sub query.
Here is an example

SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress
WHERE (((tblAddress.BirthDate)=(SELECT Max(tblAddress.BirthDate) AS
MaxOfBirthDate
FROM tblAddress)) ;

to explain a bit:
assume your main query is the following:
SELECT tblAddress.LName, tblAddress.Address, tblAddress.BirthDate
FROM tblAddress

the sub query is
(SELECT Max(tblAddress.BirthDate) AS MaxOfBirthDate
FROM tblAddress))

in the main query in design view, type the subquery into the criteria
row
of
the main row in the field for BirthDate

Jeanette Cunningham


Hey.

I have made a query to make address labels from and i have an
address
field
in it.
But now when I run the query, I get all the people on an address. I
just
want one person from each address.
Is there also possible to sort it out so I get's the oldest person
in
that
house hold to?
Have a field called [Fødselsdato] that holds the persons birthdate.
--
Tom R. Thorstensen


:

Hi Tom,
the post doesn't say how your table is set up or how much
experience
you
have with queries, so I am just guessing on how much info and
explanation
to
include.
Do you have your names and addresses in a query?
If you create a new query that concatenates the street no, the
street
name
and city into a single field, you could set Unique Values to Yes on
the
query's property sheet. That would give you a list of all addresses
needed
without duplicates.

Jeanette Cunningham


Hello!

Is there a way to sort out just one address so that I don't send
the
same
info several times to one household. I have many people in a
familiy
and
they
have of course the same address, but I only want to send them my
info
once.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top