How to set this up?

[Ed from AZ]

I am an extreme novice with Access. I tried to set this up with
Excel, but I really think it's just too much and needs a database.
Several people will be using this, and I know more than one will try
to use it at the same time.

We have a fleet of vehicles and several routes. We want to take the
operator's daily logs and record how many times each vehicle covered
any route. The routes covered and the number of times any route is
covered varies daily and is different for each vehicle. Each vehicle
has a unique identifier, and each route has a unique code. We want to
generate reports to show where each vehicle went over any given time
span, which vehicles covered any specified route over any given time
span, comparisons of route acitivity over any given time span, and
probably a few other variations that management hasn't thought of yet.

I can see a table of vehicles by type ans serial number and a table of
routes. The data input form would have a drop-down for vehicle type,
then a drop-down for the serial numbers of vehicles of that type, a
date field, and then a series of drop-downs to allow the user to
choose the route and input how many times that route was covered that
day by that vehicle.

How do you set up the master data table? By vehicle or by route? How
do you create a key field that will tie together vehicle, route, date,
and data? Every time I try to visualize this, I see a three-axis
table - vehicle, route, and date - and my brain grinds to a halt! Any
help is greatly appreciated!!

Ed

 

[jon]

Hi Ed,

There doesn't seem to be any guru's answering today, so I'll have a go, but
I'm no expert.

I think you need 3 tables.
1) Vehicle details, perhaps columns such as Vehicle_ID, Vehicle _Type (Where
Vehicle Type could contain Manned/Drone)
2) Route details, perhaps columns such as Route_ID,Route_Type ( where
Route_Type could contain AIR/SEA/LAND )
3) Log details, perhaps columns such as
Operator_ID,Vehicle_ID,Route_ID,Log_date

I think you only need the log details table to produce your reports but the
others will help later if your superiors want more detailed reporting.

You create a query with columns from the log details table, and in the
criteria for log_date you need a between statement such as

between [What is the reporting start date] and [What is the reporting end
date]
The line above is exactly as you would need to enter it if you want it to
prompt you for the dates.
Alternatively you could use something like between date()-30 and date()
which would show results from the last 30 days.

The is query can now be saved as it will filter out the lines we are
interested in.

Then create a second query based upon the first query, and add all columns
except the date column.

click the epsilon button when in design view to group all the columns, then
change one such as operator_id to count.

Running this would give you a list showing 1 line for each vehicle and route
with a count showing how many times that vehicle had done that route in the
date range you had specified.

Later you could add fields such as Conflict, with yes or no values in you
log table, then you could have 2 lines for the vehicle showing how many
times it had done the route, and how many times it had been shot at.

hope this helps

Jon.

 

[BruceM]

I think the LogDetails table is essential to the design rather than a
convenience for reporting. Each Vehicle can go on many routes, and each
route can be taken by many vehicles over time, so LogDetails serves as a
junction table to allow this many-to-many relationship. LogDetails is the
Vehicle/Route table, containing the record of a particular vehicle traveling
a particular route on a particular day (or at a particular time if that
vehicle can travel the route more than once per day).

The thing to remember is that each table stores information about a single
entity such as Vehicle or Route. There is not necessarily a Master table as
such, but rather a choice of how to model the relationships. For instance,
you could have a Vehicles table (tblVehicle), and a form (frmVehicle) based
on the tblVehicle (or on a query based on the table). Then there would be
another form based on tblRoute, which contains Route information including
the RouteCode. This is where you set up your routes.

The form frmVehicle would have a subform based on tblLog (the LogDetails
table as described above). It could be a continuous subform to enable you
to see several routes traveled by that vehicle. The subform would have a
combo box that gets its Row Source from tblRoute.

You may also need an Employee or Driver table (tblEmployee), with a unique
identifier such as EmployeeID for each employee record. A combo box on the
subform could be used to select the driver.

This would allow flexible reporting that could show all of the routes a
vehicle traveled, all of the vehicles that traveled a route, all of the
trips taken by a driver, and so forth.

There is a lot to consider with such a project. Access is very flexible and
powerful, but because you are creating an application and for other reasons
it has a steep learning curve. You may do well to review this tutorial:
http://allenbrowne.com/casu-22.html

This page is well worth checking too:
http://allenbrowne.com/tips.html

