Improve company productivity with a Business Account.Sign Up

x
?
Solved

MS Access Query design help

Posted on 2013-11-03
5
Medium Priority
?
500 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 41

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 41

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 41

Accepted Solution

by:
als315 earned 2000 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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

595 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