Rearrange/split tables

I have designed a small database customized for one particular department of
the company. The company uses a much larger database, which I don't know
much about except that it's complicated to use and in my opinion doesn't seem
to be very well designed. In order to assist us somewhat, the IT dept. does
daily exports of only the data we need for our dept., and sends it to us in
the form of an excel spreadsheet. I then convert the spreadsheet to a table
in Access in order to link it with other tables, forms, reports etc. that we
utilize. This all works fine, but due to the poor design of the database
from which we get the data, there is alot of duplications and tables are not
really organized the way I would like. I can't do anything about the way we
get the data, What I want to know is if there is anyway with (code, queries,
something) that I can change things around either in excel before the
conversion or in access afterwards. Following is an more detailed
explanation of what I would like:

The table design Now: RecordNo is PK(autonumber), The SampleID has
duplicates due to the fact that each sample can have several different tests
performed on it, From there, each Test usually contains several SubTests.
Yet all this data is in one single Table. Nothing I can do about that
though, just want to fix it after I get it.

RecordNo SampleID TestName SubTestName
190 60589 Test1 SubTestA(Same as Test1)
191 60589 Test1 SubTestB
192 60589 Test1 SubTestC
193 60589 Test2 SubTestA(Same as Test2)
194 60589 Test2 SubTestB
195 60589 Test2 SubTestC
196 60589 Test3 SubTestA(Same as Test3)
197 60589 Test3 SubTestB
198 60589 Test3 SubTestC
199 60589 Test4 SubTestA(Same as Test4)
193 60589 Test4 SubTestB
194 60589 Test4 SubTestC

195 87254 Test1 SubTestA
etc., etc.

As you can see a lot of duplication. I have a Form which I use to pull up
all the data for Specific SampleID, where the "Test" data is displayed in the
Main section of the form, and the "SubTest" data is displayed in a continuous
subform. It works fine, but due to the table design, If a SampleID consists
of 4 Main Tests which each have 3 subtests, I get 12 records on the main
form, with the each Main test being repeated 3 times, and the correct
subtests show up in the subform, but they show up for each time the main test
is repeated.

I would like for each Main test to only be displayed once in the main form,
and depending on what main test is selected, the appropriate subtest(s) data
appear in the subform. Is there a way I can make the form only display the
data once, or do I need to somehow divide up or rearrange the tables, if so
how can I do that?

Your main db sounds like something I worked with in the past. A Non-First
Normal Form structure. They can be very ugly. Ick!

Anyway, first I would suggest automating your import function. It will be
much easier to push a button and import the file. Then you can use queries
to break your data up. Perhaps something like this:

tblSample
RecordNo (PK)
SampleID
SampleName

tblTests
TestID
TestName (PK)

tblSubTests
SubTestID
SubTestName (PK)

tblSampleTests
RecordNo (PK) (FK)
TestID (PK) (FK)
SubTestID (PK) (FK)

Now you can display only the Sample information, and populate the
subform(s), with Test information, after they select a Sample to view.

HTH

Sharkbyte



"rg32" wrote:

I have designed a small database customized for one particular department of
the company. The company uses a much larger database, which I don't know
much about except that it's complicated to use and in my opinion doesn't seem
to be very well designed. In order to assist us somewhat, the IT dept. does
daily exports of only the data we need for our dept., and sends it to us in
the form of an excel spreadsheet. I then convert the spreadsheet to a table
in Access in order to link it with other tables, forms, reports etc. that we
utilize. This all works fine, but due to the poor design of the database
from which we get the data, there is alot of duplications and tables are not
really organized the way I would like. I can't do anything about the way we
get the data, What I want to know is if there is anyway with (code, queries,
something) that I can change things around either in excel before the
conversion or in access afterwards. Following is an more detailed
explanation of what I would like:

The table design Now: RecordNo is PK(autonumber), The SampleID has
duplicates due to the fact that each sample can have several different tests
performed on it, From there, each Test usually contains several SubTests.
Yet all this data is in one single Table. Nothing I can do about that
though, just want to fix it after I get it.

RecordNo SampleID TestName SubTestName
190 60589 Test1 SubTestA(Same as Test1)
191 60589 Test1 SubTestB
192 60589 Test1 SubTestC
193 60589 Test2 SubTestA(Same as Test2)
194 60589 Test2 SubTestB
195 60589 Test2 SubTestC
196 60589 Test3 SubTestA(Same as Test3)
197 60589 Test3 SubTestB
198 60589 Test3 SubTestC
199 60589 Test4 SubTestA(Same as Test4)
193 60589 Test4 SubTestB
194 60589 Test4 SubTestC

