Record Existance Across Forms/Tables

[isoquin]

I have a gigantic database that I had to split across two tables (I'd normally use one, but it hits the max number of items in a table). I built input forms for data entry, which are similarly split (the user needs to hit a "go on to part 2" button).

In the past when I've used one table for everything, I just include:
DoCmd.OpenForm stDocName, , , stLinkCriteria
and that works fine.

But now that the data is across two tables, I need to
1) check to see if a record is already in existance by a primary key [Subject Number] in the second form/table, and if so, open it, or
2) create a new record in the [Second Table] and have its unique [Subject Number] equal the [Subject Number] of the first form/table to keep things consistent.

I know there must be something I'm missing here. I'm pretty new at this (as you can tell), so I would greatly appreciate anyone helping out .

[Scott Price]

What does your table metadata look like?

Here is an example of how to post table MetaData :
Table Name=tblStudent

 
         Field;                  Type;             IndexInfo
StudentID;           AutoNumber;         PK
Family;               String;            FK
Name;                String
University;          String;             FK
Mark;                 Numeric
LastAttendance;        Date/Time

If you have a gigantic database using only two tables, I suspect you really need to read the tutorial dealing with Normalisation. Any concepts you don't understand we'll be glad to help try explain.

[isoquin]

Thank you, Mr. Price.

I read the article, as you requested. I believe my problem is that I don't exactly know how to breakup the data. Basically, the database needs to record a questionaire. Each user is anonymously defined by a unique identifier.

It basically looks like this, but much longer:

Type        Text
Age      Number
Education   Text
Language   Text
Employed   Yes/No

Each item is "atomic", as the tutorial defines it. There are just a lot of them, and each user answers all questions.

Here's a concept from it that I don't understand, even though it seems so basic: Once a relationship is defined... then what? Let's say I break all these questions into two tables, and link the primary key subject identifier to a foreign key in another table... what does that functionally do to the setup? Can I code differently in VBA?

I feel so illeterate in this subject despite years of coding experience in other environments. Perhaps my problem is that I'm tackling it from a coding perspective, just looking for the right syntax to do what I want, instead of doing it the right Access way? Any help is appreciated.

[Scott Price]

Hmm...

With your situation, have you tried going into Access's table analyzer to see what it recommends as far as breaking your table into smaller pieces? That would be under Tools-Analyze-Tables in MS Access 2003.

The idea behind data normalization is that you should only have to store one piece of data in one physical location. The rules referred to in the tutorial modify and extend this basic premise.

The concept of PK/FK relationships is to relate data in one table to data in another table. For example, with your situation, the people who are completing your questionnaire are anonymous, but identified with a unique #. This should become the PK of your tblRespondants. The next table can include questions, with a field QuestionID to uniquely identify each question. The third table will link the above two in order to 'relate' each RespondantID with each QuestionID.

With your situation, however, you are going to run into space limitations when you try to store more than a certain number of entries to any one table... The solution may be to store historic data based on a certain date range in one table. How many entries are you storing? How many are unique (i.e. are you duplicating records knowingly/unknowingly)?

The size limits for MS Access are fairly generous for most applications: 2 gigabytes stores a lot of data. Each table can hold 255 fields... How many fields does your table have?

[Scott Price]

To clarify the my post a little more: are you running into field constraints (i.e. too many fields for one table) or record constraints (too many records for one table)?

To address the first, field constraints, try breaking the questions up into subjects, like personal questions, i.e. age, etc. Then educational questions, etc... This will bring your field # down below the 255 limit. Make sure to identify each table with a unique Primary key ID field. (and do you really have that many questions? and do you really find people that are willing to answer them all? Incredible :-)

To address the second, you will need to try the historical table based on date range idea.

I'll be away a few minutes here, gotta run make lemon curd... no way to buy it down here in Peru!

Scott

[isoquin]

Thank you for your patience and help in this matter.

I should clarify a bit. First, it's not the number of records. I haven't even started entering data. I haven't reached the end of the list of fields in design view (there are ~25 blanks, and I'm using ~225). HOWEVER, the problem comes with editing/saving. Let's say I go to add on field #226. Then I go to save and get:

