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 |
#11
|
|||
|
|||
Access Labels - Same info on all labels
len,
I think you see what I mean now. Drop the key fields from your ancillary tables, and just store the actual State or Type. What you're doing is not "wrong"... it's just a bit of "over normalization". It is possible to store the ID of a State, instead of the State itself, in the Client table, but you have to relink them back together again with the proper realtionship in your label query... or any subsequent queries and reports. To fix the problem, I would do an "Update" query against your Client table to bring those ancillary external values into it. Using State as an example... Create a new text State field in Clients. Create an Update query that links tblClients to tblStates via your existing StateID field, using a one to many relationship ("show all in Clients and only those in States where there is a match"). Set it up to update the new State field with the text State "name". After running the update query, the new State field should contain the name of the appropriate state. You can then delete the StateID field from Clients, and rework your form to store the State name directly (instead of the StateID) from your combo box. Do the same for ClientType.. etc.. etc... And.. it's no trouble at all. That's what the newsgroup is here for. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message ... Groan....I havent had much experience with Access and am self taught. I downloaded some Microsoft templates to check how they were set up... so thought i was doing the right thing. I tested your suggestion without the extra tables and it works beautifully...thanks. Only problem is that the state and ID only appear as the auto number.. so obviously have the problem you mentioned. I will have to go back to the old drawing board and work out how to fix it. I am really sorry to have troubled you with all this. Thank you so much for your help. Kind Regards LEN. "Al Camp" wrote: len, Well, having external tables that provide combo values for your Clients form is OK, but once a value is selected, it should be stored against the client in that Client table. For ex... if you have a table of States, selecting a client state from a combo should store that value in the Client [State] field. In your label query it sounds like your trying to link the State table back to the Client table. That should not be necessary... the client State should be stored in the Client table, and available to the query from that table. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message ... Hi Al Not being an expert at Access I thought it was the correct way. The two items I mentioned are the same for a number of people so are using drop down boxes to save work. While the states dont change, the type may. I was trying to make it easier for the user so that she could add in extra types if needed. ie. I have a form to add additional types. If you could advise of a better way it would be great. Regards LEN "Al Camp" wrote: Len, As I guessed, the relationship between those tables is the culprit, but I have no way of determining why. But, more importantly tan that... all the info you mentioned about the client should be in one table... say tblClients. Name address, type, and state are all "ONE" related to the client. Why would you have personal details in one table.. and the state they live in in another?? Get that data "normalized" into one table, and your label, and all subsequent reports will be easy to develop. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message ... Hi Al The labels are not for addressing purposes. It is info required for OH & S so includes their name, address and information such as type of client etc. There are three tables... one which includes his personal details, one which inlcudes the type of client and one which inlcudes the state they live in. i did a test without the table and all works fine. When i add the additional table i receive the message i mentioned. Thanks very much Len "Al Camp" wrote: Len, Do you have just an Address table and the Cartesian "counter" table in your query... or do you have other tables included? It sounds like you do, and Access is balking at one of those "links." Please try to explain why you have more than just those two tables involved. Normally, an Address table should have all the info you need to create a mailing label. ALSO.... Try running a "basic test" query with just the Address and Counter table, with no joins, and see if you can get that to run. Try just 1through10 in your Counter table to see if you can get each address to repeat 10 times. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message news Hi Al I created the table and added to the query with no joins, but get a message stating that the SQL statement could not be executed because it contains ambiguous outer joins and to force one of the joins to be performed first by creating a separate query. I am really sorry, but I am not an expert at this, so just checking to see if I have done something wrong. Thansk Len "Al Camp" wrote: Len, Create a cartesian relationship to force a single name to repeat X number of times to fill one sheet of labels. Let's say you have 30 labels on a page. Create a table called tblCounter with one field called Counter, and populate that table with... 1, 2, 3, 4, etc... to 30 Place your Address table, and the new Counter table on the query... with NO connecting relationship. This will force (through a cartesian relationship) any name in the Names table to repeat 30 times... once for each Counter in the Counter table. Now, if you filter for just one name, that name will repeat 30 times, and fill your label page. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message ... Hi There I would like to create labels in Access using a parameter query to call up a person, then populate all lables on a page with the same information. ie. the above persons Name, Address, date of birth etc. Is this possible? If so, how is it done? Thanks |
#12
|
|||
|
|||
Access Labels - Same info on all labels
Thank you so much... I will give it a try when I get home tonight. I really
appreciate this. Have a great week. Regards Len "Al Camp" wrote: len, I think you see what I mean now. Drop the key fields from your ancillary tables, and just store the actual State or Type. What you're doing is not "wrong"... it's just a bit of "over normalization". It is possible to store the ID of a State, instead of the State itself, in the Client table, but you have to relink them back together again with the proper realtionship in your label query... or any subsequent queries and reports. To fix the problem, I would do an "Update" query against your Client table to bring those ancillary external values into it. Using State as an example... Create a new text State field in Clients. Create an Update query that links tblClients to tblStates via your existing StateID field, using a one to many relationship ("show all in Clients and only those in States where there is a match"). Set it up to update the new State field with the text State "name". After running the update query, the new State field should contain the name of the appropriate state. You can then delete the StateID field from Clients, and rework your form to store the State name directly (instead of the StateID) from your combo box. Do the same for ClientType.. etc.. etc... And.. it's no trouble at all. That's what the newsgroup is here for. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message ... Groan....I havent had much experience with Access and am self taught. I downloaded some Microsoft templates to check how they were set up... so thought i was doing the right thing. I tested your suggestion without the extra tables and it works beautifully...thanks. Only problem is that the state and ID only appear as the auto number.. so obviously have the problem you mentioned. I will have to go back to the old drawing board and work out how to fix it. I am really sorry to have troubled you with all this. Thank you so much for your help. Kind Regards LEN. "Al Camp" wrote: len, Well, having external tables that provide combo values for your Clients form is OK, but once a value is selected, it should be stored against the client in that Client table. For ex... if you have a table of States, selecting a client state from a combo should store that value in the Client [State] field. In your label query it sounds like your trying to link the State table back to the Client table. That should not be necessary... the client State should be stored in the Client table, and available to the query from that table. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message ... Hi Al Not being an expert at Access I thought it was the correct way. The two items I mentioned are the same for a number of people so are using drop down boxes to save work. While the states dont change, the type may. I was trying to make it easier for the user so that she could add in extra types if needed. ie. I have a form to add additional types. If you could advise of a better way it would be great. Regards LEN "Al Camp" wrote: Len, As I guessed, the relationship between those tables is the culprit, but I have no way of determining why. But, more importantly tan that... all the info you mentioned about the client should be in one table... say tblClients. Name address, type, and state are all "ONE" related to the client. Why would you have personal details in one table.. and the state they live in in another?? Get that data "normalized" into one table, and your label, and all subsequent reports will be easy to develop. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message ... Hi Al The labels are not for addressing purposes. It is info required for OH & S so includes their name, address and information such as type of client etc. There are three tables... one which includes his personal details, one which inlcudes the type of client and one which inlcudes the state they live in. i did a test without the table and all works fine. When i add the additional table i receive the message i mentioned. Thanks very much Len "Al Camp" wrote: Len, Do you have just an Address table and the Cartesian "counter" table in your query... or do you have other tables included? It sounds like you do, and Access is balking at one of those "links." Please try to explain why you have more than just those two tables involved. Normally, an Address table should have all the info you need to create a mailing label. ALSO.... Try running a "basic test" query with just the Address and Counter table, with no joins, and see if you can get that to run. Try just 1through10 in your Counter table to see if you can get each address to repeat 10 times. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message news Hi Al I created the table and added to the query with no joins, but get a message stating that the SQL statement could not be executed because it contains ambiguous outer joins and to force one of the joins to be performed first by creating a separate query. I am really sorry, but I am not an expert at this, so just checking to see if I have done something wrong. Thansk Len "Al Camp" wrote: Len, Create a cartesian relationship to force a single name to repeat X number of times to fill one sheet of labels. Let's say you have 30 labels on a page. Create a table called tblCounter with one field called Counter, and populate that table with... 1, 2, 3, 4, etc... to 30 Place your Address table, and the new Counter table on the query... with NO connecting relationship. This will force (through a cartesian relationship) any name in the Names table to repeat 30 times... once for each Counter in the Counter table. Now, if you filter for just one name, that name will repeat 30 times, and fill your label page. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "len" wrote in message ... Hi There I would like to create labels in Access using a parameter query to call up a person, then populate all lables on a page with the same information. ie. the above persons Name, Address, date of birth etc. Is this possible? If so, how is it done? Thanks |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using Access 2003 to get info from Excel | Rusty | New Users | 2 | September 17th, 2005 04:45 PM |
Ambiguous Name Error | pm | Using Forms | 10 | June 5th, 2005 09:19 PM |
Select Query By Month. | Terry | Running & Setting Up Queries | 6 | June 2nd, 2005 04:10 PM |
Access Error Message when opening database | eah | General Discussion | 3 | January 26th, 2005 10:04 AM |
starting access 97 | Edward Letendre | General Discussion | 2 | January 26th, 2005 02:15 AM |