Created A union Query of two tables, Would like to count records s

SELECT [Subject], [Paste verses]
FROM [tstudy]

UNION ALL SELECT [subject], [paste verses1]
FROM [Tstudy];

UNION ALL SELECT[subject], [paste verses2]
FROM [Tstudy];

Why I need to do after this is add an autonumber So I can use it in a form
based on the union query that will allow me to doubleclick on the record and
take me to the form from the Main Menu. (Usually you get the why do you want
to do it that way question) Thanks, Another solution I tried was to create
another query based on my union query, then add a field that will count
subquentially the field. Keep in mind I just need an uniqe number there to
use as a referance for the list box. If I put the autokey field in both
statements in the statement above, I get duplicate numbers and I dont want
that. Thanks, any help would be greatly appreciated using acccess 2003

I'm one of those who ask "why"...

If I'm interpreting your SQL statement correctly, you have a table [tStudy]
that has three different fields ([Paste verses], [Paste verses1], [Paste
verses2]) that contain the same kind of data. "Repeating fields" like this
are typically found ... in spreadsheets.

When you have to deal with them in a relational database, you (and Access)
have to work harder to come up with a work-around -- Access expects
well-normalized data, and repeating fields are not.

If you don't take another look at your table structure and normalize it, you
will have to modify it, and your queries, forms, reports, macros, code, etc.
every time you change the number of [Paste verses#] you wish to deal with.
Are you willing to do all that maintenance, or to force whoever maintains
this application after you to do it?

You can "pay now, or pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"BrianPaul" wrote in message
...
SELECT [Subject], [Paste verses]
FROM [tstudy]

UNION ALL SELECT [subject], [paste verses1]
FROM [Tstudy];

UNION ALL SELECT[subject], [paste verses2]
FROM [Tstudy];

Why I need to do after this is add an autonumber So I can use it in a form
based on the union query that will allow me to doubleclick on the record
and
take me to the form from the Main Menu. (Usually you get the why do you
want
to do it that way question) Thanks, Another solution I tried was to
create
another query based on my union query, then add a field that will count
subquentially the field. Keep in mind I just need an uniqe number there
to
use as a referance for the list box. If I put the autokey field in both
statements in the statement above, I get duplicate numbers and I dont want
that. Thanks, any help would be greatly appreciated using acccess 2003

Yep, There are actually 29 of them that paste verses 1, paste verses 2, ect.
I only listed the first 2. The database is used for bible study on a
particular subject, which is a field. Then comment1, comment2, ect, but will
only allow 29 comment and 29 paste verses for the subject (Keep in mind these
are memo fields). However, would like to combine the verses, which I did
successfully with the union querry. However, when I created another form,
put 2 list boxes on the form, The first list box updates the 2nd listbox
based on the subject. Anotherwords, selecting the subject in the first list
box will update the listbox in the 2nd one based on the subject. However, If
I want to see the entire verses which can be over 255 characters (reason its
a memo) I need to double click on the list box to bring up another form
which is a popup form to view the entire verse since it is a memo field. I
hope that Helps.

"Jeff Boyce" wrote:

I'm one of those who ask "why"...

If I'm interpreting your SQL statement correctly, you have a table [tStudy]
that has three different fields ([Paste verses], [Paste verses1], [Paste
verses2]) that contain the same kind of data. "Repeating fields" like this
are typically found ... in spreadsheets.

When you have to deal with them in a relational database, you (and Access)
have to work harder to come up with a work-around -- Access expects
well-normalized data, and repeating fields are not.

If you don't take another look at your table structure and normalize it, you
will have to modify it, and your queries, forms, reports, macros, code, etc.
every time you change the number of [Paste verses#] you wish to deal with.
Are you willing to do all that maintenance, or to force whoever maintains
this application after you to do it?

You can "pay now, or pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"BrianPaul" wrote in message
...
SELECT [Subject], [Paste verses]
FROM [tstudy]

UNION ALL SELECT [subject], [paste verses1]
FROM [Tstudy];

UNION ALL SELECT[subject], [paste verses2]
FROM [Tstudy];

Why I need to do after this is add an autonumber So I can use it in a form
based on the union query that will allow me to doubleclick on the record
and
take me to the form from the Main Menu. (Usually you get the why do you
want
to do it that way question) Thanks, Another solution I tried was to
create
another query based on my union query, then add a field that will count
subquentially the field. Keep in mind I just need an uniqe number there
to
use as a referance for the list box. If I put the autokey field in both
statements in the statement above, I get duplicate numbers and I dont want
that. Thanks, any help would be greatly appreciated using acccess 2003

Sorry, I should have mentioned. I need a field only created at runtime and
not a stored value. I thought of having it make a new table and adding an
autonumber, then when I went to the menu on open. then it would delete the
existing table and create a new, or append but dont have the knowledge to do
that. Thought this would be easier to do.

Thanks,

"BrianPaul" wrote:

Yep, There are actually 29 of them that paste verses 1, paste verses 2, ect.
I only listed the first 2. The database is used for bible study on a
particular subject, which is a field. Then comment1, comment2, ect, but will
only allow 29 comment and 29 paste verses for the subject (Keep in mind these
are memo fields). However, would like to combine the verses, which I did
successfully with the union querry. However, when I created another form,
put 2 list boxes on the form, The first list box updates the 2nd listbox
based on the subject. Anotherwords, selecting the subject in the first list
box will update the listbox in the 2nd one based on the subject. However, If
I want to see the entire verses which can be over 255 characters (reason its
a memo) I need to double click on the list box to bring up another form
which is a popup form to view the entire verse since it is a memo field. I
hope that Helps.

"Jeff Boyce" wrote:

I'm one of those who ask "why"...

If I'm interpreting your SQL statement correctly, you have a table [tStudy]
that has three different fields ([Paste verses], [Paste verses1], [Paste
verses2]) that contain the same kind of data. "Repeating fields" like this
are typically found ... in spreadsheets.

When you have to deal with them in a relational database, you (and Access)
have to work harder to come up with a work-around -- Access expects
well-normalized data, and repeating fields are not.

If you don't take another look at your table structure and normalize it, you
will have to modify it, and your queries, forms, reports, macros, code, etc.
every time you change the number of [Paste verses#] you wish to deal with.
Are you willing to do all that maintenance, or to force whoever maintains
this application after you to do it?

You can "pay now, or pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"BrianPaul" wrote in message
...
SELECT [Subject], [Paste verses]
FROM [tstudy]

UNION ALL SELECT [subject], [paste verses1]
FROM [Tstudy];

UNION ALL SELECT[subject], [paste verses2]
FROM [Tstudy];

Why I need to do after this is add an autonumber So I can use it in a form
based on the union query that will allow me to doubleclick on the record
and
take me to the form from the Main Menu. (Usually you get the why do you
want
to do it that way question) Thanks, Another solution I tried was to
create
another query based on my union query, then add a field that will count
subquentially the field. Keep in mind I just need an uniqe number there
to
use as a referance for the list box. If I put the autokey field in both
statements in the statement above, I get duplicate numbers and I dont want
that. Thanks, any help would be greatly appreciated using acccess 2003

From your additional information, it sure sounds like you have a
candidate for normalization! (How would you like to be able to handle 5
verses, or 28 verses, or 65 verses, or ... equally as easily?) That's
what Access is *very* good at, but only if the data structure is
normalized.

Several here will strongly encourage you to follow this path ... there
is a learning curve, but as Jeff said, you can "pay now, or pay
later...."

Thinking out loud: If you change your second listbox to a sub-form in
continuous form view (you can use datasheet view for learning and
testing; but sooner or later you'll likely run into something that's
difficult to do in datasheet view but quite easy in continuous form
iew -- so the extra setup effort for continuous form view becomes well
worth while) you can make the sub-form appear quite similiar to your
list-box with the advantage that you can select the appropriate row and
column in your continuous form and use [shift + F2] to open up the
built-in zoom box. (Or write a double-click event in the sub-form to
open up the zoom box from code.)


For resources on learning about Access and relational database design
(ie, normalization) read on:
--
(Thanks to John W. Vinson [MVP] for this info
"Access has a steeper learning curve than (say) Word or Excel; not
least, in order to make productive use of the program you have to
understand the theoretical basis of database design - a concept called
'Normalization'. It is very logical and not at all difficult once you
get the concepts down."

Here are some tutorials and other resources that you should find
helpful:

A free tutorial written by Crystal (MS Access MVP):
http://www.accessmvp.com/Strive4Peace/Index.htm
also at http://allenbrowne.com/casu-22.htmlMVP

Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Here's a primer with 23 well defined, well written, clearly named
chapters:
http://www.functionx.com/vbaccess/index.htm

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

Access MVP Larry Linson has additional Access resources in a list at:
http://sp.ntpcug.org/accesssig/default.aspx

From Access MVP Tom Wickerath:
May I recommend that you help jumpstart your Access-related knowledge by
downloading a copy of a Word document that I have available in zipped
form? I
call it "Access Links". The first four pages include important
information
that anyone working with Access should be aware of. This includes
reserved
words and special characters, naming conventions, database design, etc.
My
advice is to avoid using any reserved words (Name, Date & Description
are
three prime examples) or special characters (#, $, spaces, etc.) in
anything
that you assign a name to within Access.
http://www.accessmvp.com/TWickerath/

--
Clif


"BrianPaul" wrote in message
...
Sorry, I should have mentioned. I need a field only created at
runtime and
not a stored value. I thought of having it make a new table and
adding an
autonumber, then when I went to the menu on open. then it would delete
the
existing table and create a new, or append but dont have the knowledge
to do
that. Thought this would be easier to do.

Thanks,

"BrianPaul" wrote:

Yep, There are actually 29 of them that paste verses 1, paste verses
2, ect.
I only listed the first 2. The database is used for bible study on a
particular subject, which is a field. Then comment1, comment2, ect,
but will
only allow 29 comment and 29 paste verses for the subject (Keep in
mind these
are memo fields). However, would like to combine the verses, which I
did
successfully with the union querry. However, when I created another
form,
put 2 list boxes on the form, The first list box updates the 2nd
listbox
based on the subject. Anotherwords, selecting the subject in the
first list
box will update the listbox in the 2nd one based on the subject.
However, If
I want to see the entire verses which can be over 255 characters
(reason its
a memo) I need to double click on the list box to bring up another
form
which is a popup form to view the entire verse since it is a memo
field. I
hope that Helps.

"Jeff Boyce" wrote:

I'm one of those who ask "why"...

If I'm interpreting your SQL statement correctly, you have a table
[tStudy]
that has three different fields ([Paste verses], [Paste verses1],
[Paste
verses2]) that contain the same kind of data. "Repeating fields"
like this
are typically found ... in spreadsheets.

When you have to deal with them in a relational database, you (and
Access)
have to work harder to come up with a work-around -- Access expects
well-normalized data, and repeating fields are not.

If you don't take another look at your table structure and
normalize it, you
will have to modify it, and your queries, forms, reports, macros,
code, etc.
every time you change the number of [Paste verses#] you wish to
deal with.
Are you willing to do all that maintenance, or to force whoever
maintains
this application after you to do it?

You can "pay now, or pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"BrianPaul" wrote in message
...
SELECT [Subject], [Paste verses]
FROM [tstudy]

UNION ALL SELECT [subject], [paste verses1]
FROM [Tstudy];

UNION ALL SELECT[subject], [paste verses2]
FROM [Tstudy];

Why I need to do after this is add an autonumber So I can use it
in a form
based on the union query that will allow me to doubleclick on the
record
and
take me to the form from the Main Menu. (Usually you get the why
do you
want
to do it that way question) Thanks, Another solution I tried
was to
create
another query based on my union query, then add a field that will
count
subquentially the field. Keep in mind I just need an uniqe
number there
to
use as a referance for the list box. If I put the autokey field
in both
statements in the statement above, I get duplicate numbers and I
dont want
that. Thanks, any help would be greatly appreciated using
acccess 2003






--
Clif

It sounds like you've decided to "pay later", and keep your work-around.

Hopefully one of the other newsgroup readers will be able to help ... I
don't have any experience with this kind of a work-around.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"BrianPaul" wrote in message
...
Yep, There are actually 29 of them that paste verses 1, paste verses 2,
ect.
I only listed the first 2. The database is used for bible study on a
particular subject, which is a field. Then comment1, comment2, ect, but
will
only allow 29 comment and 29 paste verses for the subject (Keep in mind
these
are memo fields). However, would like to combine the verses, which I did
successfully with the union querry. However, when I created another form,
put 2 list boxes on the form, The first list box updates the 2nd listbox
based on the subject. Anotherwords, selecting the subject in the first
list
box will update the listbox in the 2nd one based on the subject. However,
If
I want to see the entire verses which can be over 255 characters (reason
its
a memo) I need to double click on the list box to bring up another form
which is a popup form to view the entire verse since it is a memo field.
I
hope that Helps.

"Jeff Boyce" wrote:

I'm one of those who ask "why"...

If I'm interpreting your SQL statement correctly, you have a table
[tStudy]
that has three different fields ([Paste verses], [Paste verses1], [Paste
verses2]) that contain the same kind of data. "Repeating fields" like
this
are typically found ... in spreadsheets.

When you have to deal with them in a relational database, you (and
Access)
have to work harder to come up with a work-around -- Access expects
well-normalized data, and repeating fields are not.

If you don't take another look at your table structure and normalize it,
you
will have to modify it, and your queries, forms, reports, macros, code,
etc.
every time you change the number of [Paste verses#] you wish to deal
with.
Are you willing to do all that maintenance, or to force whoever maintains
this application after you to do it?

You can "pay now, or pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"BrianPaul" wrote in message
...
SELECT [Subject], [Paste verses]
FROM [tstudy]

UNION ALL SELECT [subject], [paste verses1]
FROM [Tstudy];

UNION ALL SELECT[subject], [paste verses2]
FROM [Tstudy];

Why I need to do after this is add an autonumber So I can use it in a
form
based on the union query that will allow me to doubleclick on the
record
and
take me to the form from the Main Menu. (Usually you get the why do you
want
to do it that way question) Thanks, Another solution I tried was to
create
another query based on my union query, then add a field that will count
subquentially the field. Keep in mind I just need an uniqe number
there
to
use as a referance for the list box. If I put the autokey field in
both
statements in the statement above, I get duplicate numbers and I dont
want
that. Thanks, any help would be greatly appreciated using acccess 2003

I thought about that also at one time. Your telling me in anotherwords
instead of using a second list box, use a subform, When I click on the
subject, (code will have to be written on the click event property) in the
list box, It will sync up with the subform based on the union query subject,
and verses and will show me the verses which can be up to 28 I believe. I
could even put a record navigation buttion to go through each record of
them. However, my question then would be how would I sync the first list box
containing the subject to the subform which has the subject and the verses
but are based on Memo fields? Usually my lists boxes have autonumbers that
are part of the tables as the uniqe value. and I set there with to 0. So you
dont see the numbers in the listbox when you open the forms. Where I got
caught in this instance was when I created the fields in the table, I limited
it to 28 I believe verses, but then had to recombine them. Keep in mind no
data will be entered through these form, just displayed with records locked.

"Jeff Boyce" wrote:

It sounds like you've decided to "pay later", and keep your work-around.

Hopefully one of the other newsgroup readers will be able to help ... I
don't have any experience with this kind of a work-around.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"BrianPaul" wrote in message
...
Yep, There are actually 29 of them that paste verses 1, paste verses 2,
ect.
I only listed the first 2. The database is used for bible study on a
particular subject, which is a field. Then comment1, comment2, ect, but
will
only allow 29 comment and 29 paste verses for the subject (Keep in mind
these
are memo fields). However, would like to combine the verses, which I did
successfully with the union querry. However, when I created another form,
put 2 list boxes on the form, The first list box updates the 2nd listbox
based on the subject. Anotherwords, selecting the subject in the first
list
box will update the listbox in the 2nd one based on the subject. However,
If
I want to see the entire verses which can be over 255 characters (reason
its
a memo) I need to double click on the list box to bring up another form
which is a popup form to view the entire verse since it is a memo field.
I
hope that Helps.

"Jeff Boyce" wrote:

I'm one of those who ask "why"...

If I'm interpreting your SQL statement correctly, you have a table
[tStudy]
that has three different fields ([Paste verses], [Paste verses1], [Paste
verses2]) that contain the same kind of data. "Repeating fields" like
this
are typically found ... in spreadsheets.

When you have to deal with them in a relational database, you (and
Access)
have to work harder to come up with a work-around -- Access expects
well-normalized data, and repeating fields are not.

If you don't take another look at your table structure and normalize it,
you
will have to modify it, and your queries, forms, reports, macros, code,
etc.
every time you change the number of [Paste verses#] you wish to deal
with.
Are you willing to do all that maintenance, or to force whoever maintains
this application after you to do it?

You can "pay now, or pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"BrianPaul" wrote in message
...
SELECT [Subject], [Paste verses]
FROM [tstudy]

UNION ALL SELECT [subject], [paste verses1]
FROM [Tstudy];

UNION ALL SELECT[subject], [paste verses2]
FROM [Tstudy];

Why I need to do after this is add an autonumber So I can use it in a
form
based on the union query that will allow me to doubleclick on the
record
and
take me to the form from the Main Menu. (Usually you get the why do you
want
to do it that way question) Thanks, Another solution I tried was to
create
another query based on my union query, then add a field that will count
subquentially the field. Keep in mind I just need an uniqe number
there
to
use as a referance for the list box. If I put the autokey field in
both
statements in the statement above, I get duplicate numbers and I dont
want
that. Thanks, any help would be greatly appreciated using acccess 2003

"BrianPaul" wrote in message
news
I thought about that also at one time. Your telling me in anotherwords
instead of using a second list box, use a subform, When I click on
the
subject, (code will have to be written on the click event property) in
the
list box, It will sync up with the subform based on the union query
subject,
and verses and will show me the verses which can be up to 28 I
believe.

Yes, that's the general idea. How are you populating your second
listbox? Surely you are using code?

One approach (I still recommend properly normalized structure) would be
to use your union query as the recordsource for your subform, and use
the sub-form's filter properties to display only records with the
selected subject (do you have an OrderBy clause in your union query?).

(In a code module, type Me.Filter and press F1 to bring up the help
topic)

In your click event code, try something like this aircode:

dim strWHERE as string
strWHERE = BuildCriteria("[Subject]", dbText, Me.cboSubject.Value)
'debug.print strWHERE
With Me.NameOfSubformControl.Form
.Filter = strWHERE
.FilterOn = True
End With


I
could even put a record navigation buttion to go through each record
of
them. However, my question then would be how would I sync the first
list box
containing the subject to the subform which has the subject and the
verses
but are based on Memo fields?

If your subform control is big enough to display all lines you wouldn't
need the navigation buttons. I haven't tried this, but if your subform
is big enough to display the most verses you'll ever encounter and your
subform control isn't (for instance, you want to limit the 'drop down'
to 8 lines) you might be able to set the sub-form's vertical scroll
property and not need the navigation buttons.

Usually my lists boxes have autonumbers that
are part of the tables as the uniqe value. and I set there with to 0.
So you
dont see the numbers in the listbox when you open the forms.


Sorry, you lost me there. Setting width to 0 I understand; but I'm not
grasping what you're doing with the autonumbers here.

Where I got
caught in this instance was when I created the fields in the table, I
limited
it to 28 I believe verses, but then had to recombine them. Keep in
mind no
data will be entered through these form, just displayed with records
locked.

Which gets us back to the normalization recommendation!

Good luck!

--
Clif


"Jeff Boyce" wrote:

It sounds like you've decided to "pay later", and keep your
work-around.

Hopefully one of the other newsgroup readers will be able to help ...
I
don't have any experience with this kind of a work-around.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"BrianPaul" wrote in message
...
Yep, There are actually 29 of them that paste verses 1, paste
verses 2,
ect.
I only listed the first 2. The database is used for bible study on
a
particular subject, which is a field. Then comment1, comment2,
ect, but
will
only allow 29 comment and 29 paste verses for the subject (Keep in
mind
these
are memo fields). However, would like to combine the verses, which
I did
successfully with the union querry. However, when I created
another form,
put 2 list boxes on the form, The first list box updates the 2nd
listbox
based on the subject. Anotherwords, selecting the subject in the
first
list
box will update the listbox in the 2nd one based on the subject.
However,
If
I want to see the entire verses which can be over 255 characters
(reason
its
a memo) I need to double click on the list box to bring up another
form
which is a popup form to view the entire verse since it is a memo
field.
I
hope that Helps.

"Jeff Boyce" wrote:

I'm one of those who ask "why"...

If I'm interpreting your SQL statement correctly, you have a table
[tStudy]
that has three different fields ([Paste verses], [Paste verses1],
[Paste
verses2]) that contain the same kind of data. "Repeating fields"
like
this
are typically found ... in spreadsheets.

When you have to deal with them in a relational database, you (and
Access)
have to work harder to come up with a work-around -- Access
expects
well-normalized data, and repeating fields are not.

If you don't take another look at your table structure and
normalize it,
you
will have to modify it, and your queries, forms, reports, macros,
code,
etc.
every time you change the number of [Paste verses#] you wish to
deal
with.
Are you willing to do all that maintenance, or to force whoever
maintains
this application after you to do it?

You can "pay now, or pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"BrianPaul" wrote in message
...
SELECT [Subject], [Paste verses]
FROM [tstudy]

UNION ALL SELECT [subject], [paste verses1]
FROM [Tstudy];

UNION ALL SELECT[subject], [paste verses2]
FROM [Tstudy];

Why I need to do after this is add an autonumber So I can use it
in a
form
based on the union query that will allow me to doubleclick on
the
record
and
take me to the form from the Main Menu. (Usually you get the why
do you
want
to do it that way question) Thanks, Another solution I tried
was to
create
another query based on my union query, then add a field that
will count
subquentially the field. Keep in mind I just need an uniqe
number
there
to
use as a referance for the list box. If I put the autokey field
in
both
statements in the statement above, I get duplicate numbers and I
dont
want
that. Thanks, any help would be greatly appreciated using
acccess 2003









--
Clif

Clif

Some folks just want to know what button to push...g

Jeff

"Clif McIrvin" wrote in message
...
"BrianPaul" wrote in message
news
I thought about that also at one time. Your telling me in anotherwords
instead of using a second list box, use a subform, When I click on the
subject, (code will have to be written on the click event property) in
the
list box, It will sync up with the subform based on the union query
subject,
and verses and will show me the verses which can be up to 28 I believe.

Yes, that's the general idea. How are you populating your second listbox?
Surely you are using code?

One approach (I still recommend properly normalized structure) would be to
use your union query as the recordsource for your subform, and use the
sub-form's filter properties to display only records with the selected
subject (do you have an OrderBy clause in your union query?).

(In a code module, type Me.Filter and press F1 to bring up the help topic)

In your click event code, try something like this aircode:

dim strWHERE as string
strWHERE = BuildCriteria("[Subject]", dbText, Me.cboSubject.Value)
'debug.print strWHERE
With Me.NameOfSubformControl.Form
.Filter = strWHERE
.FilterOn = True
End With


I
could even put a record navigation buttion to go through each record of
them. However, my question then would be how would I sync the first list
box
containing the subject to the subform which has the subject and the
verses
but are based on Memo fields?

If your subform control is big enough to display all lines you wouldn't
need the navigation buttons. I haven't tried this, but if your subform is
big enough to display the most verses you'll ever encounter and your
subform control isn't (for instance, you want to limit the 'drop down' to
8 lines) you might be able to set the sub-form's vertical scroll property
and not need the navigation buttons.

Usually my lists boxes have autonumbers that
are part of the tables as the uniqe value. and I set there with to 0. So
you
dont see the numbers in the listbox when you open the forms.


Sorry, you lost me there. Setting width to 0 I understand; but I'm not
grasping what you're doing with the autonumbers here.

Where I got
caught in this instance was when I created the fields in the table, I
limited
it to 28 I believe verses, but then had to recombine them. Keep in mind
no
data will be entered through these form, just displayed with records
locked.

Which gets us back to the normalization recommendation!

Good luck!

--
Clif


"Jeff Boyce" wrote:

It sounds like you've decided to "pay later", and keep your work-around.

Hopefully one of the other newsgroup readers will be able to help ... I
don't have any experience with this kind of a work-around.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"BrianPaul" wrote in message
...
Yep, There are actually 29 of them that paste verses 1, paste verses
2,
ect.
I only listed the first 2. The database is used for bible study on a
particular subject, which is a field. Then comment1, comment2, ect,
but
will
only allow 29 comment and 29 paste verses for the subject (Keep in
mind
these
are memo fields). However, would like to combine the verses, which I
did
successfully with the union querry. However, when I created another
form,
put 2 list boxes on the form, The first list box updates the 2nd
listbox
based on the subject. Anotherwords, selecting the subject in the
first
list
box will update the listbox in the 2nd one based on the subject.
However,
If
I want to see the entire verses which can be over 255 characters
(reason
its
a memo) I need to double click on the list box to bring up another
form
which is a popup form to view the entire verse since it is a memo
field.
I
hope that Helps.

"Jeff Boyce" wrote:

I'm one of those who ask "why"...

If I'm interpreting your SQL statement correctly, you have a table
[tStudy]
that has three different fields ([Paste verses], [Paste verses1],
[Paste
verses2]) that contain the same kind of data. "Repeating fields"
like
this
are typically found ... in spreadsheets.

When you have to deal with them in a relational database, you (and
Access)
have to work harder to come up with a work-around -- Access expects
well-normalized data, and repeating fields are not.

If you don't take another look at your table structure and normalize
it,
you
will have to modify it, and your queries, forms, reports, macros,
code,
etc.
every time you change the number of [Paste verses#] you wish to deal
with.
Are you willing to do all that maintenance, or to force whoever
maintains
this application after you to do it?

You can "pay now, or pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"BrianPaul" wrote in message
...
SELECT [Subject], [Paste verses]
FROM [tstudy]

UNION ALL SELECT [subject], [paste verses1]
FROM [Tstudy];

UNION ALL SELECT[subject], [paste verses2]
FROM [Tstudy];

Why I need to do after this is add an autonumber So I can use it in
a
form
based on the union query that will allow me to doubleclick on the
record
and
take me to the form from the Main Menu. (Usually you get the why do
you
want
to do it that way question) Thanks, Another solution I tried was
to
create
another query based on my union query, then add a field that will
count
subquentially the field. Keep in mind I just need an uniqe number
there
to
use as a referance for the list box. If I put the autokey field in
both
statements in the statement above, I get duplicate numbers and I
dont
want
that. Thanks, any help would be greatly appreciated using acccess
2003









--
Clif

"Jeff Boyce" wrote in message
...
Clif

Some folks just want to know what button to push...g

Jeff


Yeah, I've noticed that.

Guess I'm (still) hoping we've got someone willing to learn on the other
end.

Thanks grin

--
Clif