Question for Jeff Boyce

[ridgerunner]

What types of issues/problems can I expect or look for if I attempt to change
a field that is a lookup up as result of a combo box to a static field at the
table level? My data entry form captures only a numeric reference to a
question and places that into the database. Problem is, as you said, they
decided to change questions and now I need to capture the actual question (I
have separate question tables to pull from) and place it into the database.
This will be something I will have to work on long term while I "patch"
(scary) things to keep the data entry part moving.
Many thanks for your patience,
ridgerunner

For reference below is part of a discussion from July. I thought I should
start a new string.

Subject: Re: IIF SELECT in Row Source 7/31/2008 2:44 PM PST

By: Jeff Boyce In: microsoft.public.access.tablesdbdesign


I wish you (and whoever "gets" to maintain your database in the future) all
the best ... and if you happen to be the one to return to do some work on it
in 6 months or a year, I hope you remember that "what you see is NOT what
you get" when you work in those tables.

Although it might be a painful lesson, I suspect this falls into the "pay
now or pay later" category...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

I may be interpreting your description too literally.

It sounds like you are saying that the actual question (?a text string?)
will be stored, rather than the ID pointing to that question. That would
seem to imply that the same text string would be inserted over and over
again, each time someone else's record needs to include that question.
(think ID, not text!)

Since Access is ACTUALLY storing the ID in that "lookup field" anyway, the
only difference you'd probably see is that the ID would show, rather than
the "looked-up value". If you have a form with a combobox that displays the
looked-up value (and stores the ID), that's all that comes to mind.

Good luck!

(you don't need to limit yourself to one responder when you post ... there
are a LOT of eyes and brains in these newsgroup and you'll get a broader
range of responses when you don't address a specific individual)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ridgerunner]

Yes, the actual text question would be stored, but I also need to keep the
question ID. Can I accomplish that by using two combo boxes on my form? One
to bind the ID column and one to bind the question column? I really
appreciate your help.

Thanks,
ridgerunner

 

[Jeff Boyce]

I didn't make my case strongly enough ...

If you want to get easy (and good) use of a relationally-oriented product
(e.g., Access), you can't feed it spreadsheet data. Replicating the text of
the question over and over is usually a bad idea.

Maybe if you explained what having multiple copies of the same text would
allow you to do will give folks here enough information to be able to offer
more specific suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ridgerunner]

I'm sorry I am not explaining this well enough. I started out with one table
of questions but the users decided to change some of the questions, which
requires new question tables and now I have to have different add forms, edit
forms and reports everytime they want to change questions. I hoped that by
putting the actual text into the database I could I could have one edit form
and one report that would pull the questions from the database rather than
the tables. The report has six subreports.

 

[Jeff Boyce]

Are you saying that each question is a different field in your table?
That's how a spreadsheet works, but not a relational database.

I'm going to send you off to look at something Duane H. created to help
folks generate surveys and tests. His design offers a well-normalized
structure that accommodates new questions without requiring a total redesign
of the database (tables, forms, queries, reports, etc.).

See:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ridgerunner]

Sorry I had to be gone for awhile. Thanks for the link. Each question is
not a field. Below is a simplistic representation of how the data looks when
it is printed in a report. There would be no problems if they left the
wording of the questions alone; and did not add or delete them.
SALES
Question 1 Score
Question 2 Score
Question 3 Score
Subtotal

PRODUCTION
Question 4 Score
Question 5 Score
Question 6 Score
Subtotal

Total

 

[Jeff Boyce]

Thanks for the description of the report... but it all starts with the data.

I'm just not getting it yet. It would help (me) if you could provide a
description of the data structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ridgerunner]

Thanks for the description of the report... but it all starts with the data.

I'm just not getting it yet. It would help (me) if you could provide a
description of the data structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ridgerunner]

I am sorry, I sent a blank post before this one.

tblInspections
InspID
StoreNo
InspDate
DMnameID
Store Mgr
OverallComments

The table below is the first question table we had until I was notified they
wanted to change some of the questions under some of the categories. I am
creating new tables, forms and reports that use data from the table that was
in effect at the time of the inspection.

tblQuestions
QstID
QstWithPtVal
DMCatID
QstSortOrd
DMCatSortOrder

The table below contains the results of each inspection
tblDMDet
DMInspDetAutoID
InspID
DMCatID
QstID
Score

 

[Jeff Boyce]

Maybe I'm suffering Monday mind ... I'm having trouble understand how you
are getting from the tables/fields you describe below to the report outputs
you mentioned in a previous post. It might help to have some idea of the
query(ies) you are using to gather data for reporting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ridgerunner]

Does it help for me to explain that each category (Sales, Production,
Customer Service, Cleanliness, Safety and General) has a subreport and that
data is gathered through queries and they are all pulled together into a main
report? (Not to mention other data and subreports we do not need to go into
here.) The subreports and main reports are the worst to re-create with the
references to the different table of questions. If we do not use different
tables of questions, the reports will no longer print the applicable
questions. It is complicated and is becoming more so every day - I explained
to my boss that I agree with you...this is no longer a true relational
database application, but we will have to see this through to completion
(whatever that means). What we really should be doing is gathering the
detail data on a spreadsheet and capturing the category subtotals into a
database, but the users want only one application to deal with. I really do
appreciate your time and understanding on this project. I have learned a
great deal and hope I have not been too much of a headache.

 

[Jeff Boyce]

I'm not sure if I'm understanding, so my suggestions may not fit...

It sounds like there may be confusion between what the users see (reports,
forms) and what the database holds (tables). To get the best (*and
easiest*) use of Access' relationally-oriented features/functions, you can't
feed it 'sheet data.

But that's not a problem, since you can store data in a well-normalized
table structure (in Access, in SQL-Server, in ...) and the users never have
to see it! Your task, should you accept this mission, is to provide a
user-friendly graphical interface (forms, reports) that hides the actual
data storage.

If your data isn't well-normalized (or if this term is not familiar), spend
the time to climb this learning curve. Everything else you try to do using
Access will depend on having done this. As I mentioned back at the top, pay
now or pay later <G>!

The comments below are a bit too general to be able to offer any specific
suggestions on.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP