query filter

[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

 

[Beetle]

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.

 

[Christine]

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?

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

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

 

[Beetle]

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

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?

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

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