Cross-Tab CSV Records

B

Brian Bradley

My data comes in comma-separated values:



Billy,Tetanus,Polio_1,,,January,February,,,Injection,Oral,,,

David,Rubella,Polio_1,Polio_2,,February March,April,,Oral,Oral,Oral,,

Betty,Polio_1,Polio_2,Rubella,Tetanus,February,March,April,April,Oral,Injection,Oral,Oral



Note the multiple consecutive delimiting commas in some of the records, and how the vaccinations are in groups, then come the dates in a group, then the method of administration in a group. (Is it just me, or is that "sideways?")



Importing the CSV(s) into Access yields a table whose datasheet looks like this:



Billy
Tetanus
Polio_1


January
Februay


Injection
Oral



David
Rubella
Polio_1
Polio_2

February
March
April

Oral
Oral
Oral


Betty
Polio_1
Polio_2
Rubella
Tetanus
February
March
April
April
Oral
Injection
Oral
Oral




I don't how to do anything with a table like that, but I want to create a report that looks like this:



Billy Tetanus January Injection

Polio February Oral

-------------------------------(page break)------------------------------

David Rubella February Oral

Polio_1 March Oral

Polio_2 April Oral

-------------------------------(page break)------------------------------

Betty Polio_1 February Oral

Polio_2 March Injection

Rubella April Oral

Tetanus April Oral



How can I get the CSVs into a table (or into a query from the table above) whose datasheet I presume should look like this . . .



Billy
Tetanus
January
Injection

Billy
Polio_1
February
Oral

David
Rubella
February
Oral

David
Polio_1
March
Oral

David
Polio_2
April
Injection

Betty
Polio_1
February
Oral

Betty
Polio_2
March
Injection

Betty
Rubella
April
Oral

Betty
Tetanus
April
Oral




.. . . so that I can create the reports depicted above?



The children's names are actually unique ID numbers, thank goodness. The charity insists that the CSV files cannot be provided in any other format. (I don't know how the CSV files are generated.)



I am losing my mind and ruining my eyes over this. **Thanks** to everyone who contributed to my prior similar post ("Am I Dealing With Cross-Tabbed Data Or Not?"), but I did not understand the suggestions. Could some kind soul(s) please try again? Thanks.
 
P

Pieter Wijnen

Create a Import Table following the spec of the Csv File
ImportVacc
----------------
ID
Vacc1
Vacc2
Vacc3
Vacc4
VaccMonth1
...
VaccMonth4
VaccMethod1
...
VaccMethod4

Then you can make a table called Vaccination (you may want to have a lookup table for the VaccMethod & Vaccinations instead of storing the texts, but I leave that for you)

Vaccination
-----------------
ID
Vaccination
VaccDate
VaccMethod

The way to transpose the data would then be

Public Function TransposeVacc() As Boolean

Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef
Dim iRs As DAO.Recordset
Dim i As Long

Set Db = Access.CurrentDb()

' you can put the actual import code here

Set QDef = Db.CreateQueryDef(VBA.vbNullString)
Qdef.SQL = "Parameters pID Long, pVaccination Text, pVaccDate Date, pVaccMethod Text;" & VBA.vbCrlf & _
"INSERT INTO Vaccination (ID, Vaccination, VaccDate, VaccMethod)" & VBA.vbCrlf & _
"VALUES (pID, pVaccination, pVaccDate, pVaccMethod)"

Set iRs = Db.OpenRecordset("SELECT * FROM ImportVacc", DAO.dbOpenSnapshot)
While Not iRs.EOF
Qdef.Parameters("pID").Value = iRs.Fields("ID").Value
For i = 1 To 4
Qdef.Parameters("pVaccination").Value = iRs.Fields("Vacc" & i).Value
Qdef.Parameters("pVaccDate").Value = Month2Date(iRs.Fields("VaccMonth" & i).Value)
Qdef.Parameters("pVaccMethod").Value = iRs.Fields("VaccMethod" & i).Value
QDef.Execute DAO.dbSeeChanges
Next
iRs.MoveNext
Wend
iRs.Close : Set iRs = Nothing
Qdef.Close : Set Qdef = Nothing
Db.Execute "DELETE From ImportVacc", DAO.dbSeeChanges
Set Db = Nothing

End Function

Public Function Month2Date(ByVal mnth As String) As Date
Dim i As Long

Select Case mnth
Case "January": i =1
Case "February": i =2
'...
End Select
Month2Date = VBA.DateSerial(VBA.Year(VBA.Now()), i, 1)
End Function

HtH

Pieter

My data comes in comma-separated values:



Billy,Tetanus,Polio_1,,,January,February,,,Injection,Oral,,,

David,Rubella,Polio_1,Polio_2,,February March,April,,Oral,Oral,Oral,,

Betty,Polio_1,Polio_2,Rubella,Tetanus,February,March,April,April,Oral,Injection,Oral,Oral



Note the multiple consecutive delimiting commas in some of the records, and how the vaccinations are in groups, then come the dates in a group, then the method of administration in a group. (Is it just me, or is that "sideways?")



Importing the CSV(s) into Access yields a table whose datasheet looks like this:



Billy
Tetanus
Polio_1


January
Februay


Injection
Oral



David
Rubella
Polio_1
Polio_2

February
March
April

Oral
Oral
Oral