It includes a Links link on the right side of the page, with links to more
information and tutorials.

Hi Ed,

There doesn't seem to be any guru's answering today, so I'll have a go,
but I'm no expert.

I think you need 3 tables.
1) Vehicle details, perhaps columns such as Vehicle_ID, Vehicle _Type
(Where Vehicle Type could contain Manned/Drone)
2) Route details, perhaps columns such as Route_ID,Route_Type ( where
Route_Type could contain AIR/SEA/LAND )
3) Log details, perhaps columns such as
Operator_ID,Vehicle_ID,Route_ID,Log_date

I think you only need the log details table to produce your reports but
the others will help later if your superiors want more detailed reporting.

You create a query with columns from the log details table, and in the
criteria for log_date you need a between statement such as

between [What is the reporting start date] and [What is the reporting end
date]
The line above is exactly as you would need to enter it if you want it to
prompt you for the dates.
Alternatively you could use something like between date()-30 and date()
which would show results from the last 30 days.

The is query can now be saved as it will filter out the lines we are
interested in.

Then create a second query based upon the first query, and add all columns
except the date column.

click the epsilon button when in design view to group all the columns,
then change one such as operator_id to count.

Running this would give you a list showing 1 line for each vehicle and
route with a count showing how many times that vehicle had done that route
in the date range you had specified.

Later you could add fields such as Conflict, with yes or no values in you
log table, then you could have 2 lines for the vehicle showing how many
times it had done the route, and how many times it had been shot at.

hope this helps

Jon.

I am an extreme novice with Access. I tried to set this up with
Excel, but I really think it's just too much and needs a database.
Several people will be using this, and I know more than one will try
to use it at the same time.

We have a fleet of vehicles and several routes. We want to take the
operator's daily logs and record how many times each vehicle covered
any route. The routes covered and the number of times any route is
covered varies daily and is different for each vehicle. Each vehicle
has a unique identifier, and each route has a unique code. We want to
generate reports to show where each vehicle went over any given time
span, which vehicles covered any specified route over any given time
span, comparisons of route acitivity over any given time span, and
probably a few other variations that management hasn't thought of yet.

I can see a table of vehicles by type ans serial number and a table of
routes. The data input form would have a drop-down for vehicle type,
then a drop-down for the serial numbers of vehicles of that type, a
date field, and then a series of drop-downs to allow the user to
choose the route and input how many times that route was covered that
day by that vehicle.

How do you set up the master data table? By vehicle or by route? How
do you create a key field that will tie together vehicle, route, date,
and data? Every time I try to visualize this, I see a three-axis
table - vehicle, route, and date - and my brain grinds to a halt! Any
help is greatly appreciated!!

Ed

 

[Ed from AZ]

Thank you, Bruce!! I think that tutorial is just what I was looking
for to get me started _correctly_! I've muddled my way through one or
two database projects, but not very successfully and not without an
exceptional amount of hand-holding. I realize this one is quite a bit
for a beginner, but it's what there is on the plate to do, so . . .

Ed

I think the LogDetails table is essential to the design rather than a
convenience for reporting.  Each Vehicle can go on many routes, and each
route can be taken by many vehicles over time, so LogDetails serves as a
junction table to allow this many-to-many relationship.  LogDetails is the
Vehicle/Route table, containing the record of a particular vehicle traveling
a particular route on a particular day (or at a particular time if that
vehicle can travel the route more than once per day).

The thing to remember is that each table stores information about a single
entity such as Vehicle or Route.  There is not necessarily a Master table as
such, but rather a choice of how to model the relationships.  For instance,
you could have a Vehicles table (tblVehicle), and a form (frmVehicle) based
on the tblVehicle (or on a query based on the table).  Then there wouldbe
another form based on tblRoute, which contains Route information including
the RouteCode.  This is where you set up your routes.

The form frmVehicle would have a subform based on tblLog (the LogDetails
table as described above).  It could be a continuous subform to enable you
to see several routes traveled by that vehicle.  The subform would havea
combo box that gets its Row Source from tblRoute.

You may also need an Employee or Driver table (tblEmployee), with a unique
identifier such as EmployeeID for each employee record.  A combo box onthe
subform could be used to select the driver.

This would allow flexible reporting that could show all of the routes a
vehicle traveled, all of the vehicles that traveled a route, all of the
trips taken by a driver, and so forth.

