Query To Show New Patients Of Each Day
Jan 11, 2012
"New" patients are patients that weren't present the prior day. I'm needing a query to show how many patients were NEW on each day [each patient listed once for each service date]. Also, some patients will have multiple visits.
Pt Name & Date/MAINAuto IDpt namedate,1smith1/1/20122smith1/2/20123smith1/3/20124jones1/3/20125jones1/4/20126jones1/5/20127garza1/2/20128garza1/3/20129garza1/4/201210garza1/5/201211smith2/2/201212smith2/3/201213smith2/4/201214smith4/16/201215smith4/17/2012
View Replies
ADVERTISEMENT
May 21, 2007
Few months ago, I saw a database about Physicians and Patients, with Skeleton Image on the Switchboard in this site, but now I cant find it, if any one knows its address plz post here, Shall be appreciated.
View 5 Replies
View Related
Mar 1, 2007
I have a query with the following fields:
[Clinic] [Month] [Year] [NewPatients]
Some of the calculations I would like to do are based on the number of new patients that a certain clinic received in the previous month. Does anyone have any suggestions on how I can create a field that has the number of new patients from the previous month?
What I have is this:
PrevMonth: DLookUp("[NewPatients]","ClinicQuery","[Month] = " & [Month]-1)
What I can't figure out is how to account for the fact that I have multiple clinics in the [Clinic] field and each clinic has multiple years in which it was receiving new patients. The PrevMonth statement above works fine for the first clinic listed in the query during the first year it received patients (entries are sorted by month and year), but then just repeats these data for each subsequent year and clinic. Any ideas?
Thanks in advance for any advice!
View 5 Replies
View Related
Apr 8, 2015
I have been asked to create a access data base (using 2007) on tracking patients medical tests that then alerts you when it is overdue set against a scale:
Physical Test - age 20-39 - every 3 years - Male and Female
Physical Test - age 40-49 - every 2 years - Male and Female
Physical Test - age 50+ - every 1 years - Male and Female
PAP Test - 20+ - every 3 years - Female
There is about 10 of these tests like this and then tests specifically for Cancer type patients.
I figured out one way by creating a table with each column being a test but this didn't really work as I need to keep a record of each time they come in and the test is done and then it spits out the next reoccurring test from that date.
I then will need to make a report created from this that alerts on everybody who is overdue to not gotten any of the tests by category.
I then thought okay I will create a table that has the fields as ready something that this is better and to append the records to another form:
Illness - Pulling from tables of Illness
Test - Pulling from table of Tests
AgeGrp - multi-field, referenced from Table of Patients
Gender - Multi-field, pulling from the Table of Patients
Frequency - pulling from table of frequencies with amount of days to calculate
I did this and now I can't figure out anything past this point and how to link all this data up so the right information pops out.
Or is there a better way of going about this.
View 3 Replies
View Related
May 22, 2012
I am trying to create a database for a clinic, and am severely stuck on how to input appointment dates for individual patients.
I have been using the 'student' database from office.com as a template for how to save the dates (given that appointments and attendance are exactly the same!), however, even after following what has been set up in the 'student' template database, I can't seem to replicate it.
Every time I add multiple visit dates for a specific patient, these exact dates show up for every other patient in the database. I need to be able to add different dates for all the different patients.
View 1 Replies
View Related
Feb 3, 2014
I'll first of all explain the purpose of the query. I've built a database to record the admission details of patients admitted to hospital. Amongst other things, the database captures date/time of admission and date/time of discharge.
The query I am building needs to show patients with multiple admissions, and in particular, the number of days that have elapsed since they were last discharged. Within my query is the following subquery that I had written to show this aspect of it:
(SELECT TOP 1 Dupe.[Discharge Date]
FROM [Inpatient Database] AS Dupe
WHERE Dupe.[CHI Number] = [Inpatient Database].[CHI Number]
AND Dupe.[Discharge Date] <= [Inpatient Database].[Admission Date]) AS [Previous Discharge],
Note: "CHI Number" is a unique reference number assigned to every patient.
I then display in a column [Admission Date] - [Previous Discharge].
This works fine where a patient only has one previous admission. However, where a patient has multiple admission, the subquery always returns that patient's first discharge date instead of their most recent discharge date (because I have used the "TOP 1" predicate). It seems that it should be straightforward enough, however I can't seem to work around it. Anything I try results in errors, and so I keep defaulting back to my "TOP 1" solution.
i.e. What I want it to show is:
Name.......Admission Date...Discharge Date.... Previous Discharge... Days Between Admissions
Joe Bloggs ..01/01/2014 .......10/01/2014
Joe Bloggs ..15/01/2014 .......20/01/2014 .......10/01/2014 ..............5
Joe Bloggs ..27/01/2014 .......01/02/2014 ........20/01/2014 .............7
However, what it actually shows is:
Name ......Admission Date ...Discharge Date ...Previous Discharge ...Days Between Admissions
Joe Bloggs ..01/01/2014 ......10/01/2014
Joe Bloggs ..15/01/2014 ......20/01/2014 ........10/01/2014 .............5
Joe Bloggs ..27/01/2014 ......01/02/2014 ........10/01/2014 .............17
View 2 Replies
View Related
Jul 5, 2005
I have a parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.
i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..
View 10 Replies
View Related
Jun 11, 2006
I wondered if someone could help.
I have 3 columns of data within a table and from a select query am trying to seperate certain groups.
Table 1
yr period valuehome
2006 0 100
2006 0 10
2006 1 1000
2006 1 800
2006 2 60
2006 2 50
2006 3 40
2006 3 10
2006 3 5
2006 3 70
2006 4 50
2006 4 5
I wish to show both results for period 0 and an accumulative total for periods 1-4
so results would be
year period Total sum
2006 0 110
2006 1-4 2090
Can anyone help?
thanks
Paul
View 5 Replies
View Related
Mar 8, 2005
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
Can someone see where I’m going wrong?
Slaughter
slaughter at mizzou dot edu
View 9 Replies
View Related
Mar 30, 2006
I have a database that I run a few queries on to do some simple calculations for me using IIf statements. I did not need these fields in the datasheet view so I unclicked the Show button and everything looked great. Now when I go back into my query, those feilds are gone, how can I re-show them to make some changes? Thank you for any help.
Jimmy
View 4 Replies
View Related
May 12, 2006
A friend has developed a database at college. All seems pretty well but the query she uses to populate a form and reports is incorrectly displaying the curence. Her main tables are just number fields. The query calculates correctly but her lecturer has told her the query needs to show a £ sign with the figures retrieved. Any ideas?
View 2 Replies
View Related
Nov 6, 2006
Hi all,
Hope every is good.
I've stumbled accross an issue where i have 1 table (140,000 records) and 1 query (141,000 records) making up my MERGE query.
My merge query brings back 55,500 results, this means that there are on 55,000 records that match by a unique ID.
I need to create to other querys displaying the result that are NOT in the MERGE query, 1 for each table/query used in the MERGE query
This will then show me what data we and the client have thats out standing.
cheers
Error#9
View 1 Replies
View Related
Apr 4, 2007
I have a query that includes the following fields
Year
EventNumber
CorrectiveActionNumber
ResponsibleGroup
Committment
Each event will have multiple corrective actions. For each corrective action assigned to a group, the "committment" is also assigned to them, but I also have to assigned a committment to our group (SRG). So this makes the query results show two records for each corrective action, one with the group assigned committment and one for my groups committment. I need a query that will show corrective actions that have the group committment assigned but not a corresponding SRG committment assigned. However since my query results in two separate records, I am unsure how to do this.
Any suggestions?
Thanks,
Jim
View 4 Replies
View Related
Oct 5, 2007
Hello..
I have a one applicant to many callers database setup. I want to be able to see only the last call to the applicant based on date.
I have tried the following:
SELECT tblApplicant.ApplicantID, tblApplicant.ApplicantLName, tblApplicant.ApplicantFName, tblApplicant.LastFour, tblApplicant.TestDate, tblApplicant.JacketExpireDate, tblApplicant.ContinueProcessDate, [ApplicantLName] & ", " & [ApplicantFName] AS Name, tblYsNo.YesNoType, tblLuTestType.Type, tblCaller.Comment, tblCaller.Date
FROM tblYsNo INNER JOIN ((tblApplicant INNER JOIN tblLuTestType ON tblApplicant.TestType = tblLuTestType.TestType) INNER JOIN tblCaller ON tblApplicant.ApplicantID = tblCaller.ApplicantID) ON tblYsNo.YesNoMain = tblApplicant.Interested
WHERE (((tblYsNo.YesNoType)=[Forms]![frmReport]![cboInterest]) AND ((tblLuTestType.Type)=[Forms]![frmReport]![cmdTestType]))
ORDER BY tblApplicant.ApplicantLName, tblCaller.Comment DESC , tblCaller.Date DESC;
Which had both the caller date and coller comment descending and I have also had the caller date and coller comment ascending. Then I have tried one at a time. But in the report that is based off of this query it still only shows the first comment by date not the last comment.
IE
01/01/2004 comment this is a test
01/02/2004 comment this is a test again
01/03/2004 comment test test
On the report I only want to see the 01/03/2004 comment not the 01/01/2004 or 01/02/2004.
Does this make sense? I have been banging my head...
View 6 Replies
View Related
Dec 8, 2004
Please help, I have a form with 7 option buttions and a calendar. i would like to be able to run a query from the selection that is made. the way this would work is:
7 option buttions - listing different locations
Calendar - select date
the query would list any event and the start time and end time that fell on that date.
my problem is that I dont know were to start. i have been told to use SQl statements. i have tried this but only failed miserably. the events and times are in 2 different tables, i cant get a SQL stastment to work like that.
I have tried to use a List box, using the Row Source to display a query, but only 1 row showed. it still was unselectable.
Please help, dont know were to go with.
John
View 1 Replies
View Related
Dec 29, 2004
I am attempting to run a query that only captures the fields that are null so that I can
run a report that shows outstanding information still required.
Essentially, each record is a provider. I have reviewers that complete an Access Form to
certify each provider. The form consists of approx. 120 checkbox criteria. If it is checked
they meet that criteria...unchecked they do not. The form is based off of 4 tables of which
I am querying from.
I'm trying to get a query to identify only those criteria that are unmet for each provider.
In other words...only show the unmet fields/checkboxes. From that I'll make the report.
Any help is greatly appreciated.
View 2 Replies
View Related
Jun 7, 2007
Is it possible to convert fields listed down to show across.
IDNo Code Position
AAA B01 1
AAA C01 2
AAA D01 3
AAA E01 4
I want to show it as
IDNo Code Code Code Code
AAA B01 C01 D01 E01
Is this possible using Access 2003?
View 7 Replies
View Related
May 24, 2007
Hello, I have a system where a person may have several posts.
Ive made a query which shows everyone who has a post.
The problem is that if a person has three posts, the record shows three times, how do i make tell a query to do this:
Get all records, look at the name field, show only 1 record per name.
How do i do it? Whats this called? Where do i find out more?
Ant...
View 11 Replies
View Related
Nov 7, 2007
I am really new to using access and I have a massive table 16000 or more entries. What I need to do is take this example data :
VPIAddress
001:99_97_AO
001:99_96_AO
001:99_0_AI
001:986_51_CI
001:986_50_CI
001:985_51_CI
001:985_50_CI
001:984_50_CI
001:983_51_CI
001:983_50_CI
(This is just a tiny fragment of the total data)
The 001: to the first '_' is the important bit this is defining a "Router" what I need to do is total up how many '001:*_' there are, not including duplicate numbers.
Firstly if I understood this (http://www.access-programmers.co.uk/forums/showthread.php?t=92001) post correctly I need to have all the VPIAddress the same. If this is correct is there a way to get access to delete the data after the first '_' ? (I would of posted in this tread but i didnt think that would be right, if im wrong mods move the post :) )
Secondly will the info in that post help me to reduce the data into this :
VPIAddress
001:99_
001:986_
001:985_
001:984_
001:983_
So that I can then see how many Physical "routers" there are
If any one can help me and tell me that my idea will work then tell me how to do it id be extremely happy!
Even if any one says that wont work and gives me the correct way to do it and does a virtual "Clip around the Head" and call me a Daft Bugga I would still be happy because I wouldn't have to trawl through the entire 16000+ entries deleting and modifying.
View 4 Replies
View Related
Jan 3, 2008
I have two one-many related tables: Transactions and Site Details. Most of the Transactions information can be filled in first, and then the Site Details filled in when they are ready.
How do I get a query based on both tables to show those transactions with missing site details? I have tried IsNull(SiteName) in the SiteName field in the Design View, but I don't get any results at all (for records with no SiteName). The answers I've seen in Google seem to be variations on the theme, but I am probably missing something simple (as usual).
I orignally posted this with a question about tracking revision history. I thought it might be a similar solution, since I want to add information to the blank fields when the information becomes available. But I need to find those transactions WITH blank fields first.
View 3 Replies
View Related
Jan 24, 2008
I have a table for stock with two fields, KEY and DESCRIPTION; a table for manufacturers with two fields, KEY and NAME; and a third table which links to them both with a many-to-one join with three fields, KEY, STOCK-KEY and MFR-KEY. What I am trying to do is write a query that for a given manufacturer (entered via a parameter) shows a single line for all stock records that are NOT linked to it via the third table. I am sure it should be simple but all my attempts fail to exclude stock linked to the manufacturer if it is also linked to another manufacturer. Any ideas?
View 8 Replies
View Related
May 25, 2006
Does anyone know if it's possible to show the results of a query on a form? I have 4 people entering various "outcomes" into a table. There are a possible 8 different outcomes and the one i'm interested in is the "completed" outcome. I would like to be able to show on the form used to enter the data, the total number of "completes" so far.
View 3 Replies
View Related
Jan 7, 2005
SELECT TimeFile.Component, Sum(TimeFile.Hours) AS SumOfHours
FROM TimeFile
WHERE (((TimeFile.Date) Between #12/15/2004# And #12/18/2004#) AND ((TimeFile.Project)="DSME") AND ((TimeFile.Area)="DSM-1 LIFT FRAME"))
GROUP BY TimeFile.Component;
I am having a problem trying to get all of the TimeFile.Components to show up. There are no hours in some of the TimeFile.Components, but isn't there a way to get them to all show up with a 0? Plz help.
View 2 Replies
View Related
May 15, 2006
Hey all I have another one.
When I run a query, whenever the criteria is not met, I want the column header for the row to not show up in the query.
I am running a select query and I have 10 items on the query, I select what I want via check box and then hit OK to run the query.
I want the fields that are not checked to not show up in the query at all.
Right now I still get the column header, any way to get rid of it.
Thanks a lot for any help.
View 1 Replies
View Related
Apr 2, 2015
I'm trying to develop a werehouse database for me.and I'm in a terrible dead end.Formely I have a table Articles where I have all the articles on the market with the description, EAN, price. and a table Movement where with a code (related with the article's table) I can store all the item I fisically have in my werehouse and items I sell. (with a boolen field that set if the item is an income or an outcome).
I have a form linked to the Movement Table.I'd like to write an EAN in a textbox query that EAN to the article table and output the results in a datasheet.
View 2 Replies
View Related
Jul 30, 2013
Currently I have a query, QRY_Test which contains a load of records under the fields "Gift_ID" and "Person_ID". I have another query, QRY_PersonList in which I would like to show all of the "Person_ID"s except the ones returned by QRY_Test.
View 3 Replies
View Related