JamesNT
asked on
Pulling specific rows from a database
I have a table called transactions that is as follows?
ID PatientID BillID Date TransactionID Amount
1 5 2 1/1/2011 D $50
2 5 2 2/3/2011 D $45
3 10 6 1/4/2011 D $50
4 11 8 2/1/2011 D $50
5 11 8 2/28/2011 D $45
6 24 12 1/25/2011 D $50
Notice that some patient/bills have more than one record, but not all.
I need to see the D record for each patient, if there is more than one, I need to see the one with the EARLIEST date. So the result set in this case should be:
ID PatientID BillID Date TransactionID Amount
1 5 2 1/1/2011 D $50
3 10 6 1/4/2011 D $50
4 11 8 2/1/2011 D $50
6 24 12 1/25/2011 D $50
Ideas?
JamesNT
ID PatientID BillID Date TransactionID Amount
1 5 2 1/1/2011 D $50
2 5 2 2/3/2011 D $45
3 10 6 1/4/2011 D $50
4 11 8 2/1/2011 D $50
5 11 8 2/28/2011 D $45
6 24 12 1/25/2011 D $50
Notice that some patient/bills have more than one record, but not all.
I need to see the D record for each patient, if there is more than one, I need to see the one with the EARLIEST date. So the result set in this case should be:
ID PatientID BillID Date TransactionID Amount
1 5 2 1/1/2011 D $50
3 10 6 1/4/2011 D $50
4 11 8 2/1/2011 D $50
6 24 12 1/25/2011 D $50
Ideas?
JamesNT
Sorry - this might work better:
SELECT PatientID , BillID , Min([Date]),TransactionID , Amount
FROM YourTable
WHERE TransactionID="D"
GROUP BY PatientID , BillID , TransactionID , AmountASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't think that will get the correct results.
Create a MinBillDate query like so:
Then execute the following query:
Note: Try to avoid using keywords, such as Date, as column names.
Create a MinBillDate query like so:
SELECT BillID , Min([Date]) As MinOfBillDate
FROM Transactions
GROUP BY BillIDThen execute the following query:
SELECT PatientID , BillID , [Date], TransactionID , Amount
FROM TransactionsInner Join MinBillDate On Transactions.BillID = MinBillDate.BillID
And Transactions.Date = MinBillDate.MinOfBillDateNote: Try to avoid using keywords, such as Date, as column names.
ASKER
I hate to say this as I'm sure it will frustrate some, but I need the ID column to show.
Also, I can assure you all that I do not use reserved words such as date, transaction, first, last, etc. as field names in my tables. Unfortunately, a lot of software out there does (and we are talking software that costs thousands of dollars). I'm not the one who made this thing, I'm just the one who has to deal with it. :(
Each patient can have many bills but each bill may have one and only one patient.
Let me know if your queries preserve the rows I need and I'll try them out.
Thank you all for your help - and for taking time out of your Sunday!
JamesNT
Also, I can assure you all that I do not use reserved words such as date, transaction, first, last, etc. as field names in my tables. Unfortunately, a lot of software out there does (and we are talking software that costs thousands of dollars). I'm not the one who made this thing, I'm just the one who has to deal with it. :(
Each patient can have many bills but each bill may have one and only one patient.
Let me know if your queries preserve the rows I need and I'll try them out.
Thank you all for your help - and for taking time out of your Sunday!
JamesNT
Can't you just try them out?
I can't speak for everyone (though I strongly suspect the same), but I put at least some effort into matching your object names, so they should parse and execute verbatim.
Just giving them a try to determine if they work doesn't seem too much to do on spec (even just based on the fact that the ID is shown in those queries, if not the assumption that our queries will have had the effort put in to just execute as they stand).
It's surely worth a shot and not too much trouble to go to - compared to, say, accurately writing up two (hopefully) syntactically correct queries (on a Sunday no less ;-p)
Cheers.
(P.S. Yes, they do. :-)
I can't speak for everyone (though I strongly suspect the same), but I put at least some effort into matching your object names, so they should parse and execute verbatim.
Just giving them a try to determine if they work doesn't seem too much to do on spec (even just based on the fact that the ID is shown in those queries, if not the assumption that our queries will have had the effort put in to just execute as they stand).
It's surely worth a shot and not too much trouble to go to - compared to, say, accurately writing up two (hopefully) syntactically correct queries (on a Sunday no less ;-p)
Cheers.
(P.S. Yes, they do. :-)
Here you go:
SELECT ID, PatientID , BillID , [Date], TransactionID , Amount
FROM TransactionsInner Join MinBillDate On Transactions.BillID = MinBillDate.BillID
And Transactions.Date = MinBillDate.MinOfBillDateASKER
Thank you all for your help.
JamesNT
JamesNT
Glad you got a solution that worked. :-s
Sorry for the late post here.
Why was the question closed like this?
From testing this in Access 2010, Leigh's first query in http:#a37234364 was syntactically perfect and produced the desired results -- exactly as shown in the original post, right off the bat with no modification.
The Accepted Answer doesn't work - syntactical issues. The problems with it are straight forward to fix, but it seems that the first correct answer (http:#a37234364) should have been awarded the points.
Why was the question closed like this?
From testing this in Access 2010, Leigh's first query in http:#a37234364 was syntactically perfect and produced the desired results -- exactly as shown in the original post, right off the bat with no modification.
The Accepted Answer doesn't work - syntactical issues. The problems with it are straight forward to fix, but it seems that the first correct answer (http:#a37234364) should have been awarded the points.
ASKER
mbizup,
The question was closed like this because it solved MY problem. Yes, I did have to fix a few trivial (at least to me) syntactical issues but it worked - FOR ME. And, it was the first answer I got to. I didn't try all the other answers because I just didn't have the time. I needed the report that uses this query done before close of business Monday (one of those difficult clients we all know and love), which is why I offered 500 points so people would put time in on Sunday. Therefore, spending a few hours trying out everyone's answer wasn't ever on the table.
This situation is truly a far cry from ideal. I shouldn't have clients calling me at 7pm on Friday demanding a complicated report by the following Monday thereby ruining my weekend. You guys shouldn't have to worry about posters that don't give your answers a fair shake. But then again, when we all signed up to work in IT/Software Development, we certainly knew what we were getting ourselves into. At least I hope we did.
Thank you for your time and consideration.
JamesNT
The question was closed like this because it solved MY problem. Yes, I did have to fix a few trivial (at least to me) syntactical issues but it worked - FOR ME. And, it was the first answer I got to. I didn't try all the other answers because I just didn't have the time. I needed the report that uses this query done before close of business Monday (one of those difficult clients we all know and love), which is why I offered 500 points so people would put time in on Sunday. Therefore, spending a few hours trying out everyone's answer wasn't ever on the table.
This situation is truly a far cry from ideal. I shouldn't have clients calling me at 7pm on Friday demanding a complicated report by the following Monday thereby ruining my weekend. You guys shouldn't have to worry about posters that don't give your answers a fair shake. But then again, when we all signed up to work in IT/Software Development, we certainly knew what we were getting ourselves into. At least I hope we did.
Thank you for your time and consideration.
JamesNT
LPurvis really should have received the accepted answer here - I've asked a Mod to fix it.
When I commented "I don't think that will get the correct results.", I was referring to the mbizup comments. The http:#37234364 solution comment arrived nine minutes before my comment and takes the same approach as my SQL. I broke up my solution into two queries and LPurvis included the second Select statement inside his From clause.
When I commented "I don't think that will get the correct results.", I was referring to the mbizup comments. The http:#37234364 solution comment arrived nine minutes before my comment and takes the same approach as my SQL. I broke up my solution into two queries and LPurvis included the second Select statement inside his From clause.
ASKER
If everyone is in consensus, then the decision to change points is fine by me.
We should, now that I think about it, consider the fact that others will be seeing this post six months from now and may not have anywhere near the skill we all have and may wind up being further confused.
Thank you all for clearing this up.
JamesNT
We should, now that I think about it, consider the fact that others will be seeing this post six months from now and may not have anywhere near the skill we all have and may wind up being further confused.
Thank you all for clearing this up.
JamesNT
ASKER
It's understandable that things can get hectic when you have demanding clients and stressful deadlines. If you don't have the time to test all responses to your questions before meeting a deadline, you don't have to close it immediately. A good alternative is to post a comment to that effect and then return to your question after your deadline - when you have more time and are under less pressure. That would let you close the question when you are better situated to test the comments posted.
And if that is simply not feasible for whatever reason, you can also use the Request Attention button and ask for a technical review of your question.
_aliass99 your comment was very fair and balanced, thank you for that.
I honestly wish I had thought of the two suggestions you mentioned above. I'll be certain to for future questions, rest assured.
Again, thank you all for your help. I'm glad we got this resolved to everyone's satisfaction.
I hope and pray none of you have crazy deadlines like the ones I sometimes get. It's just too easy to sometimes get "tunnel vision" and let other things slip through.
JamesNT
ASKER
Last, and certainly not least, my apologies to mbizup. mbizup did not deserve the attitude of the comment I made to her. Yes, I was under some pressure at the time, but that doesn't matter. Common Courtesy is never a waste of time.
JamesNT
JamesNT
>>Common Courtesy is never a waste of time
amen
amen
Open in new window