New to this

[Thorn]

I work for a dental clinic and we're trying to use Access to create a query which will bring up claims data for a particular date range. Unfortunately our dental program does not store all the necessary data in a single table and so some of the fields we need are on seperate tables.


DAISY_ChargeView
acct_trans_receive_dt
procedure_id
descrp
tooth
surface


acct_trans_amt
pap_provider

DAISY_PatientAccountView
pat_last_nm
pat_first_nm
pat_ssn
provider_nm
pat_birth_dt

We need to filter by the service date supplied in acct_trans_receive_dt and have the patient's account information correctly associate with the date that service was provider for the patient. I tried using the Query Wizard but I haven't had much luck in getting the data to return right (to say nothing of filtering by date). When pulling data that doesn't have a relationship established, it will return the information several different times. For example, Acct is consistent in both tables, but procedure_id and descrp are only present in one. When running the query it would produce something like this

Acct - Procedure_ID - Descrp
2 D9410 House Call
2 D0150 Comprehensive Exam
2 D9410 Comprehensive Exam
2 D0150 House Call

I'm really new to Access so I'm struggling with this. If anyone could help me write this query I'd appreciate it.

[ConneXionLost]

The important thing about relational databases is...relationships! Without a relationship between these tables, you won't be able to get what you're asking for. You should have a Foreign Key in your [DAISY_ChargeView] table that will allow you to connect to the patients; either with a PatientID or an AccountID.

Cheers,