I have an input form which requires a large number of input variables. In fact it requires over 300 inputs. This exceeds the number of fields available for one table. Therefore, I have to split the data into 2 tables. I am thinking that in order to create one input form I would create a parent form with the first ~200 input fields (from table1) and a subform with the remaining ~100 fields (from table2).

Additionally, this both forms have a number of tabs. I want to create the subform so that its tabs are at the same level as the parent form. I don't want to bring in the subform so that it is all on one tab of the parent form (the subform has a number of tabs itself)

How do I do this?

Thank you for your advice.

Dave

Hi Dave,

"In fact it requires over 300 inputs. This exceeds the number of fields available for one table. Therefore, I have to split the data into 2 tables"

I rarely have a table with more than 30 fields ... sounds like your database needs to be normalized.

some good reference posts (in no particular order except mine is first <smile>) are:

• Crystal's ACCESS Basics for Programming
  
• Database planning tutorial Part I
  
• Database planning tutorial Part II
  
• argeedblu's FAQ on Design Strategies
  
• Noah's FAQ on Normalization
  
• Jerry's FAQ on Normalization
  
• GroverParkGeorge's PDF on Normalization
  
• Jerry’s FAQ on Fully Qualified Naming
  
• Jerry's FAQ on Glossary
  
• Danny's FAQ on Reserved Words
  
• Autonumbers - What They Are / Are Not
  
• Primary Keys - Natural vs Surrogate
  
• Naming Conventions - Hungarian Notation
  
• Database Answers (free models)
  
• MSKB 283878: Description of the database normalization basics
  
• The Evils of Lookup Fields in Tables

Edited by: strive4peace2009 on Sat Jan 3 15:10:08 EST 2009.

If you need any further help or explanation, feel free to post back.

Robert

Thank you for the advice. You are quite reasonable to be suspicious of such a large table.

Yet, truly, I have an input form with 350 separate data inputs. Therefore, I don't believe normalization is the issue. I did need to split the iinputs into 2 tables joined by a ClientID field.

Therefore, I do need to understand how to deal with the Tab issue.

Of course if you have a better solution (other than using subforms) I am quite open.

Thank you for looking at my post and taking time to offer advice.

Dave

Okay, if your data are normalized and I understand you, you have tabs on the main form and tabs on the subform and you want them to line up horizontally. Is that correct?

