Solved

MS Access Query design help

Posted on 2013-11-03
5
447 Views
Last Modified: 2013-11-06
I have a table looking at a group of individuals and their arrivals and departures within a range of time, with the following data points:
[PatID] = ID unique to the individual, repeats indicate multiple visits by the same individual
[CSN] = randomly generated ID unique to current visit; is not ranked by date/time
[PrevDepDate] = departure date/time for previous visit; may or may not correlate with last ArrDate on record
[ArrDate] = arrival date/time for current visit
[DepDate] = departure date/time for current visit
DataI am looking for help with Query design, primarily trying to figure out how to create running totals based on previous records, in order to generate the following outputs:
[TotPrevArr] = a running count of the number of previous visits based on ArrDate
[Arr-PrevDep] = ArrDate - PrevDepDate
[Arr-Prev30d] = 1 if Arr-PrevDep is <31 days; 0 if it is =31 days
[TotArr-Prev30d] = a running count of the total number of Arr-Prev30d that were <31 days to date
OutputIf possible, I would also like to be able to generate a TotArr-Prev30d count for a specified window of time prior to the ArrDate (e.g. TotArr-Prev30d within 12 months prior to ArrDate).

Thank you.
0
Comment
Question by:manonamtn
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39620918
Can you upload sample DB with this table?
0
 

Author Comment

by:manonamtn
ID: 39621343
Attached is a table with the sample data.
EE-1.accdb
0
 
LVL 39

Expert Comment

by:als315
ID: 39621794
Look at Query1 in sample.
I see one problem in your data:
tableI see there some records, where DepDate from previous record is not equal to PrevDepDate.
May be it is error in sample table only, but duplication same values in different records isn't good idea. I've added queru qtblData, where this value (PrevDepDate) is taken from previous record. Query2 is based in query qtblData.

In qtblData you can also limit records to 12 month or to other value
0
 

Author Comment

by:manonamtn
ID: 39622373
Sorry, I am not seeing where the database with your queries is attached.

Also, I noticed a typo in my original question. The PrevDepDate may or may not correlate with the DepDate from the previous record.

Maybe this will clarify things: ArrDate and DepDate in the record indicate the current encounter at our department. However, PrevDepDate can be a departure from a different department subsequent to the last DepDate we have on record. In other words, PrevDepDate will either coincide with the last time they departed from our department or sometime afterwards if they visited another department in the interim. But those data points will be part of the dataset we receive and not queried.

What I need specifically are queries to generate the outputs in columns TotPrevArr and TotArr-Prev30d.
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39624874
Sorry, I forgot to attach DB. Query qtblData was updated according to your last comment. Now you can filter data. There is 24 monthes, but you can change it to any value
EE-1.accdb
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now