Records populating records?

[lindabaldwin]

Hi everyone,

This is what I'm trying to do. I making a database for diabetes insulin dosing. I need the current blood glocose (currentbg) to populate the next record in the previous blood glucose (previousbg) field. These will be specialized for individuals in the records. Any ideas on how to do this?

Thanks!

[Jim Doherty]

Linda,

The explanation is with respect not specific enough you will have to outline how and where your data is being stored. Do you have a single table or more than one table. From the sounds of it you have more than one table??

Jim

[missinglinq]

Jim is right, we need more information about exactly what it is you're trying to accomplish and how you are entering records. For example, is your data being entered thru a form (it should be) or directly in the table? Is someone sitting down and entering a series of records for a single patient, i.e Patient A's 8am bgl, 12nn bgl, 4pm bgl etc. or is the user entering records for Patient A's 8am bgl, Patient B's 8am bgl, Patient C's 8am bgl? Things like that.

Welcome to TheScripts to both of you!

Linq ;0)>

[lindabaldwin]

Here is a general overview of the project. I am trying to create an insulin dosing tool based on change of blood sugar readings over time. I have two tables, one containing patient information (name, physician name, medical record number, etc.) and one containing the information I will need to calculate a recommended dosage (current blood sugar reading, time of current blood sugar reading, previous blood sugar reading, time of previous blood sugar reading, recommended dosage, etc.). I have created a relationship between these two tables based on medical record number. I have created a form (primary is patient data, subform is blood sugar information).

What I would like to do is use the current blood glucose reading and time from one record of the same patient to populate the previous blood glucose reading and time in the next record of the same patient. I would like this to self-populate in the form the next time the patient's medical record number is entered into the form. Is this possible?

I guess it would have to involve sorting the records by patient and date/time of the "current blood glucose reading" then extracting the current information and copying it to the previous information of the new record. Am I making any sense? Can Access do this?

Thanks.

[Jim Doherty]

Here is a general overview of the project. I am trying to create an insulin dosing tool based on change of blood sugar readings over time. I have two tables, one containing patient information (name, physician name, medical record number, etc.) and one containing the information I will need to calculate a recommended dosage (current blood sugar reading, time of current blood sugar reading, previous blood sugar reading, time of previous blood sugar reading, recommended dosage, etc.). I have created a relationship between these two tables based on medical record number. I have created a form (primary is patient data, subform is blood sugar information).

What I would like to do is use the current blood glucose reading and time from one record of the same patient to populate the previous blood glucose reading and time in the next record of the same patient. I would like this to self-populate in the form the next time the patient's medical record number is entered into the form. Is this possible?

I guess it would have to involve sorting the records by patient and date/time of the "current blood glucose reading" then extracting the current information and copying it to the previous information of the new record. Am I making any sense? Can Access do this?

Thanks.

Hi Linda,

Forgive me but from what you are saying It seems to me that you MAY have a design issue on the layout of your secondary table?

The Patient table should be fine, in that at least you can correctly identify an individual patient by referencing them using the Medical reference number

The second table is what concerns me! The essence of what you are doing is to record each transaction as it is happens in this table linking this to the medical record number (foreign key linked to primary key one to many). The controlling feature in the 'transaction' is would be a DATE and a time.(No problem thus far)

So.... if we entered a record today we would clearly have the current blood sugar reading to hand and that reading information would be stored in a single row WITH the DATE and TIME.

IF... shall we say three days later we entered another blood sugar level AGAIN that record would be inserted as before. IT IS AT THIS POINT that we can then do the comparison by retrieving from the database the MAXIMUM date and TIME for the individual patient WHERE a record exists PRIOR to the current transaction record that you are currently viewing on screen. This is done by the MAX function in Access.

The only concern I have conceptually in the design (I obviously havent seen it) is that you seem to me to be recording redundant data in the same row. Why type into the SAME row CURRENT AND PREVIOUS information when this can actually be extracted by a sub query and displayed as a column alongside any existing data row without human intervention. A calculation can then be done on the extracted historical data 'as displayed' in the current row set AGAINST the current reading (remember this is 'extracted' and displayed NOT typed manually into that CURRENT row). You then merely enter into that current row the dosage based on any algorithim you have that. Arguably if your algorithim is fixed then this too, could be calculated to give you your dosage without you continually having to work it out manually.

I hope I am making sense to you on this. If not feel free to PM me You guys do a great job!.

In short, Access can manipulate data and arrange and display it in a manner to suit your purposes provided you have a clear understanding of relational theory SQL and DB design. There are plenty of databases out there that started off designed one way became entrenched and which turned out that they wish they dealt with another way