195 87254 Test1 SubTestA
etc., etc.

As you can see a lot of duplication. I have a Form which I use to pull up
all the data for Specific SampleID, where the "Test" data is displayed in the
Main section of the form, and the "SubTest" data is displayed in a continuous
subform. It works fine, but due to the table design, If a SampleID consists
of 4 Main Tests which each have 3 subtests, I get 12 records on the main
form, with the each Main test being repeated 3 times, and the correct
subtests show up in the subform, but they show up for each time the main test
is repeated.

I would like for each Main test to only be displayed once in the main form,
and depending on what main test is selected, the appropriate subtest(s) data
appear in the subform. Is there a way I can make the form only display the
data once, or do I need to somehow divide up or rearrange the tables, if so
how can I do that?

Thank you for such a quick response. Yes, my next question once I figured
out what I needed to do was going to be- how can I automate it? Anyway, late
on Friday and I' don't feel like fooling with it any more now. Will work on
it Monday.

Thanks for the help

"Sharkbyte" wrote:

Your main db sounds like something I worked with in the past. A Non-First
Normal Form structure. They can be very ugly. Ick!

Anyway, first I would suggest automating your import function. It will be
much easier to push a button and import the file. Then you can use queries
to break your data up. Perhaps something like this:

tblSample
RecordNo (PK)
SampleID
SampleName

tblTests
TestID
TestName (PK)

tblSubTests
SubTestID
SubTestName (PK)

tblSampleTests
RecordNo (PK) (FK)
TestID (PK) (FK)
SubTestID (PK) (FK)

Now you can display only the Sample information, and populate the
subform(s), with Test information, after they select a Sample to view.

HTH

Sharkbyte



"rg32" wrote:

I have designed a small database customized for one particular department of
the company. The company uses a much larger database, which I don't know
much about except that it's complicated to use and in my opinion doesn't seem
to be very well designed. In order to assist us somewhat, the IT dept. does
daily exports of only the data we need for our dept., and sends it to us in
the form of an excel spreadsheet. I then convert the spreadsheet to a table
in Access in order to link it with other tables, forms, reports etc. that we
utilize. This all works fine, but due to the poor design of the database
from which we get the data, there is alot of duplications and tables are not
really organized the way I would like. I can't do anything about the way we
get the data, What I want to know is if there is anyway with (code, queries,
something) that I can change things around either in excel before the
conversion or in access afterwards. Following is an more detailed
explanation of what I would like:

The table design Now: RecordNo is PK(autonumber), The SampleID has
duplicates due to the fact that each sample can have several different tests
performed on it, From there, each Test usually contains several SubTests.
Yet all this data is in one single Table. Nothing I can do about that
though, just want to fix it after I get it.

RecordNo SampleID TestName SubTestName
190 60589 Test1 SubTestA(Same as Test1)
191 60589 Test1 SubTestB
192 60589 Test1 SubTestC
193 60589 Test2 SubTestA(Same as Test2)
194 60589 Test2 SubTestB
195 60589 Test2 SubTestC
196 60589 Test3 SubTestA(Same as Test3)
197 60589 Test3 SubTestB
198 60589 Test3 SubTestC
199 60589 Test4 SubTestA(Same as Test4)
193 60589 Test4 SubTestB
194 60589 Test4 SubTestC

195 87254 Test1 SubTestA
etc., etc.

As you can see a lot of duplication. I have a Form which I use to pull up
all the data for Specific SampleID, where the "Test" data is displayed in the
Main section of the form, and the "SubTest" data is displayed in a continuous
subform. It works fine, but due to the table design, If a SampleID consists
of 4 Main Tests which each have 3 subtests, I get 12 records on the main
form, with the each Main test being repeated 3 times, and the correct
subtests show up in the subform, but they show up for each time the main test
is repeated.

I would like for each Main test to only be displayed once in the main form,
and depending on what main test is selected, the appropriate subtest(s) data
appear in the subform. Is there a way I can make the form only display the
data once, or do I need to somehow divide up or rearrange the tables, if so
how can I do that?

To establish an import (Access 2003, though the others should be close to the
same) try the following steps:

This works best if your spreadsheet is always in the same structure.

Create an import of the spreadsheet, into its own table. You will use this for every import, so you will need to run a DELETE command before each import. Just add it to your code, before you trigger the import.

