carry previous data within a criteria

[Aximboy]

I have a query question.
I have a table with Patient Name, Date of Visit, and Flu, MMR & Polio vaccinations.
The flu vaccine is good for 1 year only.
MMR vaccine is good for 2 years.
Polio is good for 3 years.
How do I create a query to show example#2?


example#1

Here's what I have....

ID	Patient		Visit		Flu	MMR	Polio
1	John Lennon	01/01/2000	Yes		Yes
2	John Lennon	01/01/2001	Yes	Yes
3	John Lennon	01/01/2002		
4	John Lennon	01/01/2003	Yes
5	John Lennon	01/01/2004	Yes		
6	John Lennon	01/01/2005		Yes	Yes
7	John Lennon	01/01/2006	Yes

example#2

Here's what I want to happen....

ID	Patient		Visit		Flu	MMR	Polio
1	John Lennon	01/01/2000	Yes		Yes
2	John Lennon	01/01/2001	Yes	Yes	Yes
3	John Lennon	01/01/2002		Yes	Yes
4	John Lennon	01/01/2003	Yes
5	John Lennon	01/01/2004	Yes		
6	John Lennon	01/01/2005		Yes	Yes
7	John Lennon	01/01/2006	Yes	Yes	Yes

Thanks in advance!

 

[dhookom]

First I would suggest your table structure is not normalized and will probably lead to additional issues/questions. Ideally your fields would be:

ID
Patient
Visit
Vaccination

Another table of vaccinations might be:

VaccID
VaccTitle
VaccYears


If there were two (or more) vaccinations on a visit this should create multiple records. I wouldn't work much further without changing the table structure.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Aximboy]

Thank you for replying.

Yes it is normalized.
The 1st table is...
ID
Patient

...and the 2nd table is...
PatID
Visit
Flu
MMR
Polio

 

[dhookom]

If you have Flu, MMR, Polio as field names, it isn't normalized. If you add another vaccination to your solution, you should not have to add another field. Data belongs in your table, not in your structure/names.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016