There is a lot to consider with such a project.  Access is very flexible and
powerful, but because you are creating an application and for other reasons
it has a steep learning curve.  You may do well to review this tutorial:http://allenbrowne.com/casu-22.html

This page is well worth checking too:http://allenbrowne.com/tips.html

It includes a Links link on the right side of the page, with links to more
information and tutorials.

Hi Ed,

There doesn't seem to be any guru's answering today, so I'll have a go,
but I'm no expert.

I think you need 3 tables.
1) Vehicle details, perhaps columns such as Vehicle_ID, Vehicle _Type
(Where Vehicle Type could contain Manned/Drone)
2) Route details, perhaps columns such as Route_ID,Route_Type ( where
Route_Type could contain  AIR/SEA/LAND )
3) Log details, perhaps columns such as
Operator_ID,Vehicle_ID,Route_ID,Log_date

I think you only need the log details table to produce your reports but
the others will help later if your superiors want more detailed reporting.

You create a query with columns from the log details table, and in the
criteria for log_date you need a between statement such as

between [What is the reporting start date] and [What is the reporting end
date]
The line above is exactly as you would need to enter it if you want it to
prompt you for the dates.
Alternatively you could use something like between date()-30 and date()
which would show results from the last 30 days.

The is query can now be saved as it will filter out the lines we are
interested in.

Then create a second query based upon the first query, and add all columns
except the date column.

click the epsilon button when in design view to group all the columns,
then change one such as operator_id to count.

Running this would give you a list showing 1 line for each vehicle and
route with a count showing how many times that vehicle had done that route
in the date range you had specified.

Later you could add fields such as Conflict, with yes or no values in you
log table, then you could have 2 lines for the vehicle showing how many
times it had done the route, and how many times it had been shot at.

hope this helps

- Show quoted text -

 

[BruceM]

I wish I had found that tutorial or something of the sort sooner. From what
you have described Access is well-suited to your project, but yours is not
the simplest kind of database in that it includes the junction table. The
tutorial should help you with defining and setting up the basic structure.
Good luck.

Thank you, Bruce!! I think that tutorial is just what I was looking
for to get me started _correctly_! I've muddled my way through one or
two database projects, but not very successfully and not without an
exceptional amount of hand-holding. I realize this one is quite a bit
for a beginner, but it's what there is on the plate to do, so . . .

Ed

I think the LogDetails table is essential to the design rather than a
convenience for reporting. Each Vehicle can go on many routes, and each
route can be taken by many vehicles over time, so LogDetails serves as a
junction table to allow this many-to-many relationship. LogDetails is the
Vehicle/Route table, containing the record of a particular vehicle
traveling
a particular route on a particular day (or at a particular time if that
vehicle can travel the route more than once per day).

The thing to remember is that each table stores information about a single
entity such as Vehicle or Route. There is not necessarily a Master table
as
such, but rather a choice of how to model the relationships. For instance,
you could have a Vehicles table (tblVehicle), and a form (frmVehicle)
based
on the tblVehicle (or on a query based on the table). Then there would be
another form based on tblRoute, which contains Route information including
the RouteCode. This is where you set up your routes.

The form frmVehicle would have a subform based on tblLog (the LogDetails
table as described above). It could be a continuous subform to enable you
to see several routes traveled by that vehicle. The subform would have a
combo box that gets its Row Source from tblRoute.

You may also need an Employee or Driver table (tblEmployee), with a unique
identifier such as EmployeeID for each employee record. A combo box on the
subform could be used to select the driver.

This would allow flexible reporting that could show all of the routes a
vehicle traveled, all of the vehicles that traveled a route, all of the
trips taken by a driver, and so forth.

There is a lot to consider with such a project. Access is very flexible
and
powerful, but because you are creating an application and for other
reasons
it has a steep learning curve. You may do well to review this
tutorial:http://allenbrowne.com/casu-22.html

This page is well worth checking too:http://allenbrowne.com/tips.html

It includes a Links link on the right side of the page, with links to more
information and tutorials.

Hi Ed,

There doesn't seem to be any guru's answering today, so I'll have a go,
but I'm no expert.

