[mso] Re: Database Integrity & New Question

Yes it is.  It is like a big puzzle and finding all the right pieces and
where they fit.

:)


Best regards,

Carol Parent
info@xxxxxxxxxxxxxxx
http://www.carolparent.net

I finally got it all together...
I just wish I could remember where I set it down.

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of Peter Chiavaro
Sent: Tuesday, April 30, 2002 7:48 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Database Integrity & New Question



Good morning Carol,

There should be no need for yearbegin and yearend milage fields in your =
main table.

You said originally if I'm not mistaken, that you wanted to track =
"trips" or tasks each vehicle preformed. This is where the mileage would =
be kept in a related table (and related via the primary key to the main =
table).=20

The related table should look something like this.

Table Name: TRIPS

[TripID] Autonumber
[VehicleID] (or whatever you decided on for the primary key field in the =
main table)
[Task]
[DriverName]
[StartMiles]
[EndMiles]
[Notes]
[and whatever else pertains to each trip]

In other words,, everything a "TRIP" HAS ONLY ONE OF.

For example, a trip cannot have=20
two(2) different StartMiles
two(2) different EndtMiles

get the Idea?

>From there you can query off anything in the world you wanted. Mileage =
for the year, month, week, and further filtered by mileage for any =
particular task, or Driver.

At this point, I'd like to remind you to keep the data entry as =
uniformed as possible. You might need a little table for driver names, =
(and perhaps a little table for valid tasks).=20

This ensures that all you queries run correctly later on.

Use combo boxes in your data entry forms so that users cannot do typos =
and throw a monkey wrench in the gears. For example, if someone types =
Bob Joens instead of Bob Jones. Totals will never add up correctly for =
Bob Jones. Use the combo boxes and FORCE the user to choose only what's =
in the list.

Your Driver table might look like this.

[DriverID]
[DriverName]=20
should be stored as "Jones, Bob H." not "Bob H. Jones".
[DOB]
[LicenseNumber]
[BeginWorkDate]
[TerminatedDate]
[SSINumber]

and everything else a driver CAN ONLY HAVE ONE OF.

This is fun isn't it?

Peter C.
Stuart, Florida
Kp@xxxxxxxx
-_-_-_-_-_-_-_-_-
ICQ =3D 412633







-----Original Message-----
I understand running queries based on dates BUT if I have fields for
YearBeginMilage and YearEndMilage and the results will be based on miles
driven during the current year, how do I "start over" with the
YearBeginMiles to end up with the right YearEndMiles?


Best regards,

Carol Parent
info@xxxxxxxxxxxxxxx
http://www.carolparent.net

I finally got it all together...
I just wish I could remember where I set it down.

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of n1jmm
Sent: Monday, April 29, 2002 9:50 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Database Integrity



Does this becaome a problem if the school decides to RENUMBER the =
vehicles?

-Jim-


Carol Parent wrote:

