wrap your brain around this one

[David R]

So I've finally gotten my data normalized (hi Pat! *sheepish grin*). However I'm not entirely sure what the best wya to revamp my form is to reflect the new data structure.

To summarize:
tableParticipants: Main table for each person. ParticipantID is the PK.

tableDevices: Each device a person gets goes here. Has a FK for ParticipantID, and a FK for EventID (see below), since each device is given out at a specific event.

CFS_Events: Actually stored in another database, this is the record of all events we do. EventID is the PK. I've linked this to the Participants database in order to use the Event information.

So the data entry form looks like this:
-=*Section on Participant Information*=-
-=*Subform for Devices (Continuous)*=-
-=*Subform for Events (Continuous)*=-

The subform for Events is based on the following query:

SELECT DISTINCT tableDevices.ParticipantID, CFS_Events.EventName, CFS_Events.Location,
CFS_Events.StartDate, tableEventTypes.EventTypeText, CFS_Events.EventID
FROM (CFS_Events INNER JOIN tableDevices ON CFS_Events.EventID = tableDevices.EventID)
LEFT JOIN tableEventTypes ON CFS_Events.EventType = tableEventTypes.EventType
ORDER BY CFS_Events.StartDate;

So it only shows one occurrance of each event, no matter how many devices are obtained at any one event. This subform is locked and only for display purposes.

>> My problem is this: How do I select the EventID when I enter a device, without cluttering up the Devices subform too badly. Real estate is already at a premium for that subform or I'd just add a combo box. I figured that 98% of the time, there's only going to be one event on a given day, so I can lookup the event for that day and add it if there's just one, otherwise bring up a popup form with a combo of all the events for that day. The kicker is that sometimes the date field for the subform (which is actually the DateReceived) may not be the same as the EventDate, and there are 700 events (thus far) to scroll through to find the right one.

Suggestions? Would a popup form with the combo box for all the events on it and a prompt for the actual event date be too intrusive? Another possibility is adding a second line to the subform, so that you can see the name/date (at a minimum) of the event as you select it from a combo. Is there a way to make this combo disappear and the space shrink back down after you fill it out?

Anticipatorially,
David R


[This message has been edited by David R (edited 04-04-2002).]

 

[David R]

I went ahead and added a smaller combo box to each continuous record to select the event by date, but it's not an ideal situation, since now the event is shown 'twice', albeit discreetly.

I am having a hard time fathoming that Access cannot make a continuous form show additional fields when it has the focus, but I don't know why. It is Microsoft after all.

Any ideas come to mind from anyone?

David R

 

[al0y0u]

not totally quite sure what you are trying to achieve here.


you want to keep track of

participants
events
devices

and you want to keep track of what devices are given to what participants at what events.

am i correct so far?

then you want to be able to retrieve data based on the device? like who recived it already and when?

or are you creating a user entry form that when you enter a device id it will display what event it is for?

so like your entering data each time you give away a device to a participant at an event and you want to keep track of that.

the latter is easy but at same time not if that makes ya feel any better LOL

what you could do is create a simple table with an auto number for PK and then have each device listed and what events it will be used at only. may seem like alot of work BUT then in form you could create a combobox that user selects the device and then another combox that fills with only the events that match the device make sense?

Dont know if that helps but here is some code that may help ya if thats what your looking for

IN the first combobox(device) set the
rowsource/type to table/query

set rowsource
SELECT [TABLENAME].[FIELDDEVICE] FROM TABLENAME GROUP BY [TABLENAME].[FIELDDEVICE];

then in an ONCLICK event use this code

Private Sub cboDEVICE_Click()
cboEVENT.Requery
End Sub

that will load your combobox with all devices you have stored in that table or you could just type it in each time or whatever


then in your events combobox
set type to table/query
row source
SELECT [TABLENAME].[FIELDEVENT] FROM TABLENAME
WHERE ((([TABLENAME].[FIELDDEVICE])=cboDEVICE));


hope that helps some

 

[David R]

