I have three tables, one containing patient information, one containing their appointment details and another containing the pricing. I have made a calculated field in a query using the patient type and the type of appointment ('other first appointment'), i have then linked this query to the pricing table to add the pricing in from the calculated field, the pricing table contains two fields one, with the names ('other first appointment' etc) and another with the required price. When i link them however it creates multiple entries even though there is only one patient entry, and four different prices, it adds them for all possible prices (the query contains four records for the same patient with the different prices). how can i get it to link and only add the correct price from the calculated field by looking up the pricing table??
simple query problem
- Thread starter moagli
- Start date
- Local time
- Today, 16:36
- Joined
- Sep 12, 2006
- Messages
- 16,096
i take ot you couldn't join the pricing table to any of the other tables - this gives you a cartesian join, where as you found out, you get all possible values. if you can't join the pricing table, then instead of including it in the query you need a column in the query defined as a function, that will retrieve the appropriate value in VB.
- Local time
- Today, 10:36
- Joined
- Feb 28, 2001
- Messages
- 31,007
Gemma is correct. You have described a Cartesian JOIN.
To prevent this, consider NESTING your queries. First, join the patient to the appointment. Then, IN A SECOND QUERY, join the first query to your pricing table based on data in the appointment field.
If you cannot do this, then you violated one of the "old programmer's rules." Access cannot tell you anything you didn't tell it first. The appointment field must indicate what type of appointment the particular patient had on the particular date. (Which I infer could be determined by matching a code stored in your pricing table.)
The key to understanding how this works is to remember that a query can do almost anything a table can do - and more, sometimes. In particular, a JOIN does not join tables. It joins RECORDSETS - which can be based on either tables OR queries. So use this to your advantage.
To prevent this, consider NESTING your queries. First, join the patient to the appointment. Then, IN A SECOND QUERY, join the first query to your pricing table based on data in the appointment field.
If you cannot do this, then you violated one of the "old programmer's rules." Access cannot tell you anything you didn't tell it first. The appointment field must indicate what type of appointment the particular patient had on the particular date. (Which I infer could be determined by matching a code stored in your pricing table.)
The key to understanding how this works is to remember that a query can do almost anything a table can do - and more, sometimes. In particular, a JOIN does not join tables. It joins RECORDSETS - which can be based on either tables OR queries. So use this to your advantage.
