Many to Many Relationship

[MishkaNanook]

I'm looking to create a form which allows you to track the amount of time a particular piece of equipment is in use (up), not in use(idle), and being repaired (down) on a daily basis. I currently have it set us that my end users pick's the piece of equipment from a drop down box and then fill in the appropriate times; however, they would prefer if all the equipment was listed on the form when they openned it and they could them fill in the appropriate blanks for the times up, down and idle.

The database is set up with an Equipment table which has all the equipment and locations listed in it and an UpDown Table which keeps track of the date and corresponding up, down and idle times.

I was thinking this would be a many to many relationship and I therefore needed a joining table to link the two but I still can't seem to make a form which on one hand lists all of the equipment in one table and creates an input form to enter data into another table.

Any ideas how I might accomplish this?

Thanks

 

[MarkK]

It seems to me that the up/down status is a direct child of the equipment. I don't see how this is many-to-many. For one piece of equipment there will be many status records.

 

[MishkaNanook]

I know... what can I say I'm having relationship issues, I currently have it set up as a one-to-many relationship; however, this does not allow me to set up a form showing all of the equipment with input fields for Up, Down and Idle.

 

[MarkK]

this does not allow me

In what way are you disallowed?

 

[MishkaNanook]

Sorry for the delay in getting back to you, I've been trying to figure out a way to better explain what I'm trying to do and it's not really working so I figured I'd try with an attachement and see if that helps.

Exhibit A is what I currently have - a drop down box which gets it's data from the tblEquipment and text boxes which are associated with the tblUpDown. The end user would pick a piece of equipment from the drop down box and then enter in the number of hours that piece of equipment was up/down and changing for a particular day.

Exhibit B is what I would like the form to look like. All of the equipment listed in tblEquipment with their associated details would be listed on the left and the end user could then click in the field on the right to add the up/down and changing info. Note the Idle column is a calculated field.

Sorry I'm really :banghead:when trying to figure out how to accomplish something similar to Exhibit B. I've tried a form/sub form; however, it doesn't work as each entry needs to be associated with a particular piece of equipment and a particular day.

Any help would be greatly appreciated.

 

[MishkaNanook]

Sorry doesn't seem like the attachment is there. Let me try again.

 

[MarkK]

These exhibits, IMO, cannot be interchanged. You need to be able to record up and down for a single piece of equipment for a specific date/time, and B doesn't allow that. To add/edit the data, you need exhibit A. To show a summary of the data, you might use Exhibit B.
Hope this helps,

 

[MishkaNanook]

Ok I was hoping I was missing something, guess I'll have to figure something else out. Thanks.