Multiple fields on the same form

[andrewhoddie]

Hi all

Hopefully someone will be able to point me in the right direction with a problem I am having.

I have two tables, one called Questions, this has a list of 100 questions that I need to ask each quarter. The fields are

ID (Autonumber), KPIID, KPIDesc, KPITarget, KPIOwner

Example data

ID KPIID KPIDesc KPITarget KPIOwner
1 1 Type A Sales 100 Ben
2 4 Type B Sales 50 Chris
3 5 Type C Sales 60 Nick
4 6 Type E Sales 40 Ben

The second table is where I would like to store the results and is called KPIResults. The fields in this are

ID (autonumber), KPIID, KPIYear, KPIMonth, KPIResult, KPIAgreedDate

Example of how I would like the data to look once completed

ID KPIID KPIYear KPIMonth KPIResult KPIAgreedDate
1 1 2017 Jan-Mar 95 01/04/2017
2 4 2017 Jan-Mar 55 01/04/2017
3 5 2017 Jan-Mar 61 03/04/2017
4 1 2017 Apr-Jun 102 02/07/2017
5 4 2017 Apr-Jun 59 03/07/2017
6 5 2017 Apr-Jun 58 03/07/2017


The KPIID will link the two tables.

For the user to enter the data I have created a form called KPIEntry with tabs to breakdown the questions onto separate tabs. The format that I want is:

On the page to have the KPIYear and KPIMonth fields, then on each tab (one tab per owner) to have the KPIID, KPIDesc, KPIResult and KPIAgreedDate for each question they need to provide data for.

The problem I am having is getting the list of questions on the same tab per user.

For example what I would like for Ben is (Does not need to be a table format just easier to keep it clean for the example)

KPIID KPIDes KPIResult KPIAgreed
1 Type A Sales
4 Type E Sales

which would be completed and then saved back into table KPIResults. I can only get one question per page. When I try to change the sheet type to continuous it shows all questions and if I try to add duplicate fields it just shows the same value. I am trying to avoid having to create a large table with one row per year and month combination and over 100 columns for the questions. Can anyone help on this?

Hopefully I have been clear in what I am trying to achieve.

Thanks in advance
Andrew

 

[Micron]

Admittedly I skipped over some of what you want to do because I don't agree with the setup. Firstly, I would not have a field for year and one for month since there's not only no need to separate them, you'll only complicate things. If you really want to see them apart on a form, 2 controls would use built in functions to split the month and year parts. If you need to update any of the form info, then the split data might need to be unbound, while the control that contains the full date is hidden, because IIRC, calculated controls can't be updated. More importantly, if I understand the drift, a page on a tab control for each user is just a bad idea. Add or remove a user and you are opening forms to alter design to accommodate the new/gone person. Maybe you're altering code or macros to suit as well. A combo in the form header or top section should allow the picking of a user, and the questions pertaining to that user should go on the tab control pages. No idea how you'd want to break them up, so I can't suggest how right now.

Not sure I see a need for splitting the KPI data as you have shown. You say the 1st table is for questions, yet I see nothing about questions in it.
If a KPI is an entity, and if the dates, responsible person, type, description etc are all attributes of the KPI, there's no need to split the data. If for example, the dates or responsible person are not part of the KPI attributes, then those unrelated attributes should be in their own table(s). At this point it's unclear as to what your data really looks like and how any of it relates to particular questions.

 

[andrewhoddie]

Hi Micron, thanks for the reply, the date and the combo make perfect sense and I can adjust the form for these.

The reason why I was wanting to split the questions and the results was to try to reduce the number of columns in the table. In the questions table I have about 100 questions so thought it would be cleaner to have them split up. There are a few more columns in the question table, sorry if I should have included them, I was trying to keep the post as brief as I could. Would you recommend just the one table?

 

[welshgasman]

PMFJI,

You would not have a column for each question.
You would have one row for each question.

 

[Micron]

Your questions indicate you don't understand entity relationships, so I suggest you read up on normalization. Yes, questions go in rows - in their own table. Your post is kind of confusing to me because you show fields that supposedly belong to a questions table, yet there's no questions field. Suggest you read up on normalization and post back with an explanation of your schema if you want opinions on it.
Here's everything I've got in terms of links for common Access advice, some of which are not about normalization, but could help you avoid certain traps
Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/2008/12/what-is-normalization-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/2009/05/how-do-i-create-application-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

 

[xenou]

I would agree you want the questions to be in a row-oriented table:

------------------------------------------------
| QuestionNum | Question                       |
------------------------------------------------
|           1 | What is your name?             |
|           2 | What is your quest?            |
|           3 | What is your favourite colour? |
------------------------------------------------

Having 100 columns for 100 questions is nothing short of a nightmare for creating forms and reports.

Also I'd want to have a single form, and then just pick a name and that's all - instead of a tab for each user just one form that can display any user as needed.

 

[Micron]

Xenou, you spell like a Canuck!
I'm in good company.

 

[andrewhoddie]

Thanks very much for links and tips, I have read the information on normalisation and I think I now have table relationships working as I had hoped. I now just need to spend a little time working on the form design to get exactly what I want.

 

[Micron]

Good luck! Should you have questions on your design, methinks that would be a new thread.