Form: link data in an unbound box to a table???????

[Dedicated]

Created several unbound text boxes in a field to calculate results on yes/no fields, not realizing that the data captured would not be stored in the table or query. Yep, I'm a novice! Only after 325 records later did I realize I made a BIG mistake! I created only 1 table and linked that table to a query. On the form I summed the fields for each session -

Table: tbl_EP
Qry: qry_EP
form: frm_EP

S1: Abs([Apple1]+[Orange1]+[Melon1]+[Pear1]....[Peach1]) S2: Abs([Apple2]+[Orange2]+[Melon2]+[Pear2]....{Peach2])

I did this until I had all 8 sessions. Note, these fields are hidden in the form. I also had a field [TS](linked to the table) for supervisors to plug in # of sessions per audit. This would allow me to calculate the unbound fields.

Then I created my calculated box:

Total Possible: Name: PETotalP Control Source =8*[TS], Total Correct: Name: PETotalC Control Source = ([S1]+[S2]+[S3]+[S4]....[S8],
PE Score: Name: PEScore Control Source = ([PETotalC])/([PETotalP]) Format Percent.
TS = [2]
End result: Total Possible [16] Total Correct [14] Score [87.50]

FYI: My queries are strictly QBE, not SQL.

How do I tie my data to the table without losing everything? Do I need to create a seperate table and link it to my main table? I welcome any suggestions at this point.

This woman is totally lost, so any suggestions would be very, very grateful and appreciated right about now.

Thanks.......

 

[fneily]

I get suspicious when someone says that they have one table in a database. Did you go through Normalization? For your problem, I sketched two tables, one holding the audit info and one holding fruit yes/no. Connecting on the primary key of the audit table, counts then drop right out. Also, it's not "normal" to store calculated values. Tables should hold only raw data. Maybe someone will give you an Access answer, but you may want to look at Excel's filtering and pivot table. You can learn that in an hour and get what you need. With Access you have alot of studying ahead of you.

 

[willir]

Dedicated

Only after 325 records later did I realize I made a BIG mistake!

How far are you into the project??

Have you read up on Relational Databases - Normalization, Creating Relationships...

http://www.abcdataworks.com/developers.htm

or

http://r937.com/relational.html

And Micro$ofts documentation...

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467

http://support.microsoft.com/default.aspx?scid=kb;en-us;283878

You may be heading into a lot of frustration.

I have seen others walk down this road before, and end up with a major headache - dozens of SQL statements 6000+ characters long, high maintenance, poor flexibility.

You use "fruit" in your explanation. So let say, you add another fruit - watermelon - now you have to change the table, change all affected tables to include watermelon, change all queries, and change all reports - ouch, ouch and ouch!

To answer your question...

How do I tie my data to the table without losing everything?

You can create a separate table and use a one-to-one link between the two. This will allow you to go beyond the 255 field limit for a table.

...but you may be walking into a whole lot of hurt.

Richard