Push data from a query into a table

[Ray]

I want to push data (calculations and expressions from
query and form) into a table record. I know everyone says
not to do that and that it is a waiste of resources.
However I have a need for this. So please do not lecture
about how bad an idea it is. I have developed a safety
database which has exceeded the limit of access 2000. I
had to make multiple tables and queries. There are 110
questions with 7 fields to each question and every
question has to be answered. There is a subform based on
the query catagory for each question. Each catagory is
it's own table and query with a unifing query for one
field of each question. The subform creates a record for
each discrepancy. I am basing all my reports on this one
table and making a back up of the table. An inspection
number in this table contains a contactination of a large
number of fields I want to split back out into the table
and include the linked information . any ideas?

Thanks
Ray Lambert

 

[TC]

You are bascially saying, "Please advise me how to proceed in a direction
that you all disagree with". Hmmm!

What "limit" of Access have you exceeded? In particular, why do you imply
that exceeding that limit, caused you "to make multiple tables and queries"?

If you explain what you want to achieve from a functional viewpoint -
instead of presupposing what the technical solution should be - you will
probably get much better help.

HTH,
TC

 

[Ray]

There is a 255 field limit in a table in access 2000. I
have 110 questions each with 6 fields making for 660
fields minimum plus various other fields for additional
information like locations, inspection numbers and so on.
I also found out the hard way that memo fields can reduce
the number of fields in a table you can use down to 128
and still retain all the property information I embeded in
each field. (lot of rethinking there). so I created 7
tables for 7 categories with the questions for each
category. At first I tried a query to combine them all
but again hit the 255 limit. Each Category table has its
questions and related information as individiual fields
(Later I hope to eliminate them as fields and convert them
to a table with VB or SQL pulling the question from the
table and create a form for each one, but that is beyound
my skills at the moment). Then I created a table that
links all 7 categories along with a query that only pulls
the question fields. I merged those onto an input form
that was designed higher in my organization and then
madea form for each question (I know, lot of work, but
for now it works until I learn more programming skills).
Next I took the main form and each question field and made
the question open each subform. The subforms have
calculations and contactinations and also pulls ratial
data from ceratin fields and links them to other tables (
a real monster, but again it works.) The subform also
saves data to the table that I want everything to go to, I
have about half the fields filled by the subforms, but I
want the partial fields to save as individual fields along
with the date calculations. This is the table I will pull
all my reports. Does that help?

Thanks
Ray

 

[Tim Ferguson]

(Sorry TC -- I couldn't resist)

There is a 255 field limit in a table in access 2000.

.... which is about two hundred more than needed in any decently-designed
database.

Have you checked out Duane's classic survey database:-

<http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your
%20Survey%202000'>

Hope it helps


Tim F

 

[Ray]

I have been there and seen that survey, It has given me a
several good ideas for later incorporation, but does not
help me with the current endevor. I have read every post
where someone has asked this, but so far no one can answer
it.

 

[Duane Hookom]

I may have missed something but I haven't heard a good reason why you need
to head in the opposite direction of most recommendations. Even if you have
one an unnormalized table structure, I don't understand why you need 6
fields per question. Are the questions all multiple choice?

I realize that my "At Your Survey" doesn't meet every need for surveys.
There are methods of loading question information into an unbound form
(built to suit a particular user experience), allow the user to enter
results, and then code to push the values into a normalized table structure.
While we tend to think of table structure and user interface as being very
tightly coupled, it doesn't have to be that way.

 

[TC]

No probs, Tim!

Ray, there is no need for anywhere near that number of fields. You need to
rethink the design of your tables. If you have not read it already, this
article gives a dry but instructive overview of the process required:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

I like Duane's point that sometimes, you need to do some non-trivial
processing between the forms & the normalized database structure. It is not
always a simple matter of basing a form on a query that is based on the
tables. Perhaps your case is one of those.

But regardless of whether it is or it isn't, it should *always* be possible
to store your data in a properly normalized database structure. And a
properly normalized structure will seldom if ever need *anywhere near* 255
fields in a table.

HTH,
TC

 

[Ray]

I already know it is not a good Idea, but I still need to
do this. There is a reference in the "Access 2000 VBA
Handbook" on Page 180 'Saving a Calculated Result to the
Database' That refers to a procedure later in the book
that I am unable to locate.