UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Find Matching Records (two Criteria) In Same Table, Access 2010    
 
   
redpenner
post May 18 2017, 02:37 PM
Post#1



Posts: 162
Joined: 16-July 10
From: Shelton, WA


I'm trying to write a query from my table CASE_NOTES that will list any multiple records for the same CASE_NOTES.CNCustomer on the same CASE_NOTES.CN_Date - in other words, if a customer has only 1 case note on a given date, I don't want to see it, but if they have more than 1 on a given date, I do. I want to display other fields as well from CASE_NOTES in the query for each record selected, and ideally I'd like the query to be updateable.

I have a feeling I'll need a subquery and/or a second instance of the table in the design, but just haven't been able to find any SQL online that I can crib to show me how this can be done. I've seen examples from 2 different tables, but not within 1 table. Is it possible?

Go to the top of the page
 
theDBguy
post May 18 2017, 02:42 PM
Post#2


Access Wiki and Forums Moderator
Posts: 69,619
Joined: 19-June 07
From: SunnySandyEggo


Hi,

It should be possible to display duplicates from a single table but not entirely sure about updatability. I suppose it's possible too. You could try modifying the sample you saw with two tables by replacing the second table with the same table and see what happens.

PS. When using two instances of the same table in one query, it's a requirement to use an alias for, at least, one of them.

--------------------
Go to the top of the page
 
doctor9
post May 18 2017, 02:46 PM
Post#3


UtterAccess Editor
Posts: 17,076
Joined: 29-March 05
From: Wisconsin


redpenner,

> if a customer has only 1 case note on a given date, I don't want to see it, but if they have more than 1 on a given date, I do.

Try this:
SELECT *
FROM CASE_NOTES
WHERE DCount("*","CASE_NOTES","CNCustomer=" & [cncustomer] & " And [CN_Date]=#" & [CN_Date] & "#")>1;

By the way, you might want to consider removing the underscores and use CamelBackCase instead. it's still readable, and avoids using special characters that can cause problems.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
redpenner
post May 18 2017, 02:51 PM
Post#4



Posts: 162
Joined: 16-July 10
From: Shelton, WA


Thanks Dennis - I got a weird syntax error with that - Syntax error (missing operator) in query expression 'CNCustomer=Smith Bob And [CN_Date]=#7/1/2010#.
Go to the top of the page
 
doctor9
post May 18 2017, 02:55 PM
Post#5


UtterAccess Editor
Posts: 17,076
Joined: 29-March 05
From: Wisconsin


redpenner,

I assumed that CNCustomer was a number datatype. If it's text, you'll need quotation mark delimiters for the value.

Do you not have a separate table of customers? If you do, your case notes table should just have a foreign key that links to the autnumber (?) primary key of the customers table. Much better than storing the same name over and over again.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
redpenner
post May 18 2017, 03:05 PM
Post#6



Posts: 162
Joined: 16-July 10
From: Shelton, WA


Thanks again - I put some single quotes in, and got a result, but I don't think I put them in the right place since I'm seeing all "same date" notes not filtered by "same customer". Can you show me where to put the quotes exactly? (The Bob Smith note that threw the error is the first one in the table)

Yes, my database is imperfect - I do have a separate Customers table but because I'm the only data entry person and I don't like entering with forms, I have my tables joined on customer name instead of ID so I see the name on every datasheet. I have so many tables and queries (which are otherwise pretty well normalized) it would take an enormous effort to change them over.
This post has been edited by redpenner: May 18 2017, 03:06 PM
Go to the top of the page
 
doctor9
post May 18 2017, 03:20 PM
Post#7


UtterAccess Editor
Posts: 17,076
Joined: 29-March 05
From: Wisconsin


redpenner,

Try this:

WHERE DCount("*","CASE_NOTES","CNCustomer=" & chr(34) & [cncustomer] & chr(34) & " And [CN_Date]=#" & [CN_Date] & "#")>1;

Untested air code, but that's where they go.

> I don't like entering with forms, I have my tables joined on customer name instead of ID so I see the name on every datasheet

Every time you mis-spell a customer name in any way, you're going to have a problem. Forms make data entry MUCH simpler, so I'm not really clear on what you don't like about them.

> I have so many tables and queries (which are otherwise pretty well normalized) it would take an enormous effort to change them over.

Without seeing the rest of your database, I can guarantee that Normalizing your data is:

1. Not an enormous effort.
2. Will reduce the number of tables and queries
3. Is totally worth it.

What you need to do is plan what needs to be done to properly Normalize your data, and then use a combination of queries and VBA code to perform the planned steps. It's more about working smarter, not harder in this case. Our Wiki has this article that explains the basics of automating the process.

Just my two cents,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
redpenner
post May 18 2017, 03:34 PM
Post#8



Posts: 162
Joined: 16-July 10
From: Shelton, WA


Works great, Dennis, thanks!!

Misspelling names has never been a problem (I have referential integrity & unique indexing, and I'm just a really good speller)...and as I said, I'm quite well normalized other than that one quirk (okay, two quirks, hating data entry forms/preferring datasheets - I'm a "typer" not a "clicker", and have been using datasheets ever since my first database back in the 80's!).

BUT...

I'm heartened by the idea that it might not be so hard to do. So I am going to formulate a theoretical plan and see what the pros/cons are for my situation.

Thanks again for the help!
Go to the top of the page
 
John Vinson
post May 19 2017, 12:44 AM
Post#9


UtterAccess VIP
Posts: 4,081
Joined: 6-January 07
From: Parma, Idaho, US


Just a couple of points about Forms:

1. Datasheet View is perfectly acceptable for a Form, if you like that look.
2. A properly designed form can be used all day without ever touching the mouse: tab from control to control, use autoexpand on all your combo boxes, press <enter> to click command buttons, etc.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd May 2017 - 08:10 PM