Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Same Product Twice < Title Case 2 | Text Box Margins >
Back to Same Product Twice    Comments List
Pinned    Upload Images   Link   Email  
Transcript
Richard Rost 
           
5 months ago
Today we're going to talk about how to detect if your customer has purchased the same product twice. Obviously, some of you want your customers to purchase the same product millions of times. But sometimes, especially if you're selling memberships, you might want to make sure they don't sign up for the same membership place. We'll talk about that in today's video.

Today's question comes from Phoebe in Roswell, Georgia, one of my platinum members. Phoebe says, "My products include memberships. How can I verify if a customer has purchased the same membership twice? Most of our purchases are unique one-offs, but sometimes a customer enrolls in the same membership more than once and then gets billed twice. I would like to be able to run a weekly query to see if any customers have duplicated their purchases. If they purchased another membership for a different person, it would go on a new customer record."

I get this all the time, even with my database. I have, obviously, my different memberships: Silver, Gold, Platinum. Sometimes, someone will come in on a Tuesday and sign up for a Silver Membership, forget about it, and then come back on Thursday and sign up for another one. I have yet to program my website to yell at people when they do this. But my database does check for it. It runs nightly and just goes through all the orders and all the memberships. If it sees any duplicates, then it at least flags me, and I can fix it before the customer gets billed another time for the second month.

So let's take a look at how we can do this. There's lots of different ways you can do this.

Before we get into it, though, this is going to be an expert-level course. What does expert mean? Well, it's my level between beginner and developer. So it's a little bit beyond the basics, but it's not quite programming. We don't need any VBA for this. You will need to know how to use aggregate queries. So if you've never used one of those before, a summary query basically, go watch this video. And this is a beginner topic. You should know how to use Access query criteria. These are both free videos. They're on my website. They're on my YouTube channel. Go watch those first and then come back.

Now I do have another video called Prevent Duplicates. This one is to prevent a user from putting the same product on the same order twice. Like you can see here, if they try to add a phaser a second time to the same order, it yells at them. I do this by creating something called a composite key. This is another method you could use. You could just set up the composite key between the customer and the product versus the order and the product, which is what we do here.

But in this video today, I'm going to show you a different method for doing it. Like I said, there's lots of different ways to do stuff. I'm just giving you different tools for your box. Right? I'm giving you several different Legos, and you can put them together however you want.

So in today's video, we'll use an aggregate query to do basically the same thing. Sometimes you want to allow the user to add a second membership. There might be an exception, for example. And if you do a composite key, you're really limited. The system will not let you add the duplicate. With this method, it just generates a report for you. You can run it daily, weekly, or monthly. And it will at least point out who's got the same service twice. You might want to put a note on the customer saying that this is okay. They got a service for themselves and their grandma. And they don't want a second customer record. I don't know. I like to leave room for exceptions, and sometimes things that you hard code into a database don't give you that opportunity.

So what are we going to do? Let's start off first by putting in some data into this database. By the way, this is the TechHelp free template. You can get a copy of this off my website if you want to. It's a free database. We've got customers, and each customer has orders, and each order has products on it.

Let's say that I ordered the phaser bank twice. Now, this is just a text field. This same thing will work fine if you're using product IDs. Whatever you want to do, I'm just going to use the text of the description. So I'm going to copy that. Let's go down here to order two, and I'm going to add a phaser bank for this one.

Okay. So now I have purchased phaser bank twice. Pretend it's a silver membership. I don't care. Let's do someone else too. Let's pick a different customer. Let's go to Jean-Luc and his orders, and he's got a bottle of wine right there. Let's create a second order for him. I'll put today's date in there. That's Control-Semicolon. It pops today's date in there. The description, if you want to. I'll put in there that bottle of wine, and he paid 120 for this one. Oops, what happened? Oh, I got my NumLock here. NumLock, 120. Okay. And of course, you can add all kinds of criteria checking to see if the order's paid, all that stuff. I'm just going to keep it simple for now.

Okay? All right. Everybody with me so far? If not, raise your hand. No. It goes back to my days of teaching in the classroom.

All right. First thing we need to do, well, actually, the second thing now, is we need to create a query to bring together the customer, the order, and the order detail. That's how we're going to get the information that we need.

