Solved

MS Access Query design help

Posted on 2013-11-03
5
434 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
Comment Utility
Can you upload sample DB with this table?
0
 

Author Comment

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

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

772 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

16 Experts available now in Live!

Get 1:1 Help Now