473,320 Members | 2,158 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

calculating weight loss between visits

Hi. I am working on a DB for obesity and one of my queries is tracking weight loss between each clinic visit. I have created the query, but do not know what to input in the criteria box. Unfortunately, there is no way to determine what visit # each visit will be, it is just labled as RTN (for return) or NWM (for new patients). I may not be able to change this. The only way to know is looking at the dates. Any ideas?
Nov 8 '06 #1
3 1606
NeoPa
32,556 Expert Mod 16PB
TGremlin,

This looks a little complicated (even though expressed very briefly).
Could you give a little more information as to the record structure etc.
Some tips on posting have recently been added to this forum. I'm sure you'll find it helpful to look through ( http://www.thescripts.com/forum/thread559246.html ).
Nov 8 '06 #2
My query is set up with two tables. the first is called tblPatient. The main fields are PtFirst, PtLast. The second table is tblClinicVisit . The main fields are Weight, DateofVisit. This is what I have used for my query. I need to know how to calculate weight loss/gain between each visit. The only way to figure this out is to sort the DateofVisit and the Weight for that specific visit for each and then compare it to the subsequent visits. OR do I only have the option of creating a query for these fields, sorting it alphabetically by Ptlast,PtFirst and manually calcuating wt loss/gain per patient?


TGremlin,

This looks a little complicated (even though expressed very briefly).
Could you give a little more information as to the record structure etc.
Some tips on posting have recently been added to this forum. I'm sure you'll find it helpful to look through ( http://www.thescripts.com/forum/thread559246.html ).
Nov 10 '06 #3
NeoPa
32,556 Expert Mod 16PB
There must be a PatientID field which is a PK of tblPatient and an FK of tblClinicVisit.
There is no real way of connecting two separate records from the same table but with different values (you need to connect one visit with the previous one), so we will use the Domain Aggregate function DMax() to get what we want - less efficient - but possible (just).
Expand|Select|Wrap|Line Numbers
  1. SELECT tblPatient.PatientID, tblClinicVisit.DateofVisit, tblClinicVisit.Weight, IIf([DateofVisit]=DMin('[DateofVisit]','[tblClinicVisit]','[PatientID]=' & [PatientID]),0,Val(Mid(DMax('Format([DateofVisit],''yyyymmdd'') & [Weight]','tblClinicVisit','(([PatientID]=' & tblPatient.PatientID & ') AND ([DateofVisit]<#' & Format([DateofVisit],'d mmm yyyy') & '#))'),9))) AS LastWeight, [Weight]-[LastWeight] AS Diff
  2. FROM tblPatient INNER JOIN tblClinicVisit ON tblPatient.PatientID=tblClinicVisit.PatientID
This ignores PtFirst & PtLast as irrelevant.
The dates used are those from tblClinicVisit.
This is quite wide so be careful copying and pasting. I have tested that this works, so if you're careful it should do the job for you.
Nov 12 '06 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: arnold | last post by:
Hi, I've been knocking my head against the wall trying to create an XSL transform to perform "normalizations" of a set of XML files that have a common structure. % XML file before transform
8
by: johnds | last post by:
This is my third question about eliminating accounting entries in a clinical database, yet retaining the valid record. Latest wrinkle is being able to sum the visits to the doctor, and the units of...
3
by: sharma1985 | last post by:
Hi all, i m working on a bioinformatics problem. Regarding that i would like to know about the perl programming. I also like to know that how could i calculate the Isoelectric point and Molecular...
2
by: eloi-ribeiro.blogspot.com | last post by:
Hi, I'm having a problem with a query. I'm running Access 2000 and a table with 35000 rows. The table contains several visits to the spots with a CODE (spot code) and DATE (visit date)...
1
by: Victor | last post by:
Can someone help me with the formula(s) to calculate the RGB components for a gradient from green to red? I'm trying to display a nice graphic with a gradient that reflects the profit or loss and I...
3
by: typhoo1 | last post by:
Hi All, I'm hoping someone is able to help me with the following: I am creating a database for a weightloss group. The database is relational and has one table containing client details. ...
5
by: bananahead | last post by:
Hello all, and thanks again for the help, I have been very pleased with the quality posts on this forum! Here what I need to solve: I have 2 tables on a db, one is called visitor_log and here...
11
by: charmeda103 | last post by:
i have to calculate a total amount of pounds for my program. i am getting each pound calculated but i have to add up all those pounds into one total. the problem is one of the total pounds is not...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.