"Property Value is too large". So I can't save.

I could try breaking it up into different sections, but that seems slightly artificial. Also, as soon as I move into linking multiple tables together, that's where I'm weak and don't know how to relate them (especially on a form, and transitioning between multiple forms as there are too many questions to fit on one).

As for the background, they pay people to answer the questions (that helps). Not all are necessarily answered, as there are some followup questions only if a person answers "yes" to something specific. However, it's not like there's a ton of unique data repeated (as in, I don't see the need, for example, for another table just to have the 5 most common ethnicities).

Again, thank you for the help, and I hope you can continue offering assistance.

[Scott Price]

Thank you for your patience and help in this matter.

I should clarify a bit. First, it's not the number of records. I haven't even started entering data. I haven't reached the end of the list of fields in design view (there are ~25 blanks, and I'm using ~225). HOWEVER, the problem comes with editing/saving. Let's say I go to add on field #226. Then I go to save and get:

"Property Value is too large". So I can't save.

I could try breaking it up into different sections, but that seems slightly artificial. Also, as soon as I move into linking multiple tables together, that's where I'm weak and don't know how to relate them (especially on a form, and transitioning between multiple forms as there are too many questions to fit on one).

As for the background, they pay people to answer the questions (that helps). Not all are necessarily answered, as there are some followup questions only if a person answers "yes" to something specific. However, it's not like there's a ton of unique data repeated (as in, I don't see the need, for example, for another table just to have the 5 most common ethnicities).

Again, thank you for the help, and I hope you can continue offering assistance.

Sorry to not get back to you earlier, I'm on an urgent part of the project I'm working on... Just woke up at 2 this a.m. and can't sleep so thought I'd throw a few more ideas your way.

You mentioned the Error of "Property Value too large" I remember seeing another post on this site dealing with that, but can't remember where it was... You could try searching on those keywords, or on something like "table size limits" etc.

I think, though that ultimately your solution is as I said earlier, to break up the main table into smaller ones.

Two ideas in this line: you mentioned that some questions are 'secondary' or will only be answered if the respondent answer Yes to a certain question. Suggestion is to make second table that looks like this:

SecQuestionID AutoNumber PK
SecQuestion1 Text
SecQuestion2 Text
Etc.

The way to link these two tables together, then, is to place in the first table a field name SecQuestionID with the attribute Number. Then in your relationships window drag the one SecQuestionID to the other thereby creating a relationship between the two tables based on that field.

Second idea... Ethnicity (and all other multiple choice questions). If you examine the data that will be entered into your tables, you will see perhaps more clearly if your data is repeated or will result in a preponderance of blank spaces. In other words, if you have five choices for Ethnicity, the incorrect approach is to list each in a unique field in your main table. This will result in leaving 4 blank for most people (if some people are to identify themselves with 2 ethnicities we will accommodate that later on)... Making a table Named Ethnicity with the following structure is a much better approach:

EthnicityID AutoNumber PK
Ethnicity Text

In this table, you will then have only 5 records (or however many options you are presenting for this choice) Back in your main table, again you will link these tables by placing a Number field named EthnicityID and in the relationships window dragging the two together to create this relationship also.

Your other multiple choice questions should be handled in the same way. This will make your main table look something like this:

RespondentID AutoNumber PK
Main Question1 Text
etc...
SecQuestionID Number FK
EthnicityID Number FK
EducationLevelID Number FK
etc...

At this point we are only trying to create a good foundation for your database. The tasks of entering the data (responding to the questions through queries and forms) will be far simpler IF at this point you create a good structure. It may seem artificial at this point, but it will result in a database structure/foundation that is much much easier to work with and maintain down the road.

I won't be able to check back to this post until at least Monday. Good luck until then. Please ask whatever question is not clear about the above explanation and I'll try to make it more understandable.

Regards,
Scott

[isoquin]

Thanks again Scott. I'll search around for that error. As for fields such as Ethnicity, I have a single one, as a dropdown menu (list box) with specified row source hard-coded in the field design.

Ok, so once I set up a relationship, how do I go about coding between the two tables? Let's go with the classic example of having a store. One table for employees, one for products, one for customers.

Let's keep it simple: on the form, I want the user to specify and record into an "Item Bought" table the time, date, product number, customer ID. However, upon inputting the product number, which is linked to the product table, I want information found in the product table to automatically fill in a few blanks on the table, to inform the user the ID matches the item in mind. For example, user enters item ABC sold to customer XYZ. The form should lookup ABC, automatically display on that form the corresponding price, color, etc found on the [Product] table, and the corresponding customer name, address, phone, etc found on the [Customer] table.

I think once I get through this step, I'll be in a much better position to tackle Access (as if VBA wasn't confusing enough!).

