Solved

MS Access Query design help

Posted on 2013-11-03
5
483 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
[X]
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
  • 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

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

691 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