I don't think I explained myself entirely well. Any device (there are ~10 currently) can be given out at any event. From this form, Devices are a subform of Participants, and Events are shown merely as a listing, not to be modified from this end. in the Devices table are fields for both ParticipantID and EventID. I'm just trying to decipher the easiest way to populate EventID (since it's a subform of Participants, the ParticipantID is already filled in).

Events are generally, but not always, unique to a specific day. However sometimes the device is actually given out on a subsequent day to the event it was signed up for. Those I'm not too concerned with, since they're current entries and I can input the event by hand. It's the older events where ALL we may have about the event is the day of the event (and for these, this is always the day the device was given).

Is that better? Man what a mobius problem.

 

[not_rich_yet]

Hmm, let me get this right:

A participant goes to an event
At an event, a participant may be given one or more devices.

In which case, I'd hazard a guess that your DB isn't normalised correctly.

I think you still have some many-many type relationships in there, and addition of one or more tables may help..

If I have assumed the above correctly, try this:

tblParticipant: holds purely participant details, one record for each individual participant. PK is something like ParticipantID

tblDevice: holds purely device details, one record for each individual device. PK is something like DeviceID

tblEvent: holds purely details about an event, one record for each event. PK something like EventID

tblDeviceLoan: holds a record of what device is lent to which person at which event. Fields for this table will be ParticipantID, EventID and DeviceID. PK will either be a composite key based on all three of the above fields, or it could be set as something like DeviceLoanID.

You can now have the following forms:

Single Form Main Form frmEventLoans

Based on tblEvent

Single form SubForm called frmParticipantDetails onto frmEventLoans

Multiple form SubForm called frmDeviceLoanDetails onto frmParticipanyDetails

This should now show and allow manipulation as follows:

Every event will display a subform of participants for that event, and for each participany Device loans will be displayed..

You'd need some queries behind the scenes so that you can pull Participants relevant to the Event, and then Device Loans relevant to the event and to the Participant and I'd guess you may need to relate these queries to the forms themselves to make it work correctly...

Having the ParticipantID field in the Device table for your DB means every Device will appear numerous times in the table, which isn't right.....base the DeviceID on the serial number for each device and there's your PK for device...

Hope this helps,

nry: wanting a job doing this!

 

[David R]

Actually the tables are normalized. There is a many-to-many relationship between the three tables, but four isn't necessary. Since ParticipantID, DeviceID, and EventID are all present in tableDevices, that can be used as the 'linking' table.

Thanks for your help anyway.

David R

 

[not_rich_yet]

Hmm, to what normal form level are they normalised? Currently, every device you have in the system can appear possibly infinite times in the device table, as a seperate record of each device is required every time a device is loaned: this is not an ideal situation and I'd guess it may make keeping a track of what devices you have impossible....

nry
Trust me, you need the fourth table! Otherwise you have repeating rows/tuples in the Device table....

You should have a table to hold details of an event (possibly including start and end dates, venue, etc), a table to hold details of each person attending the event(s), a table to hold details of each device (otherwise every time a device is loaned out more than once you have to re-enter the details for this device!), and then the final table which is the linking table, allowing the following to be recorded: which device was lent to which person at what event.

Access won't let you create a many to many relationship and a database with a many to many relationship is not fully normalised, so somewhere something is cuckoo. The only way to resolve a many to many relationship is by the addition of one or more 'joining' tables....

nry

 

[David R]

The devices are given, not loaned. That's why only three tables are needed. Once a device is given at a specific event to a specific person, it's done.

I'm not sure what you mean when you say 'Access won't allow let you create a many-to-many relationship'. We seem to be cross-communicating, and we've ranged far afield of my original question in any case.

David R

 

[not_rich_yet]

Simply, Access won't let you create a Many-Many relationship, only a many to one or a one to one....you said that you have a many to many within Access but I don't know how:

"Actually the tables are normalized. There is a **many-to-many relationship** between the three tables, but four isn't necessary. Since ParticipantID, DeviceID, and EventID are all present in tableDevices, that can be used as the 'linking' table."

I don't understand how you have created this many-many relationship....

So I presume at present then, that when a device is given to a participany the user enters details of this device manually? And once a device is given it is kept by the participant and never returned?

Relationships as I see are:

1 event has many participants
1 participant has many devices (as I presume a participany may attend multiple events and be given a new device at each event?)

Form wise, I'd be tempted to have Event as the main form, then a single form detailing a Participant. The Participants subform will display a step-throughable list of participants who are attending an event. You can then create a subform on the 'Participants' form (not on the Events form), based on a query. The query could show all devices for the event ID displayed on the Events form. When attached to the Participants form, and everything is run, it 'should' display an event, the people attending the event, and from this the devices given to each person at the event...

nry

 

[David R]

Yep, I think we're closer now. By "many-to-many" I meant that there are two one-to-many relationships: tableDevices-to-tableParticipants and tableDevices-to-CFS_Events(linked), which is how one forms a many-to-many in Access (damn terminology problems).

My form structure is very similar to what you proposed, with the exception that since there are 3000+ Participants and only 600+ Events, it was faster to have Events be the lookup box on the Devices subform of Participants.

If you're morbidly curious I can email you a copy of the database as it works currently. It would probably be simpler than trying to explain any further here, and you're welcome to make suggestions.

Regards,
David R


[This message has been edited by David R (edited 04-10-2002).]

 

[not_rich_yet]

Hell yeah, why not!

not_rich_yet@btinternet.com

nry

 

[not_rich_yet]

Stupid OutlookXP won't let me open the DB as it is 'potentially unsafe' !

I've posted elsewhere to see if I can force it to let me open it!

nry

 

[David R]

Hells, I could have told you it was unsafe. I've been working with the thing for months.