Software

Ask something
Discussion Locked

Nz Function or better way?

0 helpful
4 replies

by twila.mitchell

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to create a report that will return a companies main zip code plus the zip codes of any additional locations (if any). The report is actually a letter about a specific client. Basically it says, Client "A" is now sold and needs pertinent information regarding our products in the following zip codes:

I have a table of Client information (name, address, city, state, zip, etc.)

I have a table of extended client information (ExtendedID, ClientID (as a foreign key), Owner's name, Description of Operation, and a cell for whether this client is currently quoted, sold, or declined)

I have a table for additional location addresses. (LocationsID, ClientID(as a foreign key), Address, City, State, Zip) Some clients have additional locations but of course most do not.

I have related the tables together with autonumber fields. (ie: ClientID, ExtendedID, LocationsID)

My relationships seem to have pulled things together nicely except I don't understand how to create a query to support this report that is needed.

I ran a query that collects information from these tables, (client name, clientzip, quotestatus "criteria = "sold", locationzip.

This works fine so long as a client is sold AND has additional locations, but for clients who are sold and have only one location, the query is excluding them. I read something about using an Nz Function but I am not familiar with VB and barely understand macros.

Any help for how to get around this problem would be appreciated.

4 replies

by craig herberg

It looks like you are trying to join to a table that has no data for most of your clients. Although some tools can deal with short paths, most will not. Why don't you change your database structure, so all addresses are in a single table? This is a more solid database design.

Good luck.

Craig Herberg

by tony hopkinson

your one location is in teh client table and the rest in extended. Doing standard join you'll only get the clients with more than one location.
You could do an outer join but then you'd have to deal with the nulls in the fields of your resultset for those with no data in extended.
Be alot easier/better to take the address info out of client and put it into extended so then every client would have at least one address.

by deepsand

This is a tech. problem, in search of a solution.

It properly belongs in "Technical Q&A," not in "Discussions." Please re-post there.

Not only will you be more likely to attract the attention of those seeking to provide answers, but you will also make it easier for those seeking discussions to find such.

Thank you.

by twila.mitchell

I do think that the answers given here will take care of my immediate problem without my having to learn programming. At the request of Deepsand, I am reposting it to the technical Q&A just to see what they say.... and also this experience may teach me more about what goes into each area.

As I said -- I still have a lot to learn! (smile)

This discussion is closed to new replies.