More than one record per "row" in form

[pepandmax]

Good evening:

I'm not sure what title to use for this post, so I'll try to explain
as clearly as possible what I need to do. This is for a database of
transit schedules, by the way.

I have the following table (with some sample data):

tblStopTimes:
StopTimeID TripID StopSeq StopID StopTime
1 60 1 209 8:30
2 60 2 384 8:37
3 60 3 587 8:40
4 61 1 209 8:45
5 61 2 384 8:50
6 61 3 587 9:00

It would be easy to set up a form that looks pretty much like the
above table, but that's not a format that is familiar to people in the
transit industry. Hence, I need a form that looks like this:

TripID Stop209 Stop384 Stop587
60 8:30 8:37 8:40
61 8:45 8:50 9:00

In other words, I need each of the cells in the form to save to one
row in the table. Conversely, each row of the form needs to save to
multiple rows in the table.

I have no idea if I'm explaining this well. I'm fairly new to Access
and completely self-taught (I know-the worst kind), so forgive me if
I'm not using terminology correctly. Thank you in advance for any
help that you might be able to give me.

Regards
Nicole

 

[Bob Quintal]

(e-mail address removed) wrote in

Good evening:

I'm not sure what title to use for this post, so I'll try to
explain as clearly as possible what I need to do. This is for
a database of transit schedules, by the way.

I have the following table (with some sample data):

tblStopTimes:
StopTimeID TripID StopSeq StopID StopTime
1 60 1 209 8:30
2 60 2 384 8:37
3 60 3 587 8:40
4 61 1 209 8:45
5 61 2 384 8:50
6 61 3 587 9:00

It would be easy to set up a form that looks pretty much like
the above table, but that's not a format that is familiar to
people in the transit industry. Hence, I need a form that
looks like this:

TripID Stop209 Stop384 Stop587
60 8:30 8:37 8:40
61 8:45 8:50 9:00

In other words, I need each of the cells in the form to save
to one row in the table. Conversely, each row of the form
needs to save to multiple rows in the table.

I have no idea if I'm explaining this well. I'm fairly new to
Access and completely self-taught (I know-the worst kind), so
forgive me if I'm not using terminology correctly. Thank you
in advance for any help that you might be able to give me.

Regards
Nicole

Hi Nicole,

Investigate the use of a crosstab query to pivot the stopID
numbers from rows to columns. there is a wizard to help.

In your example, you would use tripID as a row heading, ["Stop"
& StopID] as the Column heading, and first(stoptime) as the
value.

The only possible issue is that the maximum number of columns is
255, so you could show 254 stops.

 

[Lady G]

Bob,

Thanks for your reply! It worked like a charm, except for two
things. Instead of times showing up as the value, integers are
showing up. For example, instead of 7:30 showing up, 7 does. Instead
of 7:32 showing up, 4 does. Not 7:35, but 6. Any suggestions?

Now, the next step is that I need to create a form in the format of
this crosstab query for entering schedule data. How do I do that?

Thank you again for your help!

Nicole

 

[Bob Quintal]

Bob,

Thanks for your reply! It worked like a charm, except for two
things. Instead of times showing up as the value, integers
are showing up. For example, instead of 7:30 showing up, 7
does. Instead of 7:32 showing up, 4 does. Not 7:35, but 6.
Any suggestions?

When you open the query in design view, do you have count
instead of first as the text in the Total: row?

Now, the next step is that I need to create a form in the
format of this crosstab query for entering schedule data. How
do I do that?

Sorry, crosstabs are by definition, read-only.

Thank you again for your help!

Nicole

 

[Peter Hibbs]

Nicole

You could use a Flex Grid control to display the trip times. Have a
look at :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'
for some examples. If you are interested in using this option post
back and I can send you a demo Db of what you are trying to do
(written in A2000).

HTH

Peter Hibbs.