Hi pplz!
So ill explain my whole situation in the hope i can keep this thread going for some help along the way!!
Im designing my first DB using access for the pharmacy I work at.
Im very fluent at Excel but still learning Access, (lots every day).
The DB is going to be used to:
Store customer details (First, Last names, Insurance company and Workers comp number)
Store their prescription details
Enter an invoice that refers to the prescriptions (which shows that the insurance company has paid and what they paid)
My current journey:
Created Tables for:
- Customer
- Insurance Companies
- Drug List
- Script information
- Single invoice info
- Batch script data
- Batch Invoice data
Pretty much i have related all fields and used lookup values which works really well.
Customers have their scripts allocated to them, and the invoices are added and i can see which scripts have an invoice added and which ones are still waiting payment.
My issue:!
When an invoice comes in, I have the fields "Script ID invoiced", "amount invoiced ($)"
however right now i say "script 1 has been paid" using the script ID from the Script table (which then gives info on the actual script) HOWEVER i want someone to be able to use this and instead of looking up the script ID I want them to be able to put in a field "Date of script", "drug invoiced", "Customer invoiced" then in the 4th field it will return the Script ID by looking up those 3 features in the script tables and bringing back the specific script that corresponds to them.
eg.
instead of
Script ID Amount
1 $35.00
I want
Date of script Customer name Drug Amount (ScriptID Hidden)
12/12/12 Bob Smith Viagra 100mg $35.00 1
Any help/direction or even the name of the kind of calc i should learn about would be amazing... im getting really annoyed as i cant find the answer!!!!
GLenn