Thank you again, in advance, for all your help.

[Scott Price]

You're quite welcome, and I'm glad to help where I can!

As to explaining the things you were asking about, I could explain the process of implementing those examples in MS Access, but you won't be any better off for the explanation... I'll put it this way: trying to implement a database before designing it is like a blind man trying to play tennis... if he flails wildly long enough, he might hit the ball a few times, but will never be a real tennis player (not trying to be offensive, just trying to stress the point).

The best advice I can give you is to buy and read cover to cover Database Design for Mere Mortals, second edition by Mike Hernandez (look on Amazon.com, I think I've seen it there for around $35 US). Mike covers the subject in detail and in a way that is accessible to anyone who is interested in or has to work with databases. You need to have a good grasp of the design concepts and process before moving on to implementing it.

I'll try to give a brief resume of the core concepts:

RDBMS is a term you may have seen, it means Relational Data Base Management System. The core of any database program/RDBMS is it's ability to store and manipulate data based on the relationships between pieces of data. If people don't want to take advantage of the power of an RDBMS, they should use a spreadsheet to store their data.

After deciding what data you want to store, and breaking it down into subjects, you need to decide the relationships between these data before doing anything else.

There are three relationship forms: one-to-one, many-to-one and many-to-many. For example: when you look at a Customers table, it will be related to an OrderDetails table in a many-to-many relationship. This means that many Customers can have many Orders. To handle many-to-many relationships you need to make three tables: tblCustomers, tblOrderDetails and a linking table, tblCustomerOrders (one-to-one and many-to-one relationships are handled without the linking table).

I'll leave this explanation at this point, because to go any further would be counterproductive.

Again, please read the book I mentioned above. A good book for the implementation phase of your database is: Special Edition Using Access 2003, written by Roger Jennings and published by QUE. Don't be put off by it's thickness :-) When you're needing books about this subject, the thicker the better! I have both of these books on my desk right now, and refer to them both frequently...

Back to the specifics of your db, we can try to look at it further if you like, but I think you'd probably be better off reading first.

If you want more specific help with what you are working with, could you post a sample (say 20 or so) of the fields you have in your existing table? I could then give a suggestion or two on how to break them up into smaller tables (if possible). Not knowing exactly what you are working with now means I'm shooting in the dark, and may be misunderstanding what you're trying to do :-)

Kind regards,
Scott

[isoquin]

OK, first problem solved!

So, it was a size-save error, but not the kind you'd expect. While I didn't hit the max 255, the information in the ~225 I did use was pretty packed. Most fields had comments, some had hard-coded lists in them (Lookup = Combo Box) --> (Row Source Type = Value List) --> (Row Source = "option 1";"option 2";"option 3"; etc)

I did it this way because the different options were slightly different for many of the questions, and there were about 2-4 options per question. So to help solve the problem, I consolidated the few that did happen to have the same answers, set row source type to "Table/Query", and the row source to point to a small table I made elsewhere.

OK, problem #2. The core concepts that you mentioned I'm already familiar with. I understand how to set up relationships, how they relate to one another, etc. Going back to the store example, I need 'one to many' for almost everything. My question is, once those relationships are set up, given one specific form (using the store example again), how do I:

