Question Need help with a medical dispatch Database for Needy Veterans (1 Viewer)

[jdoathout]

I have two databases I created for my office. One tracks vehicle statistics and maintenance. The other tracks customer usage for medical appointments with the VA.

I have been updating the system because the customers move quite a bit. I split the contact table in 2 one containing the customer info the other containing the address info. This works great. Then I created a query that takes and looks for a Null value in the Move out Date and autofills the info with the customers current information. This is great.

The problem is, all the past medical visits are updated with the new address info instead of the original info. This is not great. I tried to create linked tables that share similar data except one contains all the data and the other with the current info only. The update query doesn't change the data at all.

Basically I need:
Table/Query(s) that keep track of clients and all their addresses

needs to keep a history of those addresses​

needs to keep a history of rides to the hospital from each address for the specific client​

Needs to auto fill forms with current address info only and have a button that states when pushed with a pop up if this wasnt the address the client was picked up from to report this to the VTS supervisor​

Need forms to be add only for certain front ends

Forms need to autofill data with records only for certain individuals (need restrictions by Driver-less data they have less chance of hippa violation)​

Need the same with reports​

Would like query criteria based forms to have drop down lists for users as most are 60+ with little or no usage or knowledge in data entry

I know this seems Like a lot. The Dbase is already made. Can add it as an attachment with test data if this helps as an attachment. Seriously, Veterans who need transportation to the hospital will be losing this service if I can not fix this. The VA is looking at this as not cost effective based on the cost of fueling these vans. Please help.

 

[jdraw]

The situation with the address is not uncommon. A similar situation exists when people store the selling price of a product in the Product table. When they change the selling price (which is common) and query previous Sales (and lookup the Price from the Product table) they get the latest Selling Price.

To resolve the issue consider the price of the Product stored in the Product table as the current Selling Price. Or in the case of Customer address, consider the Address stored in the Customer table as the current Customer address.

In the SalesOrderItem table you record the productid, the OrderId and the AgreedUponPrice for that sale. It could include discounts for loyalty, clearance sale etc, but it really is the price of that product to that Customer on that day.

Similarly in CustomerTripToHospital table (or whatever name you choose) you record the CustomerId, the Address being used on that day by that Customer etc)

I think it is a structure issue and not something resolved by attachments, but there may be other methods to resolve the issue.

Storing the effective dates (datePriceStarted datePriceEnded) of a Part's price, or a Customer's address can also remove the Price change/ Address change issue. Another approach is to use a flag/switch (Yes/No field) to indicate this price/address is in effect now. It depends a lot on the questions being asked of the data. If the data can change, and you do historic reporting, then some means to accommodate changes is required.

Good luck with your project.

 

[jdoathout]

Thanks Jdraw. yeah I have tried your suggestions but the system always updates to the current address. I need to make it track specifics in the history for accounting issues. Payments are $.41 per mile. It therefor is required to provide accurate payment histories for grants and budgets.

 

[Adam Caramon]

Thanks Jdraw. yeah I have tried your suggestions but the system always updates to the current address. I need to make it track specifics in the history for accounting issues. Payments are $.41 per mile. It therefor is required to provide accurate payment histories for grants and budgets.

To explain part of what jdraw said in another way:

If you have a table with customer info, and another table that tracks trips to the hospital, one of the fields in tblTripstoHospital should be "current address". The field will NOT be linked back to tblCustomer.

Each time a trip occurs, and a record is created in tblTripstoHospital, the current address from tblCustomers will be appended to "Current Address" in tblTripstoHospital.

This way, historical data in "Current Address" never changes. New records created in tblTripstoHospital will always capture whatever the customer's current address is.

If you need more assistance beyond this, you'll need to attach your database.

 

[jdoathout]

I have to take the data out for Hippa reasons but will post it in an hour. I thought I did this with my query but maybe I implemented it wrong.

 

[jdoathout]

Here is my dbase.. It has changed alot from the original as I have been attempting to resolve this issue on my own but have had no luck

 

[jdraw]

You have structural issues. I don't think your tables match your requirements.
Please reread my post #2 and the post#4 from Adam. These are highlighting the issue you are facing.

 

[Adam Caramon]

I think you're going to need a complete redesign for this to work. It looks to me like Client Demographics, Clients Current Address, ClientsAddresses, and ClientsNames should be 1 table instead of 4.

I'm assuming the "Calls" table is where's you're storing the data of each individual call, and as such, you want that table to contain some static data. For example, if there is a call today on 2/14/13, and the client being picked up is Client ID #1, then whatever Client ID #1's address is at this moment will be inserted into the "Calls" table.

If Client ID #1 then moves, and has a change of address, that new address will not be reflected in the "Calls" table, only in the Client table.

Point of contact info should be removed from ClientAddresses and placed in its own table. What is "Amount" in ClientAddresses used for? I suspect that this information shouldn't be in this table, though I could be wrong.

If it is a possibility for you to work with a new database, I can upload a basic database incorporating the details I am talking about above, but then you'd have to spend a lot of time adding in all of the other facets of your database and making them work with the new format.

 

[jdoathout]

Adam, Thank you for the information. I set the structure as best as I could with my needs although I am sure it could be better. The amount is the amount of money the Department of Veterans affairs pays to ambulance services via the ratio $.41 a mile. Given that when addresses changes so does this amount. THAT IN is where the problem comes. People are being over paid and if I can't stop this all transportation services are being shut down except for Ambullettes for Wheel Chair clients.

I originally thought I could store the Clients Pick Up address in the Transportation Request Call Table to help solve my issue, however, the data had to be manually entered every time. Given that this is going to no longer be a single user system but rather have drivers of counties input their own data (these guys are more computer illiterate than I) I am trying to make a system that provides the functionality previously stated in the first post. I am finding this well beyond my pay grade.... I appreciate any and all help you guys are giving seriously thanks from me and the veterans who use this service.