By the way, are you adverse to scroll bars (a logical solution it seems to me, but some people don't like them)?


Robert

Hi Dave,

can you tell us what kind of information you are tracking?

Robert,

Thank you. Yes, I believe you understand my situation. I am not adverse to any solution which a sage such as yourself may suggest. Yet, I believe that a Tab solution is most visually appealing.

Thank you

That is not necessarily true. You can have 350 separate data inputs and yet not be normalized. It would be great to see your database structure. Perhaps someone here can make some suggestions as to a better way to do it which will solve your problems on many accounts. As Crystal has said, I also am skeptical about anything over about 30 fields and many times people have told us it is normalized when in fact it is not.

Crystal,

Sure. The database is basic client input data for low income people who have been subject to violence. So the inpput data is: basic contact data, history of abuse, history of violence, incarceration, history of drug use etc.

It really does add up to 350 separate data items per client.

Regards,

Dave

Bob,

Thank you for offering to look at my data structure. I've attached the database here. If I have made a novice mistake regarding normalization, I certainly appreciate your advice.

Regards,

Dave

Actually I will challenge that you have it normalized then. I will bet you that not all fields are applicable for each person. Yes, if someone has a history of drug use then the drug use history will be applicable. So are you storing more information than just a YES/NO about a history of drug use in that table? If so then it should be external. Do you store any information about the history of violence, other than just whether there has been a history of violence (yes/no)? If so then I challenge that it should be as a separate table. And that goes on for everything.

Hi Dave,

I have no doubt it adds up ... but consider this:

if you are tracking history, you probably have multiple fields allocated for multiple reports

for starters, I see these tables...

Clients
Phones
Addresses
eMails
Abuse
Violence
Drugs
etc

read Access Basics and pay close attention to the Normalization and Relationships sections. It can't hurt and can possibly give you some ideas ...

Dave:

I have to go do some grocery shopping, but I'm sure Crystal will probably have some suggestions for you. I took a quick, cursory glance at the database structure and there is definitely some fixing that I would suggest. Your intake is set up more as a spreadsheet than in relational database terms.

I'll comment more in a while.

Hi Dave,

now I have a look at the db you posted. In just structure for Intake_Data1 and Intake_Data2, I see:

- Clients
- contact info such as Phones, Address, -- you can put these in the Clients table if you will not have need to store multiple records
- Intake (or whatever you call info like Arrival Date and Discharge Date)
- Injuries
(this table will have a field for type of injury rather than a bunch of YN fields in the intake table). By using a separate table, you still have the flexibility to store multiple injury types per intake and searching will be a LOT easier.
- Resolution
- Insurance (with type = medical, dental, ... -- multiple records if there are multiple policies)
- Substances
- Medications
- Education
- LivingInfo
- Arrests
- Visits
- Abuse
- FamHistory
- Violence
etc

I am attaching a snapshot report of the fields in these two tables and your lookup tables. Nice that you have filled out Descriptions! <smile>

If you cannot render files in SNP (snapshot) format, here is a link to the Microsoft site to download the SNAPSHOT viewer for Access:
http://www.microsoft.com/downloads/details...;displaylang=en

In your lookup tables, you should name the fields something specific; ID is ambiguous. for instance, in the ReasonsForInjury table, ID could be something like --> InjReasID

Bob, Crystal

Thank you for looking at the database. Certainly, I see that I could break up the tables further. But, that really doesn't achieve better normalization does it? In fact, I would be storing the client ID a number of times, whereas now, I only store it in 2 tables.

Nevertheless, I could break up the tables further and create a separate subform for each tab.

Is that what you are suggesting?

Dave

Thank you Crystal.

I don't see an attachment to your post. I am a bit new to UtterAccess. Perhaps I am missing something.

You are going to need junction tables to unite clients with injuries, substances, insurances, etc.

hi Dave,

look in the message header of my post -- click on the Attachment link to download it

Crystal,

Thank you for the attachment. I got it now.

Bob, thank you for the heads up on junction tables.

I will break up the tables and create subforms for each tab.

Dave

you're welcome, Dave

There should be a distinction made between actual data and references to it. Ideally, we want to store one piece of data *once* and in *one* place and whenever we relate anything to this data or a set of data, we reference it. Thus, the keys. This is also why one of condition for a good key is that the value never changes, and thus surrogate key makes sense so we can accommodate unusual situations such as name changes, change in Social Securities, without losing all pointers to the related data.

Furthermore, I should point out that one-many relationship isn't the be-all & end-all relationship. When you get involved into set theory, it can be more formally defined as zero-many relationship, which for your specific situation is basically zero-one relationship, and thus a many-side table still makes more sense than a giant wide table. Furthermore, this means you get to store your data more effective because you only need to pull in relevant rows whereas in a wide table, you could still be wasting space for many blank columns.



That's what I would have done. Bob & Crystal provided some great advices, and I hope that it helps clear things up for us all.

Banana Republic,

Thank you for the input.

Regarding Jill and Jane... you don't mention what the contest is.

For example, if the contest is the speed with which they can tie their shoes and they are competing side by side in the contest, then they will remain in the same relative spacial position.

Dave

Do feel free to post back if you're stuck or need more assistance.

Sorry, but this doesn't address the fact that they're both accelerating. They're both definitely moving and not just sitting tying their shoes.

Best of luck with your project.

Hi Banana,

interesting question ... are Jill and Jane humans? Is speed referring to bodily movement like walking or running? If so, I can only think that they must be going in circles

If speed is something else, maybe they are both eating pie while falling ...

please do tell us!

Yep, that's it. (Circles).

Although I would love to see them falling and eating pies, Jill eating twice as fast. (provided they had a soft landing somehow)

good one, Banana