List of questions with two results in subform

[Angus_NB]

I am trying to create a form using Access 2007 to collect inspection data. There are a list of 65 questions with two answers that are numeric. Essentially, "How many did you see?" and "How many were defective?"

I have tried to simplify things by using dog breeds and cities, in my question, rather than the real data.

Here is my sample table layout.

Tables:
1. Cities
2. Dogs
3. Results

Fields:
1. Cities:
ID (Autonumber)
City (Text - city name)

2. Dogs:
ID (Autonumber)
Breed (Text - dog breed)

3. Results:
ID (Autonumber)
Date (Date)
City ID (Numeric) from the City table
Dog ID (Numeric) from the Dogs table
Total (Numeric)
Male (Numeric)

I would like to have a form that has City and Date at the top. Then I need a subform that shows the full list of dog breeds at the left with two text boxes to the right of each breed. One text box to collect the total number of dogs observed and the other to show the number of males out of the total.

I have tried using a query as the datasource in a continuous subform. The query being the Dogs table linked to the Results table by ID and Dog ID. That will only show existing records.

I don't know where to go from here.

Thank you.

 

[John Big Booty]

Try changing your table structure slightly to;

Fields:
1. TBL_Cities:
CityID (Autonumber)
City (Text - city name)

2. TBL_Dogs:
BreedID (Autonumber)
Breed (Text - dog breed)

3. TBL_Results:
ResultID (Autonumber)
CheckDate (Date)
CityID (Numeric) from the City table
BreedID (Numeric) from the Dogs table
TotalDogs (Numeric)
Male (Numeric)

In your names avoid spaces and other special characters (limit yourself to Alpha and Numeric characters and the underscore) and also avoid using reserved words as names. Also consider a naming protocol such as TBL_TableName, FRM_FormName, QRY_QueryName etc.

Now on your sub-form I would have a Combo Box to select your Dog breed, every thing else can be entered directly in the form, and so long as you have set up your Form/Sub_form set correctly the two should be linked via the CityID which will automagically be recorded in your TBL_Results.

 

[Angus_NB]

Thanks John.

One thing I have discovered. Because I need to capture more than one dog report per City/Date I am going to need another table.

My original Results table will be dropped to 3 fields:
ID (Autonumber)
IDate (Date)
City ID (Numeric) from the City table

Then I will need another table for Result Details:
ID (Autonumber)
IID (Numeric) Linked to the ID field in the main Results table.
Dog ID (Numeric) from the Dogs table
Total (Numeric)
Male (Numeric)

I do normally use a better naming convention. This was a quick example.

The real data has about 65 questions in 13 categories so drop down combo boxes would be very confusing to use.
I would like to have all questions visible so the people entering data would easily see what questions had been answered and which ones hadn't.
I would like the data entry form to look, as much as possible, like the paper form the users carry in the field.

 

[John Big Booty]

Are you saying that you will be capturing different results for the same City on different Dates? Or different Dates different cities

If it's the former then you shouldn't need to change the previously suggested table structure.

Perhaps a solution to your drop down issue would be to have a List box that hold the questions relevant to the category selected in a Combo box (a variation on a cascading combo box set), now given that you are working with a continuous form (cascading combo boxes will not perform correctly on either a continuous or datasheet form) you would need this to be on a pop up form that then feed your results back into the table and refreshes your sub form.

 

[John Big Booty]

Here's a sample that demonstrates how to use a pop up form to add and edit records to a sub form. It uses a cascading Combo box set rather than a combo and cascading list box, but the principal is exactly the same just swap the second combo for a List box.

 

[Angus_NB]

I will be capturing results for the same list of questions from the same city on the same date.

I need the users to answer two questions about each breed.
There will be a list of 65 breeds.

I was going to attach a graphic to help explain it but I don't have 10 posts yet. I've been a forum member for over 6 years. Maybe after another 4 or 5 years I'll be able to attach a graphic.

 

[John Big Booty]

Are you not able to zip your graphic and post it as an attachment

 

[Angus_NB]

I'll try just attaching the .JPG

Maybe I'll have to add the 65 questions to each record, whether they are relevant or not, just to get them in the list.

 

[Angus_NB]

Ok. Let's forget all about the dogs.

Lets say I want to record the number of days in each month and the number of holidays.
For some odd reason I want more than one record of this for each year so I have to use a subform.

See attached Months2.JPG below.

Tables:
tblMonths = Month_ID, Month
tblData_Main = Main_ID, Year
tblData_Details = Detail_ID, Main_ID, Month_ID, Days, Holidays

The month column is populated from tblMonths.
The year is recorded in tblMain_Data.
The Days, Holidays, Month_ID (1-12) and Main_ID to link to tblMain_Data are recorded in tblData_Details.

I always want all 12 months to show on the left edge of the form along with empty text boxes for the Days and Holidays.

How can I accomplish this?