New Database Design

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm fairly new to Access development. I am working for the NHS and want to
populate tables with patient information that will be refreshed every week.
Hence the database will grow quickly, but the numbers will not be that large.
I want to produce a weekly report after the data has been updated that will
only return patients that have come in that week. This I can do, but some of
these patients will have been in on previous occasion and if the patient has
been seen in a previous week and is in the data already but with an older
data, I want to exclude the older record(s). Any help would be much
appreciated. Hope this makes some sense.

Thanks
 
I'm assuming that one of the fields will track the date and time the patient
arrived. Also this field will be a data/time datatype. In that case create a
query and have the following criteria in the date field.

Between [Report Start Date] and [Report End Date] +.99999

Base the report on this query. When run the query or report will ask you for
the dates that you want. One problem might be that the query might be
expecting the mm/dd/yy date format.
 
Hi,

I'm fairly new to Access development. I am working for the NHS and want to
populate tables with patient information that will be refreshed every week.
Hence the database will grow quickly, but the numbers will not be that large.
I want to produce a weekly report after the data has been updated that will
only return patients that have come in that week. This I can do, but some of
these patients will have been in on previous occasion and if the patient has
been seen in a previous week and is in the data already but with an older
data, I want to exclude the older record(s). Any help would be much
appreciated. Hope this makes some sense.

Thanks
Click to expand...

I'd strongly suggest having *two* tables - a table of Patients (which will be
relatively static) related one-to-many to a table of visits. You can then
easily use a Query to select only those visits within a certain range of
dates.

It's not at all clear from your post how your table is structured, or what is
being "refreshed" or updated.

John W. Vinson [MVP]
 
Hi Jerry,
I'm not sure what the ".99999" section of the expression is doing but when I
entered this into with the rest of the expressions into the criteria box it
returns an error (expression typed incorrectly or too complicated to
evaluate....).
I have a field in my table that is a unique patient identifier (Patient
Number) and also one for 'diagnosis'. I want to query for the last week gone
(each week), but if the patient number exist already in the table with the
same diagnosis (but for a different date not included in the range entered) I
want to exclude this from what my query returns.
Thanks.
--
Dave Morris


Jerry Whittle said:
I'm assuming that one of the fields will track the date and time the patient
arrived. Also this field will be a data/time datatype. In that case create a
query and have the following criteria in the date field.

Between [Report Start Date] and [Report End Date] +.99999

Base the report on this query. When run the query or report will ask you for
the dates that you want. One problem might be that the query might be
expecting the mm/dd/yy date format.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

davem said:
Hi,

I'm fairly new to Access development. I am working for the NHS and want to
populate tables with patient information that will be refreshed every week.
Hence the database will grow quickly, but the numbers will not be that large.
I want to produce a weekly report after the data has been updated that will
only return patients that have come in that week. This I can do, but some of
these patients will have been in on previous occasion and if the patient has
been seen in a previous week and is in the data already but with an older
data, I want to exclude the older record(s). Any help would be much
appreciated. Hope this makes some sense.

Thanks
Click to expand...
Click to expand...
 
Hi,

I'm fairly new to Access development. I am working for the NHS and want to
populate tables with patient information that will be refreshed every week.
Hence the database will grow quickly, but the numbers will not be that large.
I want to produce a weekly report after the data has been updated that will
only return patients that have come in that week. This I can do, but some of
these patients will have been in on previous occasion and if the patient has
been seen in a previous week and is in the data already but with an older
data, I want to exclude the older record(s). Any help would be much
appreciated. Hope this makes some sense.

Thanks
Click to expand...
Last week's appointments, but not those with diagnosis from a previous
week.
(not tested)

Assumes table Patients (patient_id, first_name, last_name)
and table PatientAppointments (patient_id, appt_date, diagnosis)

Query1:

SELECT patients.patient_id,
patients.first_name,
patients.last_name,
patientappointments.appt_date,
patientappointments.diagnosis
FROM patients
INNER JOIN patientappointments
ON patients.patient_id = patientappointments.patient_id;


SELECT a.patient_id,
a.first_name,
a.last_name,
a.diagnosis,
a.appt_date
FROM query1 AS a
WHERE a.appt_date BETWEEN (DATE() - DATEPART("w",DATE()) + 1) - 7
AND (DATE() - DATEPART("w",DATE()) + 1) - 1
AND a.patient_id NOT IN (SELECT b.patient_id
FROM query1 AS b
WHERE a.patient_id = b.patient_id
AND a.diagnosis = b.diagnosis
AND b.appt_date < (DATE() - DATEPART
("w",DATE()) + 1) - 7);
 
Michael,
in you reply, you included this as the last line of code - ("w",DATE()) +
1) - 7);
I am just begining to use Access but so far have delt w/ Excel mostly. Can
you help me out and explain what this code systax is. I understand what it
does (last weeks search) but what does the "w" do and why not just subtract 6
days vs. +1 -7?

thanks for the help
dave
 
Michael,
in you reply, you included this as the last line of code - ("w",DATE()) +
1) - 7);
I am just begining to use Access but so far have delt w/ Excel mostly. Can
you help me out and explain what this code systax is. I understand what it
does (last weeks search) but what does the "w" do and why not just subtract 6
days vs. +1 -7?

thanks for the help
dave
Click to expand...

Open an Access database and press Ctrl-G,
then copy the following into the Immediate window.

?(DATE() - DATEPART("w",DATE()) + 1) - 7
5/20/2007
?(DATE() - DATEPART("w",DATE()) + 1) - 1
5/26/2007
?(DATE() - DATEPART("w",DATE()) + 1)
5/27/2007

put your cursor at the end of any of the expression
and press Enter. You dates should be the same as mine.

5/20/2007 is beginning of last week
5/26/2007 is end of last week
5/27/2007 is start of this week
 
Back
Top