I think you need 3 tables.
1) Vehicle details, perhaps columns such as Vehicle_ID, Vehicle _Type
(Where Vehicle Type could contain Manned/Drone)
2) Route details, perhaps columns such as Route_ID,Route_Type ( where
Route_Type could contain AIR/SEA/LAND )
3) Log details, perhaps columns such as
Operator_ID,Vehicle_ID,Route_ID,Log_date

I think you only need the log details table to produce your reports but
the others will help later if your superiors want more detailed
reporting.

You create a query with columns from the log details table, and in the
criteria for log_date you need a between statement such as

between [What is the reporting start date] and [What is the reporting
end
date]
The line above is exactly as you would need to enter it if you want it
to
prompt you for the dates.
Alternatively you could use something like between date()-30 and date()
which would show results from the last 30 days.

The is query can now be saved as it will filter out the lines we are
interested in.

Then create a second query based upon the first query, and add all
columns
except the date column.

click the epsilon button when in design view to group all the columns,
then change one such as operator_id to count.

Running this would give you a list showing 1 line for each vehicle and
route with a count showing how many times that vehicle had done that
route
in the date range you had specified.

Later you could add fields such as Conflict, with yes or no values in
you
log table, then you could have 2 lines for the vehicle showing how many
times it had done the route, and how many times it had been shot at.

hope this helps

- Show quoted text -

 

[a a r o n . k e m p f]

Bruce is wrong

Jet is crap.
Move to SQL Server, because it has a future and Jet does not

I wish I had found that tutorial or something of the sort sooner.  Fromwhat
you have described Access is well-suited to your project, but yours is not
the simplest kind of database in that it includes the junction table.  The
tutorial should help you with defining and setting up the basic structure..
Good luck.

Thank you, Bruce!!  I think that tutorial is just what I was looking
for to get me started _correctly_!  I've muddled my way through one or
two database projects, but not very successfully and not without an
exceptional amount of hand-holding.  I realize this one is quite a bit
for a beginner, but it's what there is on the plate to do, so . . .

Ed

I think the LogDetails table is essential to the design rather than a
convenience for reporting. Each Vehicle can go on many routes, and each
route can be taken by many vehicles over time, so LogDetails serves as a
junction table to allow this many-to-many relationship. LogDetails is the
Vehicle/Route table, containing the record of a particular vehicle
traveling
a particular route on a particular day (or at a particular time if that
vehicle can travel the route more than once per day).

The thing to remember is that each table stores information about a single
entity such as Vehicle or Route. There is not necessarily a Master table
as
such, but rather a choice of how to model the relationships. For instance,
you could have a Vehicles table (tblVehicle), and a form (frmVehicle)
based
on the tblVehicle (or on a query based on the table). Then there would be
another form based on tblRoute, which contains Route information including
the RouteCode. This is where you set up your routes.

The form frmVehicle would have a subform based on tblLog (the LogDetails
table as described above). It could be a continuous subform to enable you
to see several routes traveled by that vehicle. The subform would have a
combo box that gets its Row Source from tblRoute.

You may also need an Employee or Driver table (tblEmployee), with a unique
identifier such as EmployeeID for each employee record. A combo box on the
subform could be used to select the driver.

This would allow flexible reporting that could show all of the routes a
vehicle traveled, all of the vehicles that traveled a route, all of the
trips taken by a driver, and so forth.

There is a lot to consider with such a project. Access is very flexible
and
powerful, but because you are creating an application and for other
reasons
it has a steep learning curve. You may do well to review this
tutorial:http://allenbrowne.com/casu-22.html

This page is well worth checking too:http://allenbrowne.com/tips.html

It includes a Links link on the right side of the page, with links to more
information and tutorials.

news:[email protected]...

