Newbie doing Queries - Help would be appreciated

[Avian Flu]

SOLVED: Newbie doing Queries - Help would be appreciated

For the last week and a half I've been thrown into Access and my SQL skills are pretty much abysmal so I'm doing this in design view, just throwing this up here now.

I am working on a patient data sheet, and I'm trying to set up a query where it counts the number of times the patient has visited the office. However, the kicker is that if the patient has done a closing, then the next time they come along (for who knows how many months later) there is a new entry in the query and it starts a new count until closing.

So for example, the Table will have something like:

Name ______Date____Closing
John Smith - 1/14/14 -
John Smith - 1/18/14 -
John Smith - 1/20/14 - yes
John Smith - 2/3/14 -
John Smith - 2/7/14 -
John Smith - 2/14/14 -
John Smith - 2/15/14 - yes
John Smith - 3/12/14


The query would pop up with 3 entries of John Smith that would look something like....

Name_____Number Times Visited___Closed
John Smith - 3 - yes
John Smith - 4 - yes
John Smith - 1 -

I haven't the foggiest clue where to start writing this, so any help would be greatly appreciated.

 

[bob fitz]

Can you show us the actual SQL statement used for your query

 

[BlueIshDan]

Keep in mind we don't know your system, therefore we do not know what a Closing in. (for those of us that haven't worked in a field or project containing a similar process)

 

[Avian Flu]

Just to let you know, "Times Visited" isn't Correct and I'm doing this in Design View..x_x..


SELECT [Financial Daily].[Name], Count([Financial Daily].Date) AS [Times Visited], IIf([Closing]=Yes,"yes"," ") AS Closed
FROM [Financial Daily]
GROUP BY [Financial Daily].[Name], IIf([Closing]=Yes,"yes"," ");



BlueIshDan, The Closing is just a check box. When a patient comes in they'll be there for a certain set number of treatments. When those treatments are done, the office will put a check in Closing to say they've treated that amount of times and the patient is done with their treatments. The next time they come back it'll be for a new set of treatments and they'll go through it again until they're reached the end of the treatments before checking the box again. What I'm trying to get it to do is to count the two different sets of treatments.

 

[bob fitz]

What result do you get if you use:

SELECT [Financial Daily].[Name], Count([Financial Daily].Date) AS [Times Visited]
FROM [Financial Daily]
GROUP BY [Financial Daily].[Name]

 

[Avian Flu]

Bob, I am getting an error box with,

"Your query does not include the specified expression 'Iif(Not[Closing]=0, "yes"," ")' as part of an aggregate function. I'm guessing there's something wrong with the formula?

Oh wait, I'm copying and pasting the same thing.

BLAH. hold on a sec.

Edit: Okay, that was weird.

 

[bob fitz]

Bob, I am getting an error box with,

"Your query does not include the specified expression 'Iif(Not[Closing]=0, "yes"," ")' as part of an aggregate function. I'm guessing there's something wrong with the formula?

Sorry, I did edit my last post, please try:

SELECT [Financial Daily].[Name], Count([Financial Daily].Date) AS [Times Visited]
FROM [Financial Daily]
GROUP BY [Financial Daily].[Name]

 

[Avian Flu]

So what's coming up right now is just names and the total times visited

So John Smith - 8



I'm trying to get it so that it'll stop counting a section once there is a check box in Closing.

And then the next date it'll start counting everything after the check box until the next Closing.

 

[bob fitz]

I think I may be out of my depth here but could you try:

SELECT [Financial Daily].[Name], Count([Financial Daily].Date) AS [Times Visited], [Financial Daily].[Closing]
FROM [Financial Daily]
GROUP BY [Financial Daily].[Name], [Financial Daily].[Closing];

 

[Avian Flu]

This is what I basically wound up with the first time.

It's counting the total entries checked and the total entries unchecked instead of the dates from one checkmark to another.

Name_-_Times Visited - Closed
John Smith - 2 - (checkmark)
John Smith - 6 -

 

[bob fitz]

This is what I basically wound up with the first time.

It's counting the total entries checked and the total entries unchecked instead of the dates from one checkmark to another.