Betty
Polio_1
Polio_2
Rubella
Tetanus
February
March
April
April
Oral
Injection
Oral
Oral




I don't how to do anything with a table like that, but I want to create a report that looks like this:



Billy Tetanus January Injection

Polio February Oral

-------------------------------(page break)------------------------------

David Rubella February Oral

Polio_1 March Oral

Polio_2 April Oral

-------------------------------(page break)------------------------------

Betty Polio_1 February Oral

Polio_2 March Injection

Rubella April Oral

Tetanus April Oral



How can I get the CSVs into a table (or into a query from the table above) whose datasheet I presume should look like this . . .



Billy
Tetanus
January
Injection

Billy
Polio_1
February
Oral

David
Rubella
February
Oral

David
Polio_1
March
Oral

David
Polio_2
April
Injection

Betty
Polio_1
February
Oral

Betty
Polio_2
March
Injection

Betty
Rubella
April
Oral

Betty
Tetanus
April
Oral




. . . so that I can create the reports depicted above?



The children's names are actually unique ID numbers, thank goodness. The charity insists that the CSV files cannot be provided in any other format. (I don't know how the CSV files are generated.)



I am losing my mind and ruining my eyes over this. **Thanks** to everyone who contributed to my prior similar post ("Am I Dealing With Cross-Tabbed Data Or Not?"), but I did not understand the suggestions. Could some kind soul(s) please try again? Thanks.
 
D

Douglas J. Steele

That's what's known as "denormalized", and is not how you want the data to
be arranged in a relational database. What you want to do is import that
data as presented into a temporary table, and then use a query to arrange it
into how it should be presented.

Unfortunately, you haven't shown the names of the fields as imported, so
I'll just assume that they're coming in as F1, F2, F3, ... F13.

The query you want would be something like:

SELECT F1 AS PatientName, F2 AS InnoculationType, F6 AS InnoculationDate,
F10 As InnoculationMethod
FROM TemporaryTable
UNION
SELECT F1 AS PatientName, F3, F7, F11
FROM TemporaryTable
WHERE F3 IS NOT NULL
UNION
SELECT F1 AS PatientName, F4, F8, F12
FROM TemporaryTable
WHERE F4 IS NOT NULL
UNION
SELECT F1 AS PatientName, F5, F9, F13
FROM TemporaryTable
WHERE F5 IS NOT NULL

Use that table to populate your "actual" table, and you should be good to
go.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My data comes in comma-separated values:

Billy,Tetanus,Polio_1,,,January,February,,,Injection,Oral,,,
David,Rubella,Polio_1,Polio_2,,February March,April,,Oral,Oral,Oral,,
Betty,Polio_1,Polio_2,Rubella,Tetanus,February,March,April,April,Oral,Injection,Oral,Oral

Note the multiple consecutive delimiting commas in some of the records, and
how the vaccinations are in groups, then come the dates in a group, then the
method of administration in a group. (Is it just me, or is that "sideways?")

Importing the CSV(s) into Access yields a table whose datasheet looks like
this:

BillyTetanusPolio_1 JanuaryFebruay InjectionOral
DavidRubellaPolio_1Polio_2 FebruaryMarchApril OralOralOral
BettyPolio_1Polio_2RubellaTetanusFebruaryMarchAprilAprilOralInjectionOralOral


I don't how to do anything with a table like that, but I want to create a
report that looks like this:

Billy Tetanus January Injection
Polio February Oral
-------------------------------(page break)------------------------------
David Rubella February Oral
Polio_1 March Oral
Polio_2 April Oral
-------------------------------(page break)------------------------------
Betty Polio_1 February Oral
Polio_2 March Injection
Rubella April Oral
Tetanus April Oral

How can I get the CSVs into a table (or into a query from the table above)
whose datasheet I presume should look like this . . .

BillyTetanusJanuaryInjection
BillyPolio_1FebruaryOral
DavidRubellaFebruaryOral
DavidPolio_1MarchOral
DavidPolio_2AprilInjection
BettyPolio_1FebruaryOral
BettyPolio_2MarchInjection
BettyRubellaAprilOral
BettyTetanusAprilOral


.. . . so that I can create the reports depicted above?

The children's names are actually unique ID numbers, thank goodness. The
charity insists that the CSV files cannot be provided in any other format.
(I don't know how the CSV files are generated.)

I am losing my mind and ruining my eyes over this. **Thanks** to everyone
who contributed to my prior similar post ("Am I Dealing With Cross-Tabbed
Data Or Not?"), but I did not understand the suggestions. Could some kind
soul(s) please try again? Thanks.
 
D

Douglas J. Steele

I was going to mention the difference between the two, but forgot.

Brian: If there's a chance that the same InnoculationType, InnoculationDate
and InnoculationMethod might show up more than once for a given record,
UNION ALL will keep the duplicates, whereas UNION will only keep one record.
Your decision, based on the situation. (UNION ALL should actually be a
little more efficient, since it doesn't have to eliminate duplicates)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Pieter Wijnen"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

visually rendering tabular data as a gantt chart 0
VBA to run all variable combinations in a calculation model and store result 6
=TODAY() and HLOOKUP - help needed 2
summary report 2
Adding information to a Pivot Table 5
Sum by month running balance 3
Count Records and Group by Month 4
Custom Macro for pulling information into one sheet 0

Top