Excel and Access question--transform flat file?

[mishmish3000]

Greetings! I have been given a wonderful holiday present... a horrid flat file with patient numbers, names, addresses, and a record for each and every shot the child has received since birth. Of course, the powers that be want something different. They want one record per kid, showing a summary of one type of shots (DTaP--a shot type that includes DHB, DHH, DHF, DHV, DT, DTA, DTH, DTI, DTP, TD, TDP, and DPP), and then a summary of all the other shots the kid has received. I made an Excel version of what I HAVE and what I WANT and would greatly appreciate any help at all on this. It's coming over as a text file, which is imported into Access. I put notes on the WANT sheet, to show you what some of the fields mean, and what sort of summation I need. I've gone round and round with this one, and it's making me pull my hair out. Not good for the Christmas cheer, I can assure you.
Thanks!
MishMish

[HansV]

The "HAVE" format is not ideal because it contains a lot of repetition, but it is MUCH better than the "WANT" format which is virtually impossible to analyze scientifically.

The best format would be two tables in Access: a Patients table in which each patient (kid) is listed once, with patient-specific info such as date of birth, gender and address, and a Vaccination table with a separate record for each shot for each patient; this table contains only the Patient_No field to identify the kid, and fields specific to the shot: date, antigen etc.

To get the "WANT" format, you wouldn't create a table in Access, but a query that concatenates the data on the fly. This query can be used as record source for a report.

[mishmish3000]

OK, great idea! I'll try two tables then.

[HansV]

Post back if you need help with converting the single table to two tables (that would be a subject for a thread in Access).

[mishmish3000]

Yes, I'd love some help and how do I switch the thread to Access instead of Excel?

[HansV]

I could move this thread to the Access forum, or you could post a new question in the Access forum, whichever you prefer.

[mishmish3000]

How about if you move it for me? Do you/will you need to see the actual main table I'm dealing with? The field names are:
ID, REGION, COUNTY, COSITE, PATIENT_NO, LAST_NAME, FIRST_NAME, MI, DOB, RACE, SEX, ADDRESS, CITY, STATE, ZIP, PHONE, ANTIGEN_DTAP, SHOTDATE, ANTIGEN_OTHER, COUNT_ANTIGEN, COUNT_OTHER, DOBPLUS120, AGE_MO

ID is an autonumber primary key; each patient has multiple records, since there's one for every shot (antigen). AGE_MO I can calculate already, so that's not a prob.

I envision two tables: one with patient data only, and one with patient shot data. Patient shot data would be PATIENT_NO, ANTIGEN_DTAP, SHOTDATE, ANTIGEN_OTHER, COUNT_ANTIGEN, COUNT_OTHER
All other fields would live nicely together in the patient data only table, don't you think?

MishMish

[HansV]

OK, I've moved the thread from Excel to Access. Let me look into it.

[mishmish3000]

I believe I've made the tables...
Now, how should I create two queries that show:

patient_no, dob, sex, address, city, state, county and region, with following criteria to be filtered: age_mo <20, <1 DTaP shot, where shotdate >=DOBPlus120 (where shotdate is for the first DTaP shot)?

That's the first thing they want to look at. The second thing is:

patient_no, dob, sex, address, city, state, county and region, with following criteria to be filtered: age_mo >18 and <25, and who have <4 DTaP shots?

A bit of background:
The first report is what we're calling the late start report--kids who haven't received the normal number of DTaP shots before 120 days after birth. The second report is made up of the overdue kids--those who haven't received their required 4 DTaP shots by the time they're between 19 and 24 months of age. There's a high probability that late start kids, who haven't received their DTaP vaccinations in a timely manner (i.e., at least more than 1 before 120 days old) are less likely to get all their childhood shots. The overdue kids are ones where they have gotten part of the series of shots that are recommended, but for one reason or another, the parent hasn't brought them back to get all their shots. We use DTaP as a criteria, since all kids need to get that shot (to avoid bad things like pertussis).

I've coded the ANTIGEN_DTAP field to show DTAP if it meets one of the DTaP shots (DHB, DHH, DHI, DHV, DT, DTA, DTH, DTI, DTP, TD, TDP, DPP). I've put a 1 in the COUNT_ANTIGEN field if ANTIGEN_DTAP is not blank. Then we can sum the ANTIGEN_DTAP field in the query to get a count (i.e., the kid's had 2 DTAP shots, the kid's had 0, the kid's had 4).

Does this make sense? It's been a _very_long day.
MishMish

[HansV]

The conditions "<1 DTaP shot, where shotdate >=DOBPlus120 (where shotdate is for the first DTaP shot)?" seem contradictory - if the kid has less than 1 DTaP shot, it has none, so there is no ShotDate for DTaP.

[mishmish3000]

Yes, correct...

hmm. How do I find the kids who have had 1 or fewer DTaP shots before they are 120 days old or older? We want to catch those who have had 1 or less before they turn 120 days (4 months) old. If they've had 2, it's probably ok, but 1 or less indicates a problem.

MishMish

[HansV]

You need several queries:

One to return kids that have had one DTaP at DOBPLUS120, and to return kids that have had no DTap at DOBPLUS120, and a third query to combine them.

Alternatively you could use one query using DCount, but that could be slow if you have large numbers of records.

See the attached sample database.

Vaccination.zip

[mishmish3000]

thanks, I'll take a look!