id numbers (AccessXP)

Topic

New Reply

In a database that I have created to track reshipped drugs, I have all my tables linked by the RNA_ID number which is the unique ID number for patients. For employees, I also have a unique ID. When I pull up a patient, if he has had more than one drug reship, every employee involved in the first incident, shows up with the second incident also. Do I need to have an ID number for each reship?

Reply | Quote

Replies

Hi Laura…

In short… Yes you should…
Not necessarily a number, but a date or something to say this is a new record for the same patient with different employee(s) involved…

I don’t know how you have the db structured and how or what you are adding as details to each reship record, so I can’t really go into further advice… but post back with more details and/or sample data if you need more help…

Reply | Quote

I have four tables, T_Patient with fields RNA_ID, Patient Last Name, Patient First Name, Error Notification Date, Dispensing Error, Billing Error, etc with all the usual demographics; T_Counseled with fields RNA_ID, Counsel_ID, date, employee, dept; T_Courier with fields RNA_ID, Courier_ID, Courier, Error by Courier; and T_Out_of_Stock with fields RNA_ID, Drug, dose, sent, owed. On the main form, I have a subform for Courier, Out of Stock, and Counseled. At first, I had the RNA_ID as “no duplicates allowed” but then when we started running into repeated errors on patients, I allowed duplicates but if you pull up that patients form (if there were 3 errors, there will be 3 forms), all three errors show up on all three forms.

Reply | Quote

Laura…

It sounds like you need some good old fashioned normalization there… I don’t know how far along in the database design you are, but it may be better for you to re-think some of the tables and relationships involved before you end up trying to fix this kind of thing often…

Is it possible for you to attach a sample of your database… Zipped with just the tables, queries and/or form in question with a small amount of dummy data in it? …I’m not sure whether what you want can be done with the way you have it set up… but it’d be easier to tell if I could see something…

Reply | Quote

I may send a copy of the database later. I don’t have time to dummy the data. But now that I look at it, I have reports that show the data individually but my boss wants one report for all the errors together. If I keep them seperate, i.e. one report for each type of error, there is no duplication. You only see the duplication if you are looking at the forms or running a report for all errors. Thanks for the advice though.

Reply | Quote

I may send a copy of the database later. I don’t have time to dummy the data. But now that I look at it, I have reports that show the data individually but my boss wants one report for all the errors together. If I keep them seperate, i.e. one report for each type of error, there is no duplication. You only see the duplication if you are looking at the forms or running a report for all errors. Thanks for the advice though.

Reply | Quote

Laura…

It sounds like you need some good old fashioned normalization there… I don’t know how far along in the database design you are, but it may be better for you to re-think some of the tables and relationships involved before you end up trying to fix this kind of thing often…

Is it possible for you to attach a sample of your database… Zipped with just the tables, queries and/or form in question with a small amount of dummy data in it? …I’m not sure whether what you want can be done with the way you have it set up… but it’d be easier to tell if I could see something…

Reply | Quote

I have four tables, T_Patient with fields RNA_ID, Patient Last Name, Patient First Name, Error Notification Date, Dispensing Error, Billing Error, etc with all the usual demographics; T_Counseled with fields RNA_ID, Counsel_ID, date, employee, dept; T_Courier with fields RNA_ID, Courier_ID, Courier, Error by Courier; and T_Out_of_Stock with fields RNA_ID, Drug, dose, sent, owed. On the main form, I have a subform for Courier, Out of Stock, and Counseled. At first, I had the RNA_ID as “no duplicates allowed” but then when we started running into repeated errors on patients, I allowed duplicates but if you pull up that patients form (if there were 3 errors, there will be 3 forms), all three errors show up on all three forms.

Reply | Quote

Hi Laura…

In short… Yes you should…
Not necessarily a number, but a date or something to say this is a new record for the same patient with different employee(s) involved…

I don’t know how you have the db structured and how or what you are adding as details to each reship record, so I can’t really go into further advice… but post back with more details and/or sample data if you need more help…

Reply | Quote

How do you know which employee is associated with which incident, unless you have some sort of incident ID #?

Reply | Quote

Well, I had planned on using the Error Notification Date as the identifier. Is it possible to use the date field?

Reply | Quote

You wrote: “I had planned on using the Error Notification Date as the identifier. Is it possible to use the date field?”

Yes, you can use it; except, are you sure that no patient will return 2 drugs in the same day?

Reply | Quote

No, it’s not to track returns, it is to track the number of times we made errors and had to reship so the same patient would not have 2 reships on the same day. But I’m confused about how to pull it all together.

Reply | Quote

Without knowing more about what you are trying to do and what your data structure is, I can’t really offer any useful advice.

Reply | Quote

Without knowing more about what you are trying to do and what your data structure is, I can’t really offer any useful advice.

Reply | Quote

No, it’s not to track returns, it is to track the number of times we made errors and had to reship so the same patient would not have 2 reships on the same day. But I’m confused about how to pull it all together.

Reply | Quote

You wrote: “I had planned on using the Error Notification Date as the identifier. Is it possible to use the date field?”

Yes, you can use it; except, are you sure that no patient will return 2 drugs in the same day?

Reply | Quote

Well, I had planned on using the Error Notification Date as the identifier. Is it possible to use the date field?

Reply | Quote

How do you know which employee is associated with which incident, unless you have some sort of incident ID #?

Reply | Quote