query is duplicating information

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

Guest

I created a database that keeps track of clients that come to our substance
abuse program. some client come for a month then leave and come back. So i
have a table that has client information, it contains clientid, program name,
entry date, termination date. so 1 client could have multiple entries in the
program table.

each time a client comes to a meeting we enter that information so that we
can bill for this client. the problem that i am having is that if a client
has 2 entries for a program (if the client dropped out and then came back to
the program) and i run a billing report on this client, the billing
duplicated because it looks like the client was in two programs. i tried
putting is null in the termination date and that seems to work, but if a
client is terminated and i try to run old billing for him, it won't show up
because i have is null in termination date. hope this makes sense. any
suggestions is greatly appreciated.
 
If you were working on an Orders program instead, would you add a new record
with the person's name, address, ... for every order?

I suspect you've treated Access as a spreadsheet (which will only cause both
you and Access considerable headache!).

You could have one table to list people, and another table to list visits,
each with a start and end date. You'd include the PersonID from the person
table to know who visited, and you could run your query against that Visits
table to work on billing.

Or have I misunderstood your description?

Good luck

Jeff Boyce
<Access MVP>
 
I do have 3 tables the 1st table is the client info table, it contains name
social security (social is the primary key)and clientid (which is
autonumber). the 2nd table is the program table it contains program name,
entry date and termination date and client id. the client could have more
than 1 entry in the program table because they might drop out of a program
and come back months later and we want to keep track of each time they enter
our program. 3rd table is the meetings table, it has client id, meeting date
and time and notes.

the 3rd table information is where the billing information comes. the
problem is that if a client was in our program and then he droped out and
decides to come in the program table has 2 entries for this client. in the
query for billing, i have to put is null in the termination date because if i
don't, i will get a duplicate meeting because it shows the client was in 2
programs, so it duplicates the information in the meetings table. but when is
null is in the termination date for a client that droped out and never came
back, when you try to run the query even if he was in a meeting, because he
has a termination date, he won't show up. hope this makes sense.
 
Nydia

If your "BillingTreatmentPeriod" table includes a date field for
termination, rather than your program table, you could determine which
billable periods were already closed out.

From your description, it sounds like you put the termination date in the
program table. That would imply a single course of treatment only.

Or am I still missing something?

Jeff Boyce
<Access MVP>
 
the reason i have the dates in the program table is because we want to know
each time a client comes and goes from our program. and the client can have
multiple meeting. if we put an entry and termination date for each meeting
note it would be redundent.
In the example below, this client has been in our program 2 and he is
currently active. so if i run a billing report i am fine because a
termination date is null. but for example if he was terminated in both and i
forgot to send billing sheets for him because the termination date is null in
the query, he won't show up, but if i take away the is null in the query,
because he has 2 entries in the program table it will duplicate each meeting
note in the meeting table. so if attended a stop smoking group on 05/04/05
that will show up twice instead of once.

ex
Mickey Mouse SS# 555-12-12-12

program entry termination
Out patient 05/05/05 05/20/05
out patient 07/05/05
 
Nydia

I guess I'm just having trouble visualizing your data structure.

It sounds like you have one entry in the Program table for each time the
client starts a program, but you don't have any way to tell which "meeting"
belongs to which Program.

Even though you include a clientID in your Meeting table, you don't know
which of the client's (possibly more than one) Program rows the meeting
relates to.

If you don't include a ProgramID field in your Meeting table, the only other
way I could imagine you could do this would be to find all the Meetings for
the Client where the MeetingDate is between the Program!StartDate and
Program!TerminationDate (and you'd still need to figure out how to handle
the nulls in TerminationDate).

I suspect it would be easier to just add the ProgramID (autonumber) field in
the Program table and use a LongInt (numeric) field in the Meeting table to
show which Program a client's Meeting was related to.

Good luck!

Jeff Boyce
<Access MVP>
 
if they went to a meeting, it doesn't really matter what program they are in
as long as we can bill for it. even if i put the program id in the meeting
table i don't think it would work. the people using the database are not
access savey, so i put in alot of command buttons and in the queries i put in
between [startdate] and [enddate]. they wouldn't know how to go into a query
and put in program id's.

the way it is set-up is when they are at a specific clients record, there
is a command button that they click on, then they enter the start and end
dates for the billing on this client. then the billing sheet report is
generated based on the dates they enter. problem is that if the client is
terminated, when they click on the command button and enter dates, no billing
information shows, because his termination date is not null. and if i remove
the is null from the termination date in the query clients that have been in
more than one program (or the same program twice) it duplicates their meeting
dates. if the client was in 5 programs, it will make the same meeting note 5
times.

thanks so much for your help. i greatly appreciate it.
 
Back
Top