> Hello Peter,
>
> I am using MS Access 2000
> In Design Phase (paper/pencil) and some "playing" with tables/queries =
with
> dummy data
> What I am wanting to "see" are different options I may have to =
protecting
> the integrity of the relationships (data) but enable the addition of =
new
> vehicles using same identifying label.
>
> I really think the VIN # may be the solution (and actually a value =
added
> feature).
> Another option may be to combine the vehicle # with the DateAdded (to
school
> fleet of vehicles) as the Primary Key.
>
> Part of what I am pleased with is that I actually recognized that this =
may
> cause future problems before I put a lot of computer time in.
>
> Best regards,
>
> Carol Parent
> info@xxxxxxxxxxxxxxx
> http://www.carolparent.net
>
> I finally got it all together...
> I just wish I could remember where I set it down.
>
> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
> Behalf Of Peter Chiavaro
> Sent: Monday, April 29, 2002 12:38 PM
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] Re: Database Integrity
>
> Hi Carol,
>
>    I hope we can assume this is an MS Access database?
>
> What Version of Access are you using? And how far along are you in the =
=3D
> data that has been entered? In other words, is the database still in =
its =3D
> design mode or do you already have data in the tables?
>
> This is tuffy, but in order to help you better, I've got to know what =
=3D
> you've done so far.
>
> The only way I know of to get around the problem is to create a table =
=3D
> that might look something like this:
>
> VehicleID (autonumber)
> SchoolVehicleID (indexed - Duplicates ok)
> Make
> Model
> Actual Serial Number
> Color
> (and everything else the car CAN ONLY HAVE ONE OF)
>
> For example, a car can only be one Make. It CANNOT be a Chevy and a =
=3D
> Dodge at the same time. It can only have 1 VIN, and it can only be one =
=3D
> model.
>
> This table will be at the top level. Every time the school buys a new =
=3D
> car, you would add it to this table.
>
> >From here on, every time you add a vehicle it will have a new =
VehicleID,
=3D
> but you can assign the same SchoolVehicleID.
>
> Cheezz,,, I could lay out these tables for you in 2 minutes, but it =
=3D
> would take me an hour's worth of typing to explain it.
>
> If you email me at my address below, I'll return a sample layout. Just =
=3D
> let me know what version of Access you are using, and if you already =
=3D
> have data in the tables. If you already have data, then the procedure =
=3D
> might be a little tricky. If you're just starting to build the =3D
> foundation, then it's a piece a cake.
>
> I am by no means an Access wizard, but stuff like this I can handle =
with =3D
> ease.
>
> Peter C.
> Stuart, Florida
> Kp@xxxxxxxx
> -_-_-_-_-_-_-_-_-
> ICQ =3D3D 412633
>
> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On =3D
> Behalf Of Carol Parent
> Sent: Monday, April 29, 2002 12:34 PM
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] Database Integrity
>
> Hello,
>
> I have a question concerning Database Integrity.  I hope this is an
> applicable topic.  If not please advise.
>
> I am creating a database of Vehicles/Activities/MilesDriven/FuelUsed =
for =3D
> our
> local school system.  I need to be able to track which vehicle goes on =
=3D
> what
> activity and track the amount of miles being racked up on the =
vehicles.
>
> Now that is not a big deal BUT when the school purchases a NEW vehicle =
=3D
> they
> replace an old vehicle giving it the same number.  How can I protect =
the
> data integrity (i.e., relationship between =3D
> vehicles/milesdriven/activity)
> but use the SAME NUMBER to identify a different vehicle?
>
> I am not very proficient with databases so am taking this very slowly =
as =3D
> to
> hopefully not muck it up to much.
>
> Thanks very much for any advice/responses you may offer.
>
> Best regards,
>
> Carol Parent
> info@xxxxxxxxxxxxxxx
> http://www.carolparent.net
>
> I finally got it all together...
> I just wish I could remember where I set it down.
>
> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
> Behalf Of James LaBorde
> Sent: Monday, April 29, 2002 11:24 AM
> To: 'mso@xxxxxxxxxxxxx'
> Subject: [mso] Re: New to the List- Happy to be here. :VSMail MX2
>
> Peter,
>
> Good to see another Access person here.
>
> James
>
> -----Original Message-----
> From: Peter Chiavaro [mailto:kp@xxxxxxxx]
> Sent: Saturday, April 27, 2002 8:45 AM
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] New to the List- Happy to be here. :VSMail MX2
>
> Hello Fellow Office Dweebs,
>
> This is my first post. (Hi Linda, Hi Dian).
>
> I am proficient with MS Access. I don't write code, but I can do
> just about everything else. Perhaps I can answer some of the basic
> MS Access questions / problems.
>
> More Later.
>
> Peter C.
> Equine Cooling Technician
> Alex Campbell Racing Stables
> Payson Park
> Indiantown, FL
>
> *************************************************************
> PLEASE READ!!!!
>
> This is not S*P*A*M!  You are receiving this mail because you either
> subscribed to mso@xxxxxxxxxxxxx or to it's earlier version,
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To Unsubscribe from this group, send an email to =3D
> mso-request@xxxxxxxxxxxxx
> with a subject line that says "unsubscribe" (without the quotes).  Do =
=3D
> not
> put unsubscribe IN CAPS.  Screaming doesn't get you out any faster and =
=3D
> the
> caps prevent the function from working.
>
> To change your email settings to digest or vacation (no mail), visit =
the
> group's homepage for full instructions.
>
> http://www.freelists.org/webpage/mso
> *************************************************************
> *************************************************************
> PLEASE READ!!!!
>
> This is not S*P*A*M!  You are receiving this mail because you either
> subscribed to mso@xxxxxxxxxxxxx or to it's earlier version,
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To Unsubscribe from this group, send an email to =3D
> mso-request@xxxxxxxxxxxxx
> with a subject line that says "unsubscribe" (without the quotes).  Do =
=3D
> not
> put unsubscribe IN CAPS.  Screaming doesn't get you out any faster and =
=3D
> the
> caps prevent the function from working.
>
> To change your email settings to digest or vacation (no mail), visit =
the
> group's homepage for full instructions.
>
> http://www.freelists.org/webpage/mso
> *************************************************************
>
> *************************************************************
> PLEASE READ!!!!
>
> This is not S*P*A*M!  You are receiving this mail because you either =
=3D
> subscribed to mso@xxxxxxxxxxxxx or to it's earlier version, =3D
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To Unsubscribe from this group, send an email to =3D
> mso-request@xxxxxxxxxxxxx with a subject line that says "unsubscribe" =
=3D
> (without the quotes).  Do not put unsubscribe IN CAPS.  Screaming =3D
> doesn't get you out any faster and the caps prevent the function from =
=3D
> working.
>
> To change your email settings to digest or vacation (no mail), visit =
the =3D
> group's homepage for full instructions.
>
> http://www.freelists.org/webpage/mso
> *************************************************************=3D20
>
> *************************************************************
> PLEASE READ!!!!
>
> This is not S*P*A*M!  You are receiving this mail because you either
> subscribed to mso@xxxxxxxxxxxxx or to it's earlier version,
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To Unsubscribe from this group, send an email to =
mso-request@xxxxxxxxxxxxx
> with a subject line that says "unsubscribe" (without the quotes).  Do =
not
> put unsubscribe IN CAPS.  Screaming doesn't get you out any faster and =
the
> caps prevent the function from working.
>
> To change your email settings to digest or vacation (no mail), visit =
the
> group's homepage for full instructions.
>
> http://www.freelists.org/webpage/mso
> *************************************************************
>
> *************************************************************
> PLEASE READ!!!!
>
> This is not S*P*A*M!  You are receiving this mail because you either
subscribed to mso@xxxxxxxxxxxxx or to it's earlier version,
MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To Unsubscribe from this group, send an email to =
mso-request@xxxxxxxxxxxxx
with a subject line that says "unsubscribe" (without the quotes).  Do =
not
put unsubscribe IN CAPS.  Screaming doesn't get you out any faster and =
the
caps prevent the function from working.
>
> To change your email settings to digest or vacation (no mail), visit =
the
group's homepage for full instructions.
>
> http://www.freelists.org/webpage/mso
> *************************************************************

