Newbie needing help.

[Avian Flu]

I am working on a query for a family friend, and after setting up the columns, I'm winding up with a problem.

I am trying get the query to count the total times a person has visited the office under certain values, "W", "WI", and "CO-TX" in a column called [Ser]. If it was a separate query, then it counts everything just fine. But if I add it into the Query that I'm working on, it'll only count the one entry where it's looking up only the Pat_Type that I was searching in a previous query. So for example.

My table looks something like this....
First - Last - ---Date - Ser - Pat_Type
John - Smith - 1/1/14 - W - NP
John - Smith - 1/2/14 - WI -
John - Smith - 1/3/14 - WI
John - Smith - 1/4/14 - WI
John - Smith - 1/5/14 - OC
John - Smith - 1/6/14 - CO-TX
John - Smith - 1/7/14 - W
John - Smith - 1/8/14 - WI - NPOM
John - Smith - 1/9/14 - WI


My Query is popping up with this

First - Last - ---Date - Pat_Type - Total Visited
John - Smith - 1/1/14 - NP - ------ 1
John - Smith - 1/8/14 - NPOM ---- 1

Where I'm trying to show the total amount of W, WI, and CO-TX in every entry under that person's name. So it should look like this...


First - Last - ---Date - Pat_Type - Total Visited
John - Smith - 1/1/14 - NP - ------ 8
John - Smith - 1/8/14 - NPOM ---- 8

I know the reason it's not working is that it's counting via the group by function from the other parts of the query, is there any way to do a workaround for this?

Hopefully that made some sense. I've only been working on Access for a month and I'm still very unfamiliar with the SQL side of this program.

Any help would be greatly appreciated.

 

[plog]

You're trying to generate results on two levels--individual records and the dataset as a whole. You can't do that in one swoop using just SQL. My advice is to create a subquery to get the totals and then use that subquery in another query which provides the individual results.

Normally, I'd provide some code to get started, but your sample data is less than ideal. In you're sample data you've uniquely identified patients using their names, hopefully that's not how its done in your actual table. Instead of using [First] and [Last] to identify patients you should have an autonumber primary key for this. Once we have that Totals subquery, we will need to link it back to the table you posted. This is best done with an autonumber primary key.

So, either tell me you uniquely identify patients with their first/last names, in which case I will yell at you, or post a better set of sample data. In that sample data, post actual table and field names and provide the unique identifer you use for each patient.

 

[Avian Flu]

=_=' It uniquely identifies patients with their first/last names. You can yell at me. I know this is really bad, but this was the only information given to me when we first started, even when I mentioned they needed their Patient IDs, and I then it would involve me having to go into 500+ excel sheets to write in all their proper IDs from their patient database. So the first 12,000+ entries are this way (and dont get me started on the ridiculous amount of typos that are in here.). I do have a [Pat_ID] set up now after multiple attempts to talk to them, but it is blank because of what I said above...blagh.

 

[Avian Flu]

I think I might've actually figured it out.

I used a working query that counted the patients correctly and added the table it to the query I'm working on. Then I linked the first name and last name together. o__o

I think it works...*goes to check*

 

[plog]

Nope, not a valid excuse. Not having an autonumber primary key is your fault, not theirs. They may have a PatID field that they didn't provide to you, but that doesn't mean you should use names as a unique identifier.

The good news is, this can be fixed immediately and easily. You should have a Patients table. This table should have every unique patient, each patient in that table should have an autonumber primary key field for their id.

You may have overcome this issue, but you are going to have more. And an improper structure is going to cause you headaches down the line. I'd focus on structuring your data properly before moving on.

 

[Avian Flu]

Wait. How do I do that? If I have an autonumber primary key, wont it give keys to every single patient name? How do I get it so that it doesn't count the duplicates?

 

[plog]

You need a table that has the unique patients. In that table you assign an autonumber primary key. The table you initially posted about is a visit/service table, that would not be the place to set up unique patients.