Question for 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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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.

"ridgerunner" wrote:

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: 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

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

"ridgerunner" wrote in message
...
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.

"ridgerunner" wrote:

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: 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