Hi Ed,
There doesn't seem to be any guru's answering today, so I'll have a go,
but I'm no expert.
I think you need 3 tables.
1) Vehicle details, perhaps columns such as Vehicle_ID, Vehicle _Type
(Where Vehicle Type could contain Manned/Drone)
2) Route details, perhaps columns such as Route_ID,Route_Type ( where
Route_Type could contain AIR/SEA/LAND )
3) Log details, perhaps columns such as
Operator_ID,Vehicle_ID,Route_ID,Log_date
I think you only need the log details table to produce your reports but
the others will help later if your superiors want more detailed
reporting.
You create a query with columns from the log details table, and in the
criteria for log_date you need a between statement such as
between [What is the reporting start date] and [What is the reporting
end
date]
The line above is exactly as you would need to enter it if you want it
to
prompt you for the dates.
Alternatively you could use something like between date()-30 and date()
which would show results from the last 30 days.
The is query can now be saved as it will filter out the lines we are
interested in.
Then create a second query based upon the first query, and add all
columns
except the date column.
click the epsilon button when in design view to group all the columns,
then change one such as operator_id to count.
Running this would give you a list showing 1 line for each vehicle and
route with a count showing how many times that vehicle had done that
route
in the date range you had specified.
Later you could add fields such as Conflict, with yes or no values in
you
log table, then you could have 2 lines for the vehicle showing how many
times it had done the route, and how many times it had been shot at.
hope this helps
Jon.
I am an extreme novice with Access. I tried to set this up with
Excel, but I really think it's just too much and needs a database.
Several people will be using this, and I know more than one will try
to use it at the same time.
We have a fleet of vehicles and several routes. We want to take the
operator's daily logs and record how many times each vehicle covered
any route. The routes covered and the number of times any route is
covered varies daily and is different for each vehicle. Each vehicle
has a unique identifier, and each route has a unique code. We want to
generate reports to show where each vehicle went over any given time
span, which vehicles covered any specified route over any given time
span, comparisons of route acitivity over any given time span, and
probably a few other variations that management hasn't thought of yet.
I can see a table of vehicles by type ans serial number and a table of
routes. The data input form would have a drop-down for vehicle type,
then a drop-down for the serial numbers of vehicles of that type, a
date field, and then a series of drop-downs to allow the user to
choose the route and input how many times that route was covered that
day by that vehicle.
How do you set up the master data table? By vehicle or by route? How
do you create a key field that will tie together vehicle, route, date,
and data? Every time I try to visualize this, I see a three-axis
table - vehicle, route, and date - and my brain grinds to a halt! Any
help is greatly appreciated!!
Ed- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[rumkus]

Jet is crap.

But a very very nice one.
Many thanks for its designers and engineers by the way.

 

[Ed from AZ]

Bruce is wrong

Jet is crap.
Move to SQL Server, because it has a future and Jet does not

On Jan 7, 9:35 am, "BruceM" <[email protected]> wrote:

Aaron - I've read enough of your stuff on here to know that, while
very knowlegeable, you are also very annoying in your posting behavior
and attitudes towards others. I ask about these programs because
it's what I have - I will not be moving to anything else any time in
the future. If you are going to respond with comments like this
rather than actual helpful advice like the rest of the community, then
please do not respond to my posts.

Ed

 

[a a r o n . k e m p f]

It's not what you have. What you have a free version of SQL Server--
www.microsoft.com/sql
What you have is 'Access Data Projects'-- the same precious forms and
reports-- but an engine with a future.

it's the same price; and SQL Server is _EASIER_ than Jet..

So sorry that you think that Jet is 'good enough'.
I have more experience with Access than most of the MVPs; I was
working with VLDB and Jet a decade ago; and I know it like the back of
my hand.

I've seen enough corruption-- and enough people who blame it on the
network-- to say 'Jet is not reliable enough for a single user and 25
mb of data'
I've seen enough corruption-- and enough people who blame it on the
network-- to say 'Jet is not reliable enough for a single user and 25
mb of data'
I've seen enough corruption-- and enough people who blame it on the
network-- to say 'Jet is not reliable enough for a single user and 25
mb of data'
I've seen enough corruption-- and enough people who blame it on the
network-- to say 'Jet is not reliable enough for a single user and 25
mb of data'


It's time to learn a database with a future, kid!

 

[Ed from AZ]

What I have is Access 2000 on one machine and Access 2007 on another.
And that's also just about the depth of my knowlege of both. Well,
I've muddled a bit with a basic two-tables-and-form set-up, but that's
all I've had success with.

My future's not in database management, and I'm too close to
retirement to jump to a new future right now. I could work just as
well with pencil and paper. If my workplace wasn't pushing upgrades
and making me learn new stuff, my home machine with Win98SE and Office
2000 would be sufficient until the day I can't use a computer. Except
for Internet stuff - got a laptop with WiFi for that!

So, while I thank you for your obviously well-intentioned
recommendation, I will be very happy trying to learn how to play with
the toys and read all the books I have right now.

Cheers!
Ed