Help with normalization (how to bring the tables back together)

[Mountaineer529]

I created two separate tables to normalize my database (I am probably going to separate it into more tables later). I created a primary key, and created the relationships. Now I need to know how to bring it all back together. So, what is the best way to do this?

I have one problem with the keys though. My database is extremely large, over 30,000 records (rows), and 10 or so columns. However, there is no combination of columns that can make each record or row unique. So I created a surrogate key (called record id). This worked fine for the main table because each record was its own row. For the second table, however, I broke out certain information from one record (row) in the main table into several rows that I wanted to relate back to the record (or single row) in the main table. I did this by taking the surrogate key (record id) and using it in the second table. The way I did this is by duplicating the surrogate key multiple times for the rows related to one record. For example, main table row (record id 10111), and for each row in the second table related to that record will us that record id multiple times (record id 10111, 10111, etc).

Will this work? I don’t see any other way to relate every row in the second table back to its corresponding table in the main table without duplicating the record id. And, like I said, there is no combination of columns that is unique to each row.

Below are some further questions I am confused about.

I want to use a form to search the tables, and pull up all the information in datasheet view. I created a working database based off of this article http://www.allenbrowne.com/ser-62.html. However, I have a large database, and it’s necessary to break some of the columns into rows below the main record (like I said above).



So, my main problems are:

1. I can’t figure out how to combine the two separate tables (more than two in the future) back into one table by utilizing the primary key and relationships I created.

2. It won’t let me put the bottom part of the form in datasheet view. How can I do this?

3. However, it would be better if instead of having the datasheet and search form all on the same form, to have them as two separate entities. So when I search on the form it opens the datasheet separately.

4. For now, however, it would be easier just to put the datasheet as a subform, but I can’t get this to work with the vba code. I’m sure it wouldn’t be hard to change the code to fit the subform. I figured it would just be to indicate the fields are on a subform.

Any info on how to do this would be greatly appreciated. The main questions are the first two above. Can I normalize this database given that each record doesn’t have a unique combination of columns to create a primary key? Will the surrogate key work in the second table given that it needs to be duplicated? If so, how do I bring it all back into one table?

Thanks in advance for any help.

[June7]

Considering that Access could hold millions of records, your db is extremely small.

You don't combine back into one table - build queries and JOIN tables on primary and foreign key fields.

Use form/subform(s) arrangements for data entry/edit.

Use reports for manipulating and viewing data.

Conventional approach with form/subform is to apply filter to main form (say Customers) and only related records (say Orders) would display in the subform.

[Mountaineer529]

A couple of quick questions.

1. I set up the database to be like you described in the last sentence, except they were all on one form. When I create a subform do I just simply drag a query into the bottom half of the main form? Or do I have to use the subform button in the design tab to create the child field and relate the child field to the parent field in the main form that takes the user input? I created the child fields with that button and couldn't get the filter to work.

2. Can queries work with VBA? Because I was under the impression a query couldn't take user input from a form and filter a database.

3. I was planning on going with the form/subform to apply the filter, but can't get the bottom half in datasheet view or figure out how to use the subform. I created the datasheet view as a query and another form, but couldn't get the filter to work. Which would work better to filter in datasheet view?

4. I haven't looked too deeply into reports, since I was going to use the above method, but would it be easier to just have a form that created a report based on user input on a form?

Sorry if these are stupid questions. I'm a newbie.

[June7]

1. A subform/subreport container control can have as SourceObject a table, query, form, report. I recommend form or report because cannot have code behind tables and queries.

2. A dynamic parameterized query can reference controls on form as input - but I never use dynamic parameterized query - this is not VBA, although queries can call VBA custom functions.

3. Don't really understand issue. A subform can be in any mode - single, continuous, datasheet. As I said earlier, conventional approach is to filter main form and subform just tags along.

4. This is not a one or other option. Forms are for data entry and reports are best for data calcs and output. Data must be input to tables for reports to have something to report.

[Mountaineer529]

Thanks. That helped a lot.