--
-Jim-
n1jmm@xxxxxxxxxxxx
n1jmm@xxxxxxxxx
n1jmm@xxxxxxxx
n1jmm@xxxxxxx
n1jmm@xxxxxxxxxxx
n1jmm@xxxxxxxxxxxxx
http://www.callsign.net/n1jmm
http://www.bigfoot.com/~n1jmm


*************************************************************
PLEASE READ!!!!

This is not S*P*A*M!  You are receiving this mail because you either
subscribed to mso@xxxxxxxxxxxxx or to it's earlier version,
MicrosoftOffice@xxxxxxxxxxxxxxxx

To Unsubscribe from this group, send an email to =
mso-request@xxxxxxxxxxxxx
with a subject line that says "unsubscribe" (without the quotes).  Do =
not
put unsubscribe IN CAPS.  Screaming doesn't get you out any faster and =
the
caps prevent the function from working.

To change your email settings to digest or vacation (no mail), visit the
group's homepage for full instructions.

http://www.freelists.org/webpage/mso
*************************************************************

*************************************************************
PLEASE READ!!!!

This is not S*P*A*M!  You are receiving this mail because you either =
subscribed to mso@xxxxxxxxxxxxx or to it's earlier version, =
MicrosoftOffice@xxxxxxxxxxxxxxxx

To Unsubscribe from this group, send an email to =
mso-request@xxxxxxxxxxxxx with a subject line that says "unsubscribe" =
(without the quotes).  Do not put unsubscribe IN CAPS.  Screaming =
doesn't get you out any faster and the caps prevent the function from =
working.

To change your email settings to digest or vacation (no mail), visit the =
group's homepage for full instructions.

http://www.freelists.org/webpage/mso
*************************************************************=20

*************************************************************
PLEASE READ!!!!

This is not S*P*A*M!  You are receiving this mail because you either
subscribed to mso@xxxxxxxxxxxxx or to it's earlier version,
MicrosoftOffice@xxxxxxxxxxxxxxxx

To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx
with a subject line that says "unsubscribe" (without the quotes).  Do not
put unsubscribe IN CAPS.  Screaming doesn't get you out any faster and the
caps prevent the function from working.

To change your email settings to digest or vacation (no mail), visit the
group's homepage for full instructions.

http://www.freelists.org/webpage/mso
*************************************************************

*************************************************************
PLEASE READ!!!!

This is not S*P*A*M!  You are receiving this mail because you either subscribed 
to mso@xxxxxxxxxxxxx or to it's earlier version, 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx with 
a subject line that says "unsubscribe" (without the quotes).  Do not put 
unsubscribe IN CAPS.  Screaming doesn't get you out any faster and the caps 
prevent the function from working.

To change your email settings to digest or vacation (no mail), visit the 
group's homepage for full instructions.

http://www.freelists.org/webpage/mso
*************************************************************

Other related posts: