Need HELP to limit number of records per individual

[John]

I want to limit the number of records showing for
individuals with multiple Addresses.
Following is my table structure.

tblARTISTS tblADDRESS_TYPE tblADDRESS
ArtistID >>>>>>ArtistID
AddressID <<<<<<<<<<AddressID
I'm not sure how to describe foriegn keys and such, but ...
tblArtists is a one to many link to tblAddressType via AritistID.
tblAddress is also a one to many link to tblAddressType via AddressID

Any time I have an artist with more than one address, I get
all the addesses showing for those Artists. But for this query,
I only want the First Address for each Artist.

I copied the SQL from the Actual Query I am working with.
SELECT tblArtists.ArtistID, tblAddress.AddressID
FROM tblArtists INNER JOIN (tblAddress INNER JOIN tblAddress_Type
ON tblAddress.AddressID = tblAddress_Type.AddressID)
ON tblArtists.ArtistID = tblAddressType.ArtistID;

How do I limit the query to show the Artist only once
with only one address?
Can anyone help or advise me?
If this is a confusing description, please let me know
and I will attempt to better describe the situation.
Thank You

 

[John Vinson]

I want to limit the number of records showing for
individuals with multiple Addresses.
Following is my table structure.

tblARTISTS tblADDRESS_TYPE tblADDRESS
ArtistID >>>>>>ArtistID
AddressID <<<<<<<<<<AddressID
I'm not sure how to describe foriegn keys and such, but ...
tblArtists is a one to many link to tblAddressType via AritistID.
tblAddress is also a one to many link to tblAddressType via AddressID

Any time I have an artist with more than one address, I get
all the addesses showing for those Artists. But for this query,
I only want the First Address for each Artist.

I copied the SQL from the Actual Query I am working with.
SELECT tblArtists.ArtistID, tblAddress.AddressID
FROM tblArtists INNER JOIN (tblAddress INNER JOIN tblAddress_Type
ON tblAddress.AddressID = tblAddress_Type.AddressID)
ON tblArtists.ArtistID = tblAddressType.ArtistID;

How do I limit the query to show the Artist only once
with only one address?
Can anyone help or advise me?
If this is a confusing description, please let me know
and I will attempt to better describe the situation.
Thank You

Make it a Totals query; group by ArtestID, and select First for the
AddressID.

Note that "first" means "first in disk storage order" - and that disk
storage order is arbitrary and pretty much uncontrollable. This will
give you A single address, but there's really no telling which; if you
are assuming that records will be stored in the same order in which
they were entered, you may be in for a disappointment!

John W. Vinson[MVP]

 

[John]

Thank You very much John,

Your suggestion works, Thanks. But I was hoping it would be
editable because I will have another field or two from the
Address table, one of them being a check box that
I wanted to be able to use. But I guess I can't because it's
a Totals Query. Is that right?

Anyway Thanks for the help!

 

[John Vinson]

Thank You very much John,

Your suggestion works, Thanks. But I was hoping it would be
editable because I will have another field or two from the
Address table, one of them being a check box that
I wanted to be able to use. But I guess I can't because it's
a Totals Query. Is that right?

No, you should be able to use a checkbox (yes/no) field. If you want
to use it as a criterion, select the field in your query; choose Where
as the "operator" on the Totals row; and use a criterion of True (of
False, whichever you need).

John W. Vinson[MVP]