1) automatically have the form pull information from multiple tables to display automatically? Is this VBA bookmarking and just coding the update for specific fields on the form while leaving others the same?

2) Get a single form to write to multiple tables. Currently the form itself looks like it's tied to one specific table. The name of any given field in that form points to that table. However, I want to record the event (lets call that table [PURCHASE]) which will contain the customer ID, employee ID, item ID, date, comment, and I want to record an employee commission in a different table, [PAYROLL] with the date, employee ID, comission made. So currently I can write data to one table (the one that's linked to the form, which I find in the properties of the button in the corner between the rulers). How do I get the same form to write to a completely different table?

I understand your analogy, and take no offense to it, but I don't think I'm as blind as you may think. I understand the concepts (while I may not know the industry names/language of things), and I understand everything you're throwing at me.

[hyperpau]

OK, first problem solved!

So, it was a size-save error, but not the kind you'd expect. While I didn't hit the max 255, the information in the ~225 I did use was pretty packed. Most fields had comments, some had hard-coded lists in them (Lookup = Combo Box) --> (Row Source Type = Value List) --> (Row Source = "option 1";"option 2";"option 3"; etc)

I did it this way because the different options were slightly different for many of the questions, and there were about 2-4 options per question. So to help solve the problem, I consolidated the few that did happen to have the same answers, set row source type to "Table/Query", and the row source to point to a small table I made elsewhere.

OK, problem #2. The core concepts that you mentioned I'm already familiar with. I understand how to set up relationships, how they relate to one another, etc. Going back to the store example, I need 'one to many' for almost everything. My question is, once those relationships are set up, given one specific form (using the store example again), how do I:

1) automatically have the form pull information from multiple tables to display automatically? Is this VBA bookmarking and just coding the update for specific fields on the form while leaving others the same?

2) Get a single form to write to multiple tables. Currently the form itself looks like it's tied to one specific table. The name of any given field in that form points to that table. However, I want to record the event (lets call that table [PURCHASE]) which will contain the customer ID, employee ID, item ID, date, comment, and I want to record an employee commission in a different table, [PAYROLL] with the date, employee ID, comission made. So currently I can write data to one table (the one that's linked to the form, which I find in the properties of the button in the corner between the rulers). How do I get the same form to write to a completely different table?

I understand your analogy, and take no offense to it, but I don't think I'm as blind as you may think. I understand the concepts (while I may not know the industry names/language of things), and I understand everything you're throwing at me.

I'm kinda bored today so I'd be happy to do something.
can you send me a copy of your MDB file and I could look at it. I understand
what you're trying to do here. But to confirm, you said that you only need one-to-many relationship regarding the orders, right? meaning, one customer can only order one product in a form? that's easy then. although, i don't know why you would choose it this way.

And I think i can picture why your table has a lot of fileds. If you could email me your access file, I can make some changes to its structure, and then send it back to you. From there, you could go on and explore it yourself, and i'm sure you would learn a lot from it.

email me a copy of your mdb file.

[Scott Price]

I´m very glad you didn´t take offense at my remarks :-) I wasn´t sure how much you understood, nor was I wanting to start an online database design class...

As far as pulling information for your form from more than one table, you will create a select query that pulls each field from the different tables that you want to display-update data for in the underlying tables. I´m guessing that you will actually want more than one form, which will result in more than one query. Having as many questions as you have, you´ll have trouble designing a form that will be able to display them all! You will likely make heavy use of combo boxes, as they are the most flexible for the type of multiple choice questions that you will have. You will be able to program into the afterupdate event of your combo boxes the ability to make visible-invisible other combo boxes and or forms based on the users choice in the combo box.

None of the above things are hard to do. I´m sure once you do it once you´ll have no trouble at all following up.

I´ve got to be out of town for awhile, so won´t be back to this for the next week.

The specifics of creating your query and basing your form on the query and then later binding your forms controls to the fields passed on by the query are easily discovered with a little poking around! Good luck.

If you want to take hyperpau up on his suggestion, go ahead, but I´ll check back here as soon as I can.

Regards,
Scott