query filter

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

I have a basic three table database. It's working very well but I'm having a
bit of a problem with a query.

tbl1 - name, title
tbl2 - contact info
tbl3 - events

My problem comes from filtering addresses. I have 5 categories of
addresses: work1, work2, work3, work4, work5.

Half of the contacts have 1 address (either work1 or work2). Easy to do.

The other half can have 2 or 3 addresses (work3, work4, work5). Of this
group, everyone has work3 and work4. Only some have work5.

I need to create a query for invitation list an event (e.g. Gala2009).
Everyone in the second group will be invited. For most, I will use address
work3. However, for those that have work5, I need that to be the address. How
do I create a filter that indicates where both work3 and work5 exist, use
work5?

TIA
 
To solve your immediate problem, you can use a calculated field
(not a filter) in your query like the following;

TheAddress: IIf(Nz([Work5], "")="", [Work3], [Work5])

However, you really should consider correcting your table structure.
You should not have fields like Work1, Work2, Work3, etc.. You should
have a table for addresses that is related 1:m to your clients table
(via ClientID for example). That way, a given client could have as many,
or as few addresses as are necessary. This table would perhaps have an
additional field to indicate the category of the address.
 
sorry, I didn't explain my db clearly.

work1, work2, etc.. are not fields, they are options within a field named
Address Category. This is part of my table 2 - contact info (addresses,
phone, fax, cell, email etc...). It is a one to many relationship with tbl1.

Would the calculated field you suggested still work?

Beetle said:
To solve your immediate problem, you can use a calculated field
(not a filter) in your query like the following;

TheAddress: IIf(Nz([Work5], "")="", [Work3], [Work5])

However, you really should consider correcting your table structure.
You should not have fields like Work1, Work2, Work3, etc.. You should
have a table for addresses that is related 1:m to your clients table
(via ClientID for example). That way, a given client could have as many,
or as few addresses as are necessary. This table would perhaps have an
additional field to indicate the category of the address.
--
_________

Sean Bailey


Christine said:
I have a basic three table database. It's working very well but I'm having a
bit of a problem with a query.

tbl1 - name, title
tbl2 - contact info
tbl3 - events

My problem comes from filtering addresses. I have 5 categories of
addresses: work1, work2, work3, work4, work5.

Half of the contacts have 1 address (either work1 or work2). Easy to do.

The other half can have 2 or 3 addresses (work3, work4, work5). Of this
group, everyone has work3 and work4. Only some have work5.

I need to create a query for invitation list an event (e.g. Gala2009).
Everyone in the second group will be invited. For most, I will use address
work3. However, for those that have work5, I need that to be the address. How
do I create a filter that indicates where both work3 and work5 exist, use
work5?

TIA
Click to expand...
Click to expand...
 
Sorry. Misunderstood your post. In that case you would use criteria in
your query. Since your category field appears to be a text data type
with values like "work1", "work2", etc., I'm not sure this will reliably
find the "highest" value, but you might try something like;

SELECT Table1.ClientID, [FirstName] & " " & [LastName] AS FullName,
Table2.Address, Table2.Category
FROM Table1 INNER JOIN Table2 ON Table1.ClientID = Table2.ClientID
WHERE Table2.Category=DMax("Category","Table2","ClientID=" _
& [Table1].[ClientID]);

--
_________

Sean Bailey


Christine said:
sorry, I didn't explain my db clearly.

work1, work2, etc.. are not fields, they are options within a field named
Address Category. This is part of my table 2 - contact info (addresses,
phone, fax, cell, email etc...). It is a one to many relationship with tbl1.

Would the calculated field you suggested still work?

Beetle said:
To solve your immediate problem, you can use a calculated field
(not a filter) in your query like the following;

TheAddress: IIf(Nz([Work5], "")="", [Work3], [Work5])

However, you really should consider correcting your table structure.
You should not have fields like Work1, Work2, Work3, etc.. You should
have a table for addresses that is related 1:m to your clients table
(via ClientID for example). That way, a given client could have as many,
or as few addresses as are necessary. This table would perhaps have an
additional field to indicate the category of the address.
--
_________

Sean Bailey


Christine said:
I have a basic three table database. It's working very well but I'm having a
bit of a problem with a query.

tbl1 - name, title
tbl2 - contact info
tbl3 - events

My problem comes from filtering addresses. I have 5 categories of
addresses: work1, work2, work3, work4, work5.

Half of the contacts have 1 address (either work1 or work2). Easy to do.

The other half can have 2 or 3 addresses (work3, work4, work5). Of this
group, everyone has work3 and work4. Only some have work5.

I need to create a query for invitation list an event (e.g. Gala2009).
Everyone in the second group will be invited. For most, I will use address
work3. However, for those that have work5, I need that to be the address. How
do I create a filter that indicates where both work3 and work5 exist, use
work5?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top