Regards

Jim

[Jim Doherty]

Jim is right, we need more information about exactly what it is you're trying to accomplish and how you are entering records. For example, is your data being entered thru a form (it should be) or directly in the table? Is someone sitting down and entering a series of records for a single patient, i.e Patient A's 8am bgl, 12nn bgl, 4pm bgl etc. or is the user entering records for Patient A's 8am bgl, Patient B's 8am bgl, Patient C's 8am bgl? Things like that.

Welcome to TheScripts to both of you!

Linq ;0)>

Thanks for that moderator

[lindabaldwin]

Great questions and input Jim,

The reason (I think) I need the previous and current BG info on the same line is because I need both to calculate the dosage. The dosage calculation is based on the hourly rate of change of the BG. It is also helpful for the previous info to appear on the same form so that physicians and nurses can verify the BG changes and thus verify the dosing. A patient's BG would be checked hourly until stable, then every two hours, then every four until stable. Insulin regulated BG is used on all critical or surgical patients with BG above 110 because it reduces the risk of infection. I've already written the logic (code) for the dosing in Excel, but the docs want to be able to query it for a patient's complete insulin dosing record upon discharge, so I thought it would be a good idea to put it into Access. I am having difficulty with the transition though. I am still confused on how to take data from one record and transfer it to another.

Thanks for your help!

[Jim Doherty]

Great questions and input Jim,

The reason (I think) I need the previous and current BG info on the same line is because I need both to calculate the dosage. The dosage calculation is based on the hourly rate of change of the BG. It is also helpful for the previous info to appear on the same form so that physicians and nurses can verify the BG changes and thus verify the dosing. A patient's BG would be checked hourly until stable, then every two hours, then every four until stable. Insulin regulated BG is used on all critical or surgical patients with BG above 110 because it reduces the risk of infection. I've already written the logic (code) for the dosing in Excel, but the docs want to be able to query it for a patient's complete insulin dosing record upon discharge, so I thought it would be a good idea to put it into Access. I am having difficulty with the transition though. I am still confused on how to take data from one record and transfer it to another.

Thanks for your help!

Hi Linda,

thanks for the kind words,

I'm not saying you can't have it on the same line I am saying its HOW you get it there that matters. I would do exactly the same thing myself (have it on the same row) BUT make Access display it by calling in the previous entry via a subqueried column rather than typing a previous entry in manually to another field in the same row, thats the issue, you are repeating data as opposed to fetchng up data already entered if you get me. :))

Regards

Jim

[lindabaldwin]

Hi Jim,

I think I am beginning to understand. Do you think the best way to do this is to write code for it or to use Access tools to make a query and populate the field with the query results?

Linda

[Jim Doherty]

Hi Jim,

I think I am beginning to understand. Do you think the best way to do this is to write code for it or to use Access tools to make a query and populate the field with the query results?

Linda

There are a couple or three ways to do this you could use the DMAX function to return the Last reading date PRIOR to the current reading date for the specific record row (and thus return the reading on that occasion) or you could write a function which could open a recordset in code and limit its return value to the row that you wish to return the previous result from, or indeed you could use the DLOOKUP function to lookup the value of the last entry prior to the current one.

Long term and particularly if your database is likely to grow substantially in terms of datasize, or if you see it as something that might be likely to be a system you seek scaleability on i.e.upsizing to MS SQL server in the future then my advice would be to use raw SQL wherever possible, as it is faster than DMAX or DLOOKUP. These functions are essentially MS Access based whereas SQL is generic and thus more scaleable.

Either way, you place the end results so to speak in the 'Query Grid' as a column to the right of the current reading and then to the right of that any calculation you might wish to make to compare and give the dosage based on comparison between the two results etc etc.

After this you would then would be in a position to display that 'previous' result on the screen form in the same row and because it a computed return value you would lock it off colour it grey or something because it (the return value) would not be editable for that line if I am making myself clear.

Don't get me tooooo wrong on this I am not saying don't use DMAX or DLOOKUP and neither do I wish to be overly technical where you ultimately judge for yourself its just that provided you have options explained to you then you can make an informed choice.

If you get stuck on this.... then if you detail your tables to me ie name fieldname,datatype,size and so on, I could knock up what I am eluding to in minutes and pass it back to you to ponder on, whereas you might struggle trying to interpet what I am typing here. I think you would then see the "aaahhhhh I get it factor" coming into it. Feel free to PM me if you wish or to add to buddy list


Jim