Make a new table to normalize data

[gillianleec]

I am pulling data into reports from a program where the tables are not normalized. I would like to pull data into a new table in order to simplify reporting.

The original table has fields like: ID#, Order#, Orderdate, Test1, Test2, Test3, ...Test19, AOtest20, AOtest21...AOtest59. The fields Test1-AOtest59 are not always all populated. They can use as many fields as they need to specify individual medical tests for an order

I would like to pull the data based on Orderdate and make a new table with ID#, Order#, Testname

Therefore, there would be a new record for each individual test with the ID# and Order # from the original record.

Let me know if you need more info. Thanks

 

[scriverb]

Here is some VBA code that can be used to make your new table:

Create a new table in the format that you described with the three fields. ID#, Order#, testname

Create a query that allows for the prompting of a date to be selective for the records in the old table. This can be done by adding a criteria statement that says #[Enter Order Date: ]# You then enter the date with the following format: mm/dd/yyyy

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rss As DAO.Recordset
Dim vTestName As String
Set db = Database
Set rs = db.OpenRecordset("qryOldTable", dbOpenDynaset)
Set rss = db.OpenRecordset("tblNewTable", dbOpenDynaset)
rs.MoveFirst
Do
For I = 1 To 59 Step 1
If I < 20 Then
vTestName = &quot;Test&quot; & I
Else
vTestName = &quot;AOtest&quot; & I
End If
If Not IsNull(rss(vTestName)) Then
rss.AddNew
rss(&quot;ID#&quot = rs(&quot;ID#&quot
rss(&quot;Order#&quot = rs(&quot;Order#&quot
rss(&quot;testname&quot = vTestName
rss.Update
Else
I = 59
End If
Next I
rs.MoveNext
Loop Until rs.EOF

This should get you started to the normalization of the old table into a new table. The only thing that I question is you haven't included the OrderDate as a field in the new table. Do you need it to be added?

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gillianleec]

Thanks!! I will try this over the weekend and post any questions. I have a few more fields to add like Orderdate but I can do that with what you have given me.

Gillian

 

[scriverb]

I made the assumption that the data would start at the Test1 field and continue through the numbers until there was a blank. At that point I skipped the rest. If this is not true then just remove the lines:

Else
I = 59

and it will check all of the old fields.

Good luck.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gillianleec]

Just learning how to use VBA so I have a few questions. I have created the new table and am in the process of creating the query. Do I first create a module with the above code as a function and then call that from a query or do I include the code in a query and if so, how?

Gillian

re above-your assumptions were correct

 

[scriverb]

Create a query that will perform the OrderDate selection as you indicated in your question:
EXAMPLE
Select *
FROM tblOldTable as a
WHERE (a.OrderDate = [Enter Order Date(MM/DD/YYYY)]:

Then save this query with the name qryOldTable. Then the code I provided would be copied and pasted into the Event Procedure behind a button on a new form. This form would just be used to run your Normalization process.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gillianleec]

I am getting an error message Run time error '424' Object required and this line is highlighted Set db=database

Any ideas?

 

[scriverb]

Sorry use the following instead:

Set db = CurrentDb

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gillianleec]

This is great, I got it to work but I had to change the date in my query to a hard-coded date and not a prompt. Each time I ran the code with the prompt, I got an error saying it expected more parameters. Do you know how I could get this to work?

I also changed rss(&quot;Testname&quot=vtestname to
rss(&quot;Testname&quot=rs(vtestname) because before it was just putting the text &quot;test1&quot;, &quot;test2&quot;, ...in that field and not looking for the data in that field.

Thanks for this!!!