Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS Access Query design help

Posted on 2013-11-03
5
467 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 40

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 40

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 40

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

856 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