Cross Checking Two Fields for Duplicates

[SmugglersBlues]

HI,


I have a table that is setup as:
edate, etime, equipment one, equipment two, equipment three


I need to check that equipment two does not duplicate equipment one on the same edate and etime. I've looked everywhere but can't find any examples on how to go about this. This is on a Form and should be checked before updating. Any ideas?

Thanks

[pbaldy]

The table doesn't look normalized, but is this relevant?

http://www.baldyweb.com/OverLap.htm

Adding a criteria on equipment.

[SmugglersBlues]

Thanks for the reply, The link you provided looks interesting, if it can also sort through times as well as dates. You mentioned the table doesn't look normalized. I'd prefer to fix the table and make this easier. Would you mind sharing how I can normalize the table for better results? I need to schedule up to 3 pieces of equipment at the same time. None of the equipment can be repeated in the same time frame on the same day. Thoughts?

[alansidman]

Here is a link on data base design.

http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[pbaldy]

The link should work with a date/time value as well as dates. Typically the equipment would be in a related table, with a record for each piece.

[SmugglersBlues]

I read the article on normalizing. I would like to at least get to first normal form with this. It is going to be a small database so there wouldn't be much advantage spending time going beyond that. With that in mind here is a sample I created today. Does it meet first normal form? If so, how do I go about being able to enter 3 different vehicles at once? How would I edit the data after it is entered? The DB only has two tables and no macros or VBA.

[pbaldy]

I would expect a "jobs" table with info about each job (or event, or whatever equipment is going out for). Can't tell if that's your ScheduleT table. I'd expect an equipment table with a record for each piece of equipment and its related info. Then a many-to-many junction table that has fields for JobID and EquipmentID. It's called many-to-many because a job can have many pieces of equipment and a piece of equipment can be used on many jobs. Typically that would be presented to the user with a form bound to the jobs table and a subform bound to the junction table. Master/child links keep the subform in sync with the main form.

As to your dates and times, are they job specific or equipment specific? That tells you whether the date/time fields should be in the jobs table or the junction table.

[SmugglersBlues]

Hi pbaldy,
The table is a quick example I put together. The Description field on ScheduleT table gives a description of the job. The date/time is specific to the vehicle with multiple vehicles (up to 6) being scheduled. They can be scheduled on different times so it didn't seem right to put date/time on the ScheduleT side. Does this look like it is on the right track to 1nf?

[SmugglersBlues]

Also, the Job No is unique, like a SSN number. They can't ever repeat.

[pbaldy]

This is one reason for normalization, your words:

I need to schedule up to 3 pieces of equipment
multiple vehicles (up to 6) being scheduled

A normalized db allows for 1 vehicle or 1,000; your original design allowed 3 (and I understand you were probably being generic, not specific). Given that, I'd say you are on the right track, yes. I would store the date/times together, not separately. Storing them separately is easier for form design (you just give the user 2 fields), but can make querying more difficult.

[SmugglersBlues]

This is one reason for normalization, your words:

I need to schedule up to 3 pieces of equipment
multiple vehicles (up to 6) being scheduled

LOL, the reality is I need up to six, lets say 3 on days, 3 on nights, but never 6 on days or 6 on nights. Something along those lines. So if this DB looks 1nf acceptable, would you be able to point me in the right direction on the form design? Should I use a One to many form? Even better, would you know of any example forms or DBs that can show me how to schedule multiple equipment or events on a single form? And thanks for the replies, at least I know I'm headed on the right path.

[pbaldy]

I'm not aware of a sample db or template that fits your situation, though there certainly could be one out there. Did this make sense?

Typically that would be presented to the user with a form bound to the jobs table and a subform bound to the junction table. Master/child links keep the subform in sync with the main form.