I am trying to make my database to capture the updated price of products , i.e. reflect the changes in price but keeping the earlier records with old price at that time. The database is attached.
This is a database of healthcare services. In each transaction, we charge by codes. Each code has a price and the price can change overtime. In this file, some of codes changed the price as of 5/9/2018.
- table: MainT – has TRX ID (transaction ID) with patients name, date of services. TRX ID 1-17 are before price change, TRX 396 and 397 are after the price change.
- table: CodeT – has Code ID and Code
- table: CodePrice Table - has Code ID, price, effective date, End_ Date – the end date for the currently valid price is blank (since it is not ended yet)
- table: procedurejunkT – has TRX ID, Code ID, – this connect the transaction and the codes for each transaction
- query: code.priceQ - from “CodePrice Table”, fill the blank for ‘end date’ with “Current Date”
With these as preparation, I try to capture the price for the specific code on a specific Date of Service by conditioning ; Effective Date < Date of Service < End_Date case when the End_Date is blank, it should have [Current Date].
To execute this, I made 2 queries: PriceAppendQ and PriceAppendQ2.
PriceAppendQ refers to the table"CodePrice Table. SQL runs fine but it does not return the transactions after the price change (TRX ID 396 and 397), since the End_Date for these are blank? PriceAppend2 refers to the query: Code.PriceQ. This should be able to capture the TRX ID 396 and 397, since the End_Date is fed with Current Date, but SQL does not run.
I am puzzled here and cannot figure out what I have done wrong. It is a lengthy explanation, but if someone can kindly take a look, I’d appreciate it very much. inquiryfile.odb