If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Choosing one address over another in a table
Hello,
I have a table in access which stores my customer information. It allows the customer to enter in a billing address and a shipping address (if different than the billing address). There is a check box which is checked if there is a different shipping address. I would like to merge to word to print out my customer information, however i would like all of the shipping addresses to be printed out (not the billing addresses). How do i send only the shipping addresses to word? I tried a query, but it will not hold my alphabetical order sort like the table does, so i would like to send it via a table. Thanks in advance for your help. I'm also going to ask word people too since my problem encompasses both programs. Ilan |
#2
|
|||
|
|||
Ilan
I do not understand your comment about the query "not hold my alphabetical order sort like the table does". Normally the functionality is just the opposite... tables do not have the data in any particular order, whereas you have total control over the sorting in a query. In your query you can make a calculated field like this... OnlyShipping: Nz([Shipping Address],[Billing Address]) In other words, if there is an entry in the Shipping Address field, this will be used, but if there is no Shipping Address entry, the Billing Address data will be used. Is this what you want? -- Steve Schapel, Microsoft Access MVP Ilan wrote: Hello, I have a table in access which stores my customer information. It allows the customer to enter in a billing address and a shipping address (if different than the billing address). There is a check box which is checked if there is a different shipping address. I would like to merge to word to print out my customer information, however i would like all of the shipping addresses to be printed out (not the billing addresses). How do i send only the shipping addresses to word? I tried a query, but it will not hold my alphabetical order sort like the table does, so i would like to send it via a table. Thanks in advance for your help. I'm also going to ask word people too since my problem encompasses both programs. Ilan |
#3
|
|||
|
|||
See, i sort my information on three levels. Last name,
then first name, then middle name (i.e. I want John B. Colangelo to come before John P. Colangelo). If i send to word i can sort with these levels while i have not been able to do so in the query. As for your suggestion of the query structure, that is what i want, but since i broke down my fields into add line 1, add line 2, city, state, zip, etc. for each address will this still be plausible. If yes, should i (and would you mind) if i "borrowed" your SQL structure for my query? Also, do i need to drop down to SQL view to use it and what does the NZ stand for? Thanks, Ilan -----Original Message----- Ilan I do not understand your comment about the query "not hold my alphabetical order sort like the table does". Normally the functionality is just the opposite... tables do not have the data in any particular order, whereas you have total control over the sorting in a query. In your query you can make a calculated field like this... OnlyShipping: Nz([Shipping Address],[Billing Address]) In other words, if there is an entry in the Shipping Address field, this will be used, but if there is no Shipping Address entry, the Billing Address data will be used. Is this what you want? -- Steve Schapel, Microsoft Access MVP Ilan wrote: Hello, I have a table in access which stores my customer information. It allows the customer to enter in a billing address and a shipping address (if different than the billing address). There is a check box which is checked if there is a different shipping address. I would like to merge to word to print out my customer information, however i would like all of the shipping addresses to be printed out (not the billing addresses). How do i send only the shipping addresses to word? I tried a query, but it will not hold my alphabetical order sort like the table does, so i would like to send it via a table. Thanks in advance for your help. I'm also going to ask word people too since my problem encompasses both programs. Ilan . |
#4
|
|||
|
|||
Ilan
If you look at the deign view of a query, you will see that there is a row in the query design grid for "Sort", which you can use to have control over the order of the records. This is not possible in a table. If your addresses are split over a number of fields, it makes things a bit more complicated, but it is still possible. When the address is exported to Word, do you want it still split into the separate components, or do you want the address "unified". Nz stands for "null to zero". It is one of Access's standard built-in functions. -- Steve Schapel, Microsoft Access MVP Ilan wrote: See, i sort my information on three levels. Last name, then first name, then middle name (i.e. I want John B. Colangelo to come before John P. Colangelo). If i send to word i can sort with these levels while i have not been able to do so in the query. As for your suggestion of the query structure, that is what i want, but since i broke down my fields into add line 1, add line 2, city, state, zip, etc. for each address will this still be plausible. If yes, should i (and would you mind) if i "borrowed" your SQL structure for my query? Also, do i need to drop down to SQL view to use it and what does the NZ stand for? Thanks, Ilan |
#5
|
|||
|
|||
ok i will sort my query accordingly. i would like to keep
the fields separated when they are exported to word. The NZ feature sounds like it could help, how could i go about finding more information on how to use it? Thanks, Ilan -----Original Message----- Ilan If you look at the deign view of a query, you will see that there is a row in the query design grid for "Sort", which you can use to have control over the order of the records. This is not possible in a table. If your addresses are split over a number of fields, it makes things a bit more complicated, but it is still possible. When the address is exported to Word, do you want it still split into the separate components, or do you want the address "unified". Nz stands for "null to zero". It is one of Access's standard built-in functions. -- Steve Schapel, Microsoft Access MVP Ilan wrote: See, i sort my information on three levels. Last name, then first name, then middle name (i.e. I want John B. Colangelo to come before John P. Colangelo). If i send to word i can sort with these levels while i have not been able to do so in the query. As for your suggestion of the query structure, that is what i want, but since i broke down my fields into add line 1, add line 2, city, state, zip, etc. for each address will this still be plausible. If yes, should i (and would you mind) if i "borrowed" your SQL structure for my query? Also, do i need to drop down to SQL view to use it and what does the NZ stand for? Thanks, Ilan . |
#6
|
|||
|
|||
Ilan,
You will find a topic on the Nz() function in Access Help. The syntax is as I gave it before... Nz([NameOfField],ValueToUseIfFieldIsNull) -- Steve Schapel, Microsoft Access MVP Ilan wrote: ok i will sort my query accordingly. i would like to keep the fields separated when they are exported to word. The NZ feature sounds like it could help, how could i go about finding more information on how to use it? Thanks, Ilan |
#7
|
|||
|
|||
Ok, final question. I have my data stored on two tables
as i mentioned earlier. But data stored in the shipping address table does not store data in the table at all if the person is using the billing address for everything. Therefore, how do i pull the data so it shows everyone. If i run it right now i only get those records that are in the shipping address table. Thanks, Ilan -----Original Message----- Ilan, You will find a topic on the Nz() function in Access Help. The syntax is as I gave it before... Nz([NameOfField],ValueToUseIfFieldIsNull) -- Steve Schapel, Microsoft Access MVP Ilan wrote: ok i will sort my query accordingly. i would like to keep the fields separated when they are exported to word. The NZ feature sounds like it could help, how could i go about finding more information on how to use it? Thanks, Ilan . |
#8
|
|||
|
|||
Ilan,
Actually, you didn't mention earlier that you had the shipping address in a separate table. What is the reason for this design? You willl need to use a Left Join between the tables in the query. In design view of the query, double-click on the join line between the tables, and then in the properties dialog that pops up, select the option that says something like "all records from the table with the billing address, and matching records from the shipping address table". -- Steve Schapel, Microsoft Access MVP Ilan wrote: Ok, final question. I have my data stored on two tables as i mentioned earlier. But data stored in the shipping address table does not store data in the table at all if the person is using the billing address for everything. Therefore, how do i pull the data so it shows everyone. If i run it right now i only get those records that are in the shipping address table. Thanks, Ilan |
#9
|
|||
|
|||
I decided to have them on separate tables just because i
thought it would be cleaner. I ran the Nz functions in my query and it worked perfectly!! Thanks for all of your help Steve! Ilan -----Original Message----- Ilan, Actually, you didn't mention earlier that you had the shipping address in a separate table. What is the reason for this design? You willl need to use a Left Join between the tables in the query. In design view of the query, double-click on the join line between the tables, and then in the properties dialog that pops up, select the option that says something like "all records from the table with the billing address, and matching records from the shipping address table". -- Steve Schapel, Microsoft Access MVP Ilan wrote: Ok, final question. I have my data stored on two tables as i mentioned earlier. But data stored in the shipping address table does not store data in the table at all if the person is using the billing address for everything. Therefore, how do i pull the data so it shows everyone. If i run it right now i only get those records that are in the shipping address table. Thanks, Ilan . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Keeping old customer address when they move house. | Russell | General Discussion | 8 | July 19th, 2004 12:24 PM |
Searching address books from the "To:" box in a new email | Robert Stokes | Contacts | 10 | July 17th, 2004 11:06 AM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |