First let me thank John and Bob for their time addressing this issue.
(Bob) asked Is the Car# an identifier for the car, or a count of cars?
Answer:
Trains can have 1 to 6 cars each. A car is counted by its "car#" the car #'s
assigned to the cars are painted on the cars like cop cars have #'s So a
spotter would list each car as an entry. Each as a line entry on their paper
form. Below is an example of a paper form with 3 trains inbound, 1 with 3
cars on it and 2 with 1 car each.
example INBOUND paper stat sheet example:
Spotter Name_Joe_Blow__ Route#__North East_______ Station__NE-2______
(INXXXXXXXND) or (Outbound) circle one please
Time work started______4:40_____ Time work stoped___13:30________
Begin Data:
1.) car #= 243 route#= 345 Destination = n-2 PASS =34 time 04:45
2.) car #= 276 route#= 345 Destination = n-2 PASS =22 time 04:45
3.) car #= 543 route #= Destination = n-2 PASS =31 time 04:45
4.) car #= 222 route #= Destination = AIR PASS =22 time 04: 51
5.) car #= 125 route #= Destination = P-4 PASS =15 tome 04:55
They know the above on line 1,2&3 is the same train and has 3 cars. The TIME
recorded being all the same shows that is one train.
A query here would be "How many passengers did the 4:45 inbound to NE-2 have
on it?" Next query: How many passengers did it have when it left?? What is
the diff in passengers?
The lady who enters the data is use to doing it her way....nuf said.....
I had to create 2 forms that mirror the old patteron of data entry. One form
labled inbound entry and one form labled for outbound entry. Same form just
diff tables.
The writen Data sheets are just a unsorted pile, without reguard to time
train line or station omly a pile of inbound or outbound.
I have created forms that copy her old "entry pattern" from dbase lll the
way she was use to doing it. She did all inbound first and all outbound
second. Opening a diff dbIII file for either one. She wants to not have to
say inbound or outbound when she enters data. "She did not have to do that
before an this should not be any harder infact it should save her work" She
use to open c:\inbound or c:\outbound and then did her entrys.
So she wants a form for inbound and one for outbound.
Any advise would be very helpful
the reports want to show Inbound / outbound and together
INBOUND and OUTBOUND
total Passengers=111435
total trips = 169 (trip= counted as either inbound or outbound) 1 car in
then leaving counts as 2 trips unless destination = N/R non rev.)
Passengers per trip= 111435pass / 169trips
Load factor= 0.32 = 65 persons per car= 100% load
INBOUND
total Passengers=70432
total trips = 100 (trip= counted as either inbound or outbound) 1 car in
then leaving counts as 2 trips unless destination = N/R non rev.)
Passengers per trip= 170432pass / 100trips
Load factor= 0.32 = 65 persons per car= 100% load
then outbound
then report by the hour by the line by the station by the car
AAAAHHHHH!!!!!!
Any help? I am really trying but have forgot how to do all this.....
John W. Vinson said:
I have a db that should track trains,
Two tables one for Inbound trains and one for outbound trains. Named
(Passenger Check Inbound) the other (Passenger Check Outbound) each table has
the same fields. as listed below
Having two identically structured tables is part of the problem. It might be
better to have ONE table with an additional field - perhaps a Yes/No field
[Inbound]. A self-join query will make life easier than trying to match up two
different tables.
1.)Train line,= [north, south, east, north east, & west]
2.) Stations,= i.e. North Stations (n-1 through n-11) South Station (s-1
through s-7)
3.) Car #= any number
Is the Car# an identifier for the car, or a count of cars?
4.) route # = any data
5.) Destination = any data
6.) number of passengers,= number
7.)# of trips (trip is in and out bound) same train coming in= 1 trip and
going out = 1 trip = number
This would be easier with one table.
8.) date (1/22/2009) format
9.) TIME, 24HR FORM
You will probably want instead to use a Date/Time field, or (if you use one
table) two fields - ArrivalTime and DepartureTime. The format is irrelevant,
it just controls how the data is displayed, not what's stored.
You need to be clear what Entity - real-life person, thing or event - is
modeled by your table. It would appear that the entity isn't in fact a train
but rather a Stop (one train stopping once at one station), right? Do you need
to track trains themselves, or cars, or just "timetable" type information?
I need to be able to add the total passengers from each table, then add them
together for a total # of passengers.
I am trying to do this through a query. I really have no idea how to do this
I have tried things like listed below. Maybe I just don't get it,,,really
frustrated.....Keith
[dsum passenger check outbound].[pass]+[DSum passenger check inbound].[pass]
DSum() is a VBA function, not a fieldname. Just typing [dsum passenger check
inbound] in brackets won't tell Access to DO anything!
I think what you want is a Totals Query. Create a Query based on your inbound
table; select the pass field (passengers?) and the date field(s).
Change the query to a Totals query by clicking on the Greek Sigma icon (looks
like a sideways M). A new Total row will appear in the query grid.
Change the default Group By to Sum under Pass, and to Where under the date
field.
Put any desired criteria on the Criteria line under the date field - for
instance, if you want a count of passengers on a particular date, type
= [Enter date:] AND < DateAdd("d", 1, [Enter date:])
on the criteria line.
If you want subtotals for each station, include the station field and leave
the default Group By.
If you persist in using two different tables you'll need a (slower and more
complex) UNION query to stitch the two tables together.
The totals query has a lot of options and a lot of flexibility - play around
with it a bit, and post back if you have problems.