For the code, behind the button triggering the import, you will want something similar to this:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"tbldataimport1b", strInputFileName

Where tbldataimport1b is the destination table and strInputFileName comes
from a function that allows me to select the file for import.

I didn't write the file open function. I found it on the Internet. I have
the link somewhere in my email, at work, so if you can't find something (and
no one offers a link) email me and I will dig it up.

Good Luck

Sharkbyte


"rg32" wrote:

Thank you for such a quick response. Yes, my next question once I figured
out what I needed to do was going to be- how can I automate it? Anyway, late
on Friday and I' don't feel like fooling with it any more now. Will work on
it Monday.

Thanks for the help

"Sharkbyte" wrote:

Your main db sounds like something I worked with in the past. A Non-First
Normal Form structure. They can be very ugly. Ick!

Anyway, first I would suggest automating your import function. It will be
much easier to push a button and import the file. Then you can use queries
to break your data up. Perhaps something like this:

tblSample
RecordNo (PK)
SampleID
SampleName

tblTests
TestID
TestName (PK)

tblSubTests
SubTestID
SubTestName (PK)

tblSampleTests
RecordNo (PK) (FK)
TestID (PK) (FK)
SubTestID (PK) (FK)

Now you can display only the Sample information, and populate the
subform(s), with Test information, after they select a Sample to view.

HTH

Sharkbyte



"rg32" wrote:

I have designed a small database customized for one particular department of
the company. The company uses a much larger database, which I don't know
much about except that it's complicated to use and in my opinion doesn't seem
to be very well designed. In order to assist us somewhat, the IT dept. does
daily exports of only the data we need for our dept., and sends it to us in
the form of an excel spreadsheet. I then convert the spreadsheet to a table
in Access in order to link it with other tables, forms, reports etc. that we
utilize. This all works fine, but due to the poor design of the database
from which we get the data, there is alot of duplications and tables are not
really organized the way I would like. I can't do anything about the way we
get the data, What I want to know is if there is anyway with (code, queries,
something) that I can change things around either in excel before the
conversion or in access afterwards. Following is an more detailed
explanation of what I would like:

The table design Now: RecordNo is PK(autonumber), The SampleID has
duplicates due to the fact that each sample can have several different tests
performed on it, From there, each Test usually contains several SubTests.
Yet all this data is in one single Table. Nothing I can do about that
though, just want to fix it after I get it.

RecordNo SampleID TestName SubTestName
190 60589 Test1 SubTestA(Same as Test1)
191 60589 Test1 SubTestB
192 60589 Test1 SubTestC
193 60589 Test2 SubTestA(Same as Test2)
194 60589 Test2 SubTestB
195 60589 Test2 SubTestC
196 60589 Test3 SubTestA(Same as Test3)
197 60589 Test3 SubTestB
198 60589 Test3 SubTestC
199 60589 Test4 SubTestA(Same as Test4)
193 60589 Test4 SubTestB
194 60589 Test4 SubTestC

195 87254 Test1 SubTestA
etc., etc.

As you can see a lot of duplication. I have a Form which I use to pull up
all the data for Specific SampleID, where the "Test" data is displayed in the
Main section of the form, and the "SubTest" data is displayed in a continuous
subform. It works fine, but due to the table design, If a SampleID consists
of 4 Main Tests which each have 3 subtests, I get 12 records on the main
form, with the each Main test being repeated 3 times, and the correct
subtests show up in the subform, but they show up for each time the main test
is repeated.

I would like for each Main test to only be displayed once in the main form,
and depending on what main test is selected, the appropriate subtest(s) data
appear in the subform. Is there a way I can make the form only display the
data once, or do I need to somehow divide up or rearrange the tables, if so
how can I do that?

I am preparing a database for a survey, which has multiple choice questions,
hence I am using lookup wizard as datatype,
but the only problem is,is does not allow multiple selection from the drop
down list,
how can I select multiple options?

No, there's no way, and, in fact, I'd suggest strongly to drop your use of
lookup fields. (see "The Evils of Lookup Fields in Tables" at
http://www.mvps.org/access/lookupfields.htm)

You should never update tables directly. You should always be using a form.

For a great example of how to do a survey, see Duane Hookom's "At Your
Survey"
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 097'
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Preeti" wrote in message
...
I am preparing a database for a survey, which has multiple choice
questions,
hence I am using lookup wizard as datatype,
but the only problem is,is does not allow multiple selection from the drop
down list,
how can I select multiple options?