I work in a hospital and I have a database that is used by the departments to request additions and changes to their charges for their respective areas.
We are converting to a new system and with that new system need to add the same charge to multiple departments all at the same time. I am trying to figure out a way for the department to enter the charge request/change 1 time but have it affect all the various departments.
For example. If they add a request for Physical Therapy, I would want the 1 request to filter into 7 different departemtns or cost center numbers.
I was able to do it using a query but that only works in the query it wouldn't actuall save the duplicate records to the main table.
Is there a way to have the entry of a single record duplicate into the same table automatically?
I hope I explained this well enough to get an answer.
I'm guessing all the information will be the same except the table names are unique? Or, are the added records added to the same table but 1 field is different? Using an Append query is the easiest way tying all the info back to the data entry form and having the append query run on the forms After Insert event, which even can be done just using VBA. But, do you need the information to stay coordinated after adding the records? Why would you need to duplicate the data?
The original record along witht he duplicates would be in the same table, which is called "Charges"
The reason for the duplication is that each duplicate would corrliate to a different Department Number in our system. So the origianl would be entered as department 1, the others would be say department numbers 5, 7, 10, etc.
The department numbers are tied to a Alpha Code for the Type of Service such as Xray, MRI, CT Scan etc.
All information would be the same with a couple of exceptions, 2 of our services have multiple departments but there is 1 department that crosses services so those Charge numbers would be different the the others.
For example. Our CT Departments, 3 of them would get charge number 00108 for the next request. 1 department would get charge number 50108 since the Department Number is shared with Xray.
The reason I ask is it is bad design to duplicate data. You should consider having a relationship between the main table with the duplicated data and another table that would hold the department specific fields. You then use queries to make your reports/non-data entry forms look correct. This also keeps the Append queries small as you will only have to add Dept Number, Charge Number and foreign key ID.