So create query design. I'm going to bring in from my tables. We want the customer table. Double click. The order table and the order detail table. And you see the relationships are made for us because we have our fields named the same thing. Okay? Do you have to worry about the join type here? No, not really. This is fine because we're looking for a customer and a related detail item. And if you're missing something, that's okay. Not a big deal.

Let's bring in the customer and let's bring in the product name. So it's going to show you a list of each customer and all of the products they have. So you can have one record for each match of those two things. Right? It's going to be customer and then product name. And if you run it now, there you go. This is every product for every customer and all of the products they purchased. And if you put a sort on, let's put a sort on so we can see here. Let's sort this by ascending there and ascending there. That'll make it easier for us to read.

Okay, there we go. There's all of customer one's purchases. You can see there's two phaser banks right there. We don't care about the order ID. Yeah, you can add other kinds of criteria if you want to say, you know, within the previous year, because maybe it's a renewing membership. I don't know. You can put whatever criteria you want in here.

Okay? All right. Now, the next thing to do is we're going to group these. If you group it by customer and by product, it will group all the like items together. So instead of having two items for phaser bank, you should only see one if we add the grouping right. Right? So let's go over here and turn on totals. This creates an aggregate query. And now it's both grouped by customer ID and grouped by product name.

Okay? Run it. And now I only see one phaser bank because it's grouped together.

Okay? Now, I don't just want to see that it's grouped together. I want a count of how many of these there are. So then we're going to use the count. But where do we put the count? Well, you could put the count here or here, either one, but then you're going to lose some data. And this doesn't even, yeah, it doesn't even come out right there. So it's just all, it just scrambles everything. I still want this information. I still need the customer ID and the product name. So how do we put our count on it? Well, let's just add the customer ID or any of these fields again.

And we're going to count this guy. Okay? Count the number of times a customer ID appears for this group. And there you go. See that? You got a bunch of ones and you got two twos in there. Two phaser banks for customer one. Right? Two bottles of wine for customer four. And again, you could use product IDs or whatever other data you want in here.

Now, if you only want to see the ones where the person has purchased more than one of them, just put your criterion here greater than one. And now if I run it, boom, there you go. And maybe change it from count of customer ID. Let's add an alias to that. We can alias that here. I'll zoom in, Shift-F2, alias this. We'll call this product count or whatever.

All right? This is called an alias. I've got another video on that. If you don't know what an alias is, I'll put a link to that down below as well. It's just basically giving a calculated field a different name. So you can see it like that instead of count of customer ID, which is kind of meaningless.

Okay? And there you go. That's basically it.

All right? Save the query as, you know, duplicate product queue, whatever you want to call it. And now you just run this daily, weekly, or monthly, whenever you want to see it. And then you've got the customer ID. You know what they purchased and how many they got.

If you like this kind of stuff, if you like learning with me, come check out my website. I got lots of beginner, expert, advanced, and developer lessons. Lots of stuff. My expert levels lessons cover all the stuff that's beyond the basics. Like, you know, in the basics, we cover form design and building simple queries and stuff like that. In the expert series is when we start tearing that stuff apart and really getting under the hood. I got 32 different levels of expert series covering everything from relationships and referential integrity to action queries. You name it. It's in there. And the short of programming is covered in my expert lessons.

But there you go, folks. That's going to do it. That's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.

A special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts Software Solutions, manufacturing experts, specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com.

Another shout out to Sam Shama from Shama Consultancy. Sam is a certified Microsoft Office Specialist, and he not only offers Access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sam is your guy. Check them out at shamaconsultancy.com.


TOPICS:
Detecting duplicate product purchases
Handling duplicate memberships
Creating a summary query in Access
Using aggregate queries in Access
Adding sort criteria to queries
Grouping data in Access queries
Adding count function in queries
Creating an alias for calculated fields
Using query criteria to filter results
Running queries on a schedule

COMMERCIAL:
In today's video, I will show you how to detect if a customer has purchased the same membership twice. This tutorial is based on a question from Phoebe in Roswell, Georgia. We'll start by showing you how to use aggregate queries without any VBA to identify duplicate memberships in your database. I will guide you step-by-step through creating the necessary queries in Microsoft Access, ensuring you can flag repeated purchases. We'll also discuss how to group data, use counts, and apply criteria to generate useful reports. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Same Product Twice.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2025 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/11/2025 7:38:10 PM. PLT: 1s