Newbie doing Queries - Help would be appreciated

[Avian Flu]

Yeah. I was thinking the same thing with two john smiths.

Fortunately, there hasn't yet been two people with the same name (oh thank the heavens). And I think they'll be putting a note on that in the future when they run across it..

Unfortunately, they don't wish to use an ID number for the patients at this time. The database they're having me make already has a ton of people, and although they DO have a unique ID in their system, they didn't write it in for each entry in each of the Excel Data files.

So long story short, I'd have to go in and write all their numbers. :/ I'll probably have to do it eventually though, which will take some time, but they want this table ASAP, so I dont know if they'd be willing to wait.

So.... would it be possible to use the code with First Name and Last Name in the fields?

I've done this to it....

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));

And I have added in the table...

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 -
Jane Smith - 1/14/14 -
Jane Smith - 2/24/14 -
John Carpenter - 5/1/14 - yes

What it's giving me is strange though. I'm getting this weird result where instead of it listing Jane smith with just 2 visits, it's giving me...

Name_____Number Times Visited___Closed
John Smith - 4 - yes
John Smith - 4 - yes
John Smith - 2 - no
Jane Smith - 9 - no
John Carpenter - 1 - yes

Why is it counting all the Smiths? Can I get it to recognize that a first name+last name is one entry instead of what it's doing above?...

They've also wanted me to have it look up another category on the side with this query. *head hits desk* I'm hoping that this formula with the other one I'm looking up will work together.

 

[CJ_London]

although they DO have a unique ID in their system, they didn't write it in for each entry in each of the Excel Data files.

So not very unique then!

Had a look and you need to revise your number times code to

Number Times Visited: (SELECT COUNT(*) FROM [Financial Daily] AS T WHERE [COLOR="Red"][first name]=[financial daily].[first name] and [last name]=[financial daily].[last name] and [/COLOR][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])

 

[Avian Flu]

So not very unique then!

Kinda, I guess? I took a look at their current program and it looks pretty unique.

The main problem is, no one bothered writing it into the (one and a half year's worth of) Excel sheets they've been using (heck, I already have to go into each one of them and change the fields so they're uniform and Access doesn't throw a fit from some weird symbol). And I don't know if there is a way to import Patient ID codes into Access's database from the current thing they're using. By the way our family friend was explaining it, we'd have to go into each individual patient and input the ID or something like that, and with my subpar Access skills, I don't know any shortcuts around this can of worms. D:



Also....THANK YOU AGAIN. IT WORKS!

HOLY COWS IN RAINBOW PASTURES, YOU ARE AWESOME.

And now onto making another category to count specific words in a field for these people. =___='' Thanks so much!!!!

 

[CJ_London]

you could create your own unique id.

1. Create a patient table as already suggested.
2. import the patient firstname, lastname to the db (use select distinct so you only get one line for each patient)
3. the autonumber will create the unique ID for you for the patientPK field

Then when importing the data to the table as already suggested
1. import the data 'as is' to a temporary table
2. create an append query to populate your financial daily table, by inner joining the temporary table to the patient table on firstname and lastname and using the patientPK in the patient table to populate the patientFK field in the financial daily table and the other fields populated from the temporary table.

A bit more work, but at least you are building a 'proper' db

 

[Avian Flu]

That last half of those instructions kind of flew over my head. o_o'

Thanks for the suggestion, (and pardon the late reply, I actually thought I had already posted this but I guess I forgot to hit the button at the bottom.) I'll probably try this on my spare time when I get the current database I'm working on up and running. For now, my friend wants this ASAP, and he keeps piling additional things into the query as soon as I get one looking decent.

For example, the query that I've been having issues with here, he wants me to use an additional field to sort them out some more. (There's a [Ser] Field where I need to only count patients that have W, WI, or CO-TX in that field. :\ I wished they told me this sooner.) Unfortunately it's doing weird things so I'm trying to figure out what I'm doing wrong.