Newb Access user. Need Help wiht relationship / form design.

[Pardysound]

Hello, I need to create an issue tracking database for an entertainment venue.. My design concept is:

One Day "tblEventDate" can have many events
One Event "tblEvents" Can have many Technicians applied "tblTechniciansEvents", as well as have many reported issues.



my project goal here is to allow the user from a single form, using tabs.. to be able to enter the information for all events on a given day much of the info would be the same for each event.

Where I am stuck now is I have a form that is based on tblEventDate, where I can through a subform ad the "Events" for that day, but I cant get another subform to let me add the "technicians" for each "event" via my "tblTechniciansEvents" I can however do it through the table relationships.

I still need to figure out how i'm going to organize the issue tracking, but I imagine its a similar process.

[CarlettoFed]

The structure is not correct if you attach the file it is easier to show you the changes to be made, taking advantage of what you have already created.

[Pardysound]

Hi, Thanks ok I've attached the file here.COTG ADMIN 3cp1.zip

[mike60smart]

Hi

This is one way of laying out the Forms

[CarlettoFed]

The first thing you should do is learn how to manage the properties of the various fields in the tables and in particular:
- Size (if the Type is text, do not always leave 255 set as you will almost always not need all those characters to prevent wasting disk resources)
- Required (if set to Yes it does not allow you to save the record if the requested data has not been entered)
- Allow zero length (to be set to Yes if the data is always required)
- indexed (set to "Yes (Duplicates not allowed)" when you want a value not to be entered more than once in a field and therefore to be unique. advice you should apply, for example, in the tblEventTypes.
- index management if two or more fields must be used to prevent duplicate records (see table tblEventTechnicians, EventID and TechnicianID fields).
It doesn't make sense to have the tblEventDate table just simply write the date inside the tblEvents.
You did not manage the table relating to MacauID to then have the relationship with tblTechnicians.
I am attaching the structure as I would see it.

[Pardysound]

Thanks for the reply.. a lot to digest there.

I think I need to better map out and get down on paper my end goals for the user workflow because Its clear I don't know how to even ask the right questions just yet, if its not 100% clear to me what I need how can I expect anyone to help me get there right.

Thanks again.. I will do some more homework and come back here with a better direction.

[CJ_London]

not sure I agree with this

- Allow zero length (to be set to Yes if the data is always required)

I would say No otherwise a user can delete a previous entry or just enter a space

[CarlettoFed]

I apologize for having misspelled, it is certainly mandatory to set it to No in case the data is required.

[CJ_London]

no need to apologise, we all make mistakes from time to time

[Micron]

I would say No otherwise a user can delete a previous entry or just enter a space

I'm thinking not possible re spaces but would be ok with being proved wrong. Every time I've entered one or more leading or trailing spaces in a field while testing they are always stripped out. Seems that spaces can only be between other characters. The only way I've been able to get a zls into a field is with an update query. If I enter a space to remove a value or delete data from a field and set query criteria to "" I get nothing but the new record row. If I specify Is Null then I can get the record with the deleted value.

[CJ_London]

I'm thinking not possible re spaces

it is.

Required=yes, allow zls=yes, a space can be entered
Required=yes, allow zls=no, a space will be rejected

simple test attached: zlstest.accdb

[Micron]

So the kicker seems to be that required has to be set to Yes because in my testing, Required was no; allow zls was yes. Spaces were stripped out and "" in criteria produced no records. Query returns record with "" criteria if the settings are yes and yes as you say. Thanks.

[CJ_London]

I was only talking about when the field is required per CarlettoFed's post

[Pardysound]

It doesn't make sense to have the tblEventDate table just simply write the date inside the tblEvents.
I am attaching the structure as I would see it.

Hi, My thinking behind having the table for just the event date is because of how I plan to do the data entry. I want a form that is based off of a date, that contains a subform that lists the events on that date, and a nested subform that show the roles and technicians depending on which event is selected. Is there a better way to accomplish that ?

I am struggling with how deal with the Events/Technicians/Roles..

I have a form based on technicians with a Subform based on TechnicianRoles where I can add roles to each technician.
what I need to do next is when assigning a technician to a given event, i first select a role, then I just want to have the Technicians who have that role assigned show up in the combo box. Ive looked into cascading combo boxes on continuous subforms which in theory is what i need. but i'm struggling to implement it because the join tables will only Contain the ID's and not the actual values. I imagine there is a fundamental I am missing here.. just not sure what it is.

[Pardysound]

It doesn't make sense to have the tblEventDate table just simply write the date inside the tblEvents.
I am attaching the structure as I would see it.

Hi, My thinking behind having the table for just the event date is because of how I plan to do the data entry. I want a form that is based off of a date, that contains a subform that lists the events on that date, and a nested subform that show the roles and technicians depending on which event is selected. Is there a better way to accomplish that ?

I am struggling with how deal with the Events/Technicians/Roles..

I have a form based on technicians with a Subform based on TechnicianRoles where I can add roles to each technician.
what I need to do next is when assigning a technician to a given event, i first select a role, then I just want to have the Technicians who have that role assigned show up in the combo box. Ive looked into cascading combo boxes on continuous subforms which in theory is what i need. but i'm struggling to impliment it because the join tables will only Contain the ID's and not the actual values. I imagine there is a fundamental I am missing here.. just not sure what it is.