Name_-_Times Visited - Closed
John Smith - 2 - (checkmark)
John Smith - 6 -

Yes, I thought that might be the case. In that case I am unable to offer you any thing else unless you can upload a cut down version copy of your db, with a few dummy records to illustrate the problem. If you can do that I will gladly play around with it and try to find a solution. Otherwise, we will have to wait for someone more knowledgeable to pass by.

 

[Avian Flu]

Would it be something like (and I know I'm butchering the language)

Count(IIf([Closing]=no, [Date], "")< Iif([Closing]=yes, [Date], " "))


Or something of that sort? It's probably incorrect...and it'd have to count the X after the date as a new entry. x_x my brain hurts.

Here's the dummy database that I'm working with right now. I think I attached this correctly.

 

[bob fitz]

Really not sure how to go about this, which is why I asked you to post the db, which will allow me to play around and see if I can find an answer. I'm off to bed now but will take a proper look at it tomorrow evening if nobody else has stopped by with a solution.

 

[Avian Flu]

Pleasant dreams, and thanks for helping out. I'll keep on trying to get it to work in the meantime with my limited Access knowledge.

 

[CJ_London]

I'll take a look - so just to clarify the requirement, from your initial posts:

From this data

Name ______Date____Closing
John Smith - 1/14/14 -
John Smith - 1/18/14 -
John Smith - 1/20/14 - yes
John Smith - 2/3/14 -
John Smith - 2/7/14 -
John Smith - 2/14/14 -
John Smith - 2/15/14 - yes
John Smith - 3/12/14

You want this result

Name_____Number Times Visited___Closed
John Smith - 3 - yes
John Smith - 4 - yes
John Smith - 1 -

And this refers to the field/table names you are actually using

SELECT [Financial Daily].[Name], Count([Financial Daily].Date) AS [Times Visited], [Financial Daily].[Closing]
FROM [Financial Daily]
GROUP BY [Financial Daily].[Name], [Financial Daily].[Closing];

Also, be aware that Name and Date are both reserved words - I've left as is below, but strongly recommend you change them, using reserved words causes all sorts of problems.

Try this as a query - if the above names are correct you should be able to just copy and paste this into your query sql window

SELECT [Name], (SELECT COUNT(*) FROM [Financial Daily] AS T WHERE [Date] between Nz((SELECT TOP 1 [Date] From [Financial Daily] AS T WHERE [Date]<[Financial Daily].[Date] AND [Name]=[Financial Daily].[Name] AND Closing=true ORDER BY [Date] DESC),(SELECT Min([Date]) FROM [Financial Daily] as T WHERE  [Name]=[Financial Daily].[Name])-1)+1 AND [Financial Daily].[Date]) AS [Number Times Visited], IIf([Closing],'Yes','No') AS Closed
FROM [Financial Daily]
WHERE ((([Closing] Or [Date]=(select max([Date]) from [Financial Daily] as T where [Name]=[Financial Daily].[Name]))=True))

 

[Avian Flu]

Oh my gosh. It works.

I bow before you and your awesome knowledge of Query making, good sir!

Now to figure out how the process works so I can write another one in the future.

THANK YOU AGAIN!

 

[CJ_London]

Glad to be able to help. To break the query down there are a number of components


This bit finds the last record date where closing is true

(SELECT TOP 1 [Date] From [Financial Daily] AS T WHERE [Date]<[Financial Daily].[Date] AND [Name]=[Financial Daily].[Name] AND Closing=true ORDER BY [Date] DESC)

If it is null (for those records where there isn't a previous record where closing is true) we use

(SELECT Min([Date]) FROM [Financial Daily] as T WHERE  [Name]=[Financial Daily].[Name])-1)

- we subtract 1 because we add it back later for those records where there is a previous closing date

Finally, which records to show? Just those where it is closed or the last one, so we have

WHERE ((([Closing] Or [Date]=(select max([Date]) from [Financial Daily] as T where [Name]=[Financial Daily].[Name]))=True)

 

[Avian Flu]

Glad to be able to help. To break the query down there are a number of components


This bit finds the last record date where closing is true

(SELECT TOP 1 [Date] From [Financial Daily] AS T WHERE [Date]<[Financial Daily].[Date] AND [Name]=[Financial Daily].[Name] AND Closing=true ORDER BY [Date] DESC)

If it is null (for those records where there isn't a previous record where closing is true) we use

(SELECT Min([Date]) FROM [Financial Daily] as T WHERE  [Name]=[Financial Daily].[Name])-1)

- we subtract 1 because we add it back later for those records where there is a previous closing date

Finally, which records to show? Just those where it is closed or the last one, so we have

WHERE ((([Closing] Or [Date]=(select max([Date]) from [Financial Daily] as T where [Name]=[Financial Daily].[Name]))=True)

CJ,

I took your advice and changed the fields (and added an additional field) so there is a [First Name], [Last Name], and then [Date Attended], I've also added two different people with similar first names and similar last names together (a Jane Smith, and a John Carpenter). While the change for [Date Attended] was pretty easy, the additional last name added to the expression is giving me weird results.

So this is what I did to the code. But I think I butchered it somewhere.

SELECT [Financial Daily].[First Name], [Financial Daily].[Last Name], (SELECT COUNT(*) FROM [Financial Daily] AS T WHERE [Date Attended] between Nz((SELECT TOP 1 [Date Attended] From [Financial Daily] AS T WHERE [Date Attended]<[Financial Daily].[Date Attended] AND [First Name]=[Financial Daily].[First Name] AND Closing=true ORDER BY [Date Attended] DESC),(SELECT Min([Date Attended]) FROM [Financial Daily] as T WHERE  [First Name]=[Financial Daily].[First Name])-1)+1 AND [Financial Daily].[Date Attended]) AS [Number Times Visited], IIf([Closing],'Yes','No') AS Closed
FROM [Financial Daily]
WHERE ((([Closing] Or [Date Attended]=(select max([Date Attended]) from [Financial Daily] as T where [First Name]=[Financial Daily].[First Name]))=True));

What led me to make another field is that I figured it might be easier sorting out people (and avoiding typos and spelling in a single patient name). The only downside to this is that there are multiple people the same first name, and families with the same last name come in as well. It's not doing any good with the query at the moment. D: Any help you can offer is greatly appreciated.

I've attached the access file in case you need a visual look at what it's doing. x_x

 

[Avian Flu]

Well....I did some tweaking and I *ALMOST* fixed the problem.

SELECT [Financial Daily].[First Name], [Financial Daily].[Last Name], (SELECT COUNT(*) FROM [Financial Daily] AS T WHERE [Date Attended] between Nz((SELECT TOP 1 [Date Attended] From [Financial Daily] AS T WHERE [Date Attended]<[Financial Daily].[Date Attended] AND ([First Name]=[Financial Daily].[First Name] AND [Last Name]=[Financial Daily].[Last Name]) AND Closing=true ORDER BY [Date Attended] DESC),(SELECT Min([Date Attended]) FROM [Financial Daily] as T WHERE  ([First Name]=[Financial Daily].[First Name] AND [Last Name]=[Financial Daily].[Last Name]))-1)+1 AND [Financial Daily].[Date Attended]) AS [Number Times Visited], IIf([Closing],'Yes','No') AS Closed
FROM [Financial Daily]
WHERE ((([Closing] Or [Date Attended]=(select max([Date Attended]) from [Financial Daily] as T where ([First Name]=[Financial Daily].[First Name] AND [Last Name]=[Financial Daily].[Last Name])))=True));

Except it is counting certain entries twice. I added Two entries for Jane Smith, but for some reason it is counting ALL of Her entries as well as all of John Smith. :\ ...poop.

 

[CJ_London]

If you are adding fields, I strongly recommend you need a different table structure - what if you have two John Smiths?

suggest

tblPatients
PatientPK autonumber
PatientFirstName text
PatientLastName text

tblFinancialDailies
FinancialDailyPK autonumber
PatientFK long (links to PatientPK in tblPatients)
DateAttended Date
Closing Yes/No

You may want additional fields in the patient table such as NI no or similar

The you can use the same query I originally provided but substitute PatientFK for the Name field. You would also want to link to the tblPatients to display the patient name (first, last, whatever)