Can I keep this in XL (vice Access)?

[Ed from AZ]

I'm a poor hand with Access, but reasonably decent with Excel. I'm
wondering if my current project can stay in Excel or if I should bite
the bullet and grind my way into Access (I'm using the 2007 versions).

We have vehicles identified by type and ID number, and we have the
routes they travel. The idea is to track how many circuits of any
route is travelled by a vehicle on any day. So my data table looks
like:
VehType VehID Date Route QtyCircuits

Some considerations:
-- We'll want to generate data tables like:
** which vehicles ran which route how many times between certain
dates
** how many times each route was travelled by each vehicle type
between certain dates
-- Multiple users will be entering data at the same time
-- The users are very familiar and comfortable with Excel, and
uncomfortable with Access because it's unfamiliar
-- The tables for VehType, VehID, and Route are not static; items are
added on an irregular but constant basis. Once entered, though, it's
not removed, but merely ignored. (In Access, I can check a box that
marks an entry as "out of service" and it doesn't show up in the drop-
down list.)

What's the opinion of the experts?

Ed

 

[Bernie Deitrick]

Ed,

A pivot table based on your database would do this quite easily. For example, select the table, use
2007's version of Data / Pivot table... (actual command varies by version, and I don't use 2007, so
I don't know the actual command sequence but the functionality is the same).

Then of you have finished the wizard, drag "Date" to the Row area, VehType, VehID, and Route to the
row area (or the column area, depending on how you want your table laid out), and then QtyCircuits
to the data area. Then you can play around with the pivot table, and use Grouping to show by month,
by starting and ending date, etc.

Excel should expand the data area source as data is added - I'm not certain of the settings required
in 2007, but it is certainly an available option.

HTH,
Bernie
MS Excel MVP

 

[Max]

Ed,

Could you take a moment & post a closure reply in your other thread:
Re: Can I extract unique cell values from every nth column?

I've responded to your last query there, but the thread's dangling since for
about a week, awaiting closure from you
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---