Access Labels - Same info on all labels

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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.
 
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
 
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.
 
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
 
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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

:

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


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

:

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

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

:

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

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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

:

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


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

:

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

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

:

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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top