Data entry/query running issue

[jk42]

Hi!

I'm designing my form and I have a problem. I have five combo boxes for which the user to pick the design of the project. That's all fine and good, but the problem arises when I want to run a query. There is no rhyme or reason to the design of the projects. By that, I mean, the order in which I input the design isn't consistent. So column 1 for five projects might have: A, B,B,C,D- So when I ran a query of all five columns, and put in the design type, for example, "A" in each column, it didn't come up with anything, because none of the columns had "A" in every column. Does that make sense? ANyway, I'm about 70 of 600 in of entering the design of the project, and before I do anymore, I need to know if I need to be strict about the order that I input the design, which would still be very hard because there are probably 25-30 options, and I just can't have that many boxes on my form!. Does column one need to ALWAYS be "A" and if "A" isn't applicable, just leave it blank? or is there a smart way to run a query to where that won't matter?

Thanks in advance,

 

[MarkK]

Sounds like that data should be in a related table, not in the main table. If one thing is related to many things, then you need two tables to represent that reality. Maybe do some reading on Database Normalization and see if that helps.

 

[jk42]

Yeah, I may. It wasn't a problem originally, but the requests of the person using the database keep changing/adding things. It's hard to deal with since I'm so new, each new request brings on a whole host of learning challenges. So if I create a many to many relationship, thus requiring a junction table, won't I then have to have a sub form within my form? I just really don't want to do that! Is there any other way?

 

[jk42]

ok, so I resigned myself to trying to figure this out with a seperate table.

What I did:
1.Created table that is called Project Design
PKProjectDesignID

2.Created a junction table that has
PKProjectID_ProjectDesign
FKProjectID
FKProjectDesign

Linked them all (ProjectDetails and ProjectDesign to Junction table)

Then, I went to my form where I enter in all of the Project Details, and tried to add a subform, based off of the tblProjectDesign. It's not really working.. in addition, I'm not sure in which form I need to create multiple rows to allow for each project to have multiple things selected in it for Project design. I tried to do it in the tblprojectdesign where all of the choices are listed... and that didn't work. Any help is greatly appreciated!

 

[jk42]

Never mind! I think I got it to work: Do any of you see any issue with how I did it?

Created the subform off of TblJunctionProjectDesignchoice and put it in the projectdetails main form. So I then could run a query and find specific project design.

 

[MarkK]

I can't tell from your description how you solved that, but are you getting the results you wanted? Is it easier now to record multiple designs against a single project?

 

[jk42]

Yes! I am using a subform on the main form, and I think that will work. Now I just have to figure out check boxes :/