Simple Access counting queries

Hi, hoping someone can help a relative newbie with a pretty simple
query. My database (Access 2007) has three tables:

Customers
Products
Purchases (many-to-one links to both of the other tables, this is
basically a linking table)

I have two simple queries I'd like to get out of this database, but
I'm a bit stuck on how to construct the SQL. Any direction you can
give me would be helpful.

1. List of all customers who have purchased 2 or more products (or 3
or more products, or 4+, etc.)

2. List of all customers who have purchased both Product A and Product
B (or A, B, and C, or B and C, etc).

0
damon
8/23/2007 3:22:25 PM
access 16762 articles. 3 followers. Follow

2 Replies
635 Views

Similar Articles

[PageSpeed] 2

Forget the my first attempt. Found some typos:

SELECT Customers.CustID, Customers.Names,
  Count(Purchases.ProdID) AS NumberPurchases
FROM Customers INNER JOIN Purchases
  ON Customers.CustID = Purchases.CustID
GROUP BY Customers.CustID
HAVING Count(Purchases.ProdID >1) ;

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jerry Whittle" wrote:

> First one. You'll need to put in the proper field names.
> 
> SELECT Customers.CustID,
>   Count(Purchases. ProdID) AS NumberPurchases
> FROM Customers INNER JOIN Purchases
>   ON Customers.CustID = Purchases.CustID
> GROUP BY Customers.CustID
> HAVING Count(Purchases. ProdID >1 ;
> 
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "damon.blackmore@gmail.com" wrote:
> 
> > Hi, hoping someone can help a relative newbie with a pretty simple
> > query. My database (Access 2007) has three tables:
> > 
> > Customers
> > Products
> > Purchases (many-to-one links to both of the other tables, this is
> > basically a linking table)
> > 
> > I have two simple queries I'd like to get out of this database, but
> > I'm a bit stuck on how to construct the SQL. Any direction you can
> > give me would be helpful.
> > 
> > 1. List of all customers who have purchased 2 or more products (or 3
> > or more products, or 4+, etc.)
> > 
> > 2. List of all customers who have purchased both Product A and Product
> > B (or A, B, and C, or B and C, etc).
> > 
> > 
0
Utf
8/23/2007 3:50:04 PM
The second one. Not so simple at all. Not sure that it will work:

SELECT Customers.CustID, Customer.Names,
  Count(P1.ProdID) AS NumberPurchases
FROM Customers INNER JOIN 
(SELECT DISTINCT Purchases.CustID, Purchases.ProdID
FROM Purchases) as P1
  ON Customers.CustID = P1.CustID
GROUP BY Customers.CustID
HAVING Count(P1.ProdID >1) ;

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"damon.blackmore@gmail.com" wrote:

> Hi, hoping someone can help a relative newbie with a pretty simple
> query. My database (Access 2007) has three tables:
> 
> Customers
> Products
> Purchases (many-to-one links to both of the other tables, this is
> basically a linking table)
> 
> I have two simple queries I'd like to get out of this database, but
> I'm a bit stuck on how to construct the SQL. Any direction you can
> give me would be helpful.
> 
> 1. List of all customers who have purchased 2 or more products (or 3
> or more products, or 4+, etc.)
> 
> 2. List of all customers who have purchased both Product A and Product
> B (or A, B, and C, or B and C, etc).
> 
> 
0
Utf
8/23/2007 3:58:02 PM
Reply:

Similar Artilces:

Web Service Query Question
I have 3 conditions in my webservice that i use to retrieve contact records from crm. The first two conditions ( Firstaname and Lastname) are mandatory and the last one (date of birth) is not. I want to exclude the last condition if it is empty. Here is my code which doesnt work. At the end i have an if statement which I thought it will do what i want. Please help // Return distinct values query.Distinct = true; // Setup the query for the contact entity query.EntityName = "contact"; // Specify th...

Public Folder access #2
Has anyone run into the following??? I have set up a public folder and granted a group Publishing Author access. UserA and UserB are members of that group. In Outlook 2000, UserA creates a subfolder under the public folder and posts an entry into the subfolder. UserB accesses that post and the header on the window indicates he has read-only access. So far so good. UserB can choose Edit-Revise Contents, make any change to the content of the post and then enter it as a new post. Again, no big deal. HOWEVER, the new post indicates that the author is UserA rather than UserB submi...

MS Access and Outlook
I have a macro created in Acess that send 200 personal e- mails. It worked OK in the previous Outlook version but it doesn't work in Outlook 2002. I receive e warning message that other program try to send e-mail on my behalf and I have tyo click yes(200 times). How can I remove this security feature? Thank you You can't directly. The eassies way out of here is by using a click-yes tool http://www.express-soft.com/mailmate/clickyes.html -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tip ...

Accessing OAB from domain other than the domain where Exchange 2003 lives
I have a trusted domain from a company that we purchased. It is a Windows NT domain, but we have created users on our 2003 AD/Exchange domain for all of the NT domain users. After establishing the trust between the domains, I granted individual mailbox rights from each users old NT domain login to their new domain/Exchange mailbox so they would not have to enter their newer login each time they opened Outlook (also version 2003). These users are across a WAN connection from the Exchange server, so we set them up in cached Exchange mode. If Outlook is configured to use the NT domain login, n...

Make Access 2003 forget selection checkboxes upon closing a docume
We're using Access 2003 professionally and we have databases with checkboxes in them, through which we export selected fields to Excel. However, for some reason, Acess stores the names of the boxes you check and keeps them there forever unless you uncheck them again manually. Naturally, this is not very practical when it comes to unchecking say 1000 checkboxes. How can I make Access to stop remembering which checkboxes were checked the last time we opened the Access document/database? Not sure I understand. You have a checkbox (Male or Female, charter member, member of management...

Discrepancy encountered when attempted to convert a MS Excel list to MS Access
When I attempted to convert a excel list to access database using Excel's menu [Data, Convert to MS Access], I received the following message. Has anyone seen this message before? If so, how do you overcome the discrepancy? I found something similar within Microsoft knowledge base, but it reference importing from Access. Thank you very much in advance. Error message: The Microsoft Jet database engine could not find the object '<name of selected range>'. Make sure the object exists and that you spell its name and the path name correctly. ...

Count Function #5
I have a list of 6,000 records. what I'd like to do is count the number of Contractors in the list. Thr problem is some contractors have more than 1 record and their contractor number shows up as if they are duplicates. I only want it to be counted once even if they have more than one record. Here's a sample of the file: Contractor Truck Owner No. of Units 017324029 542453 EDWARD F BRADY 1 017526830 410486 DIONNE, HEATHER 1 018367773 530126 CLIFFORD DAVIS 3 018367773 530128 CLIFFORD DAVIS 3 018367773 531679 CLIFFORD DAVIS 3 0184...

how do you count cells with a time criteria??
I need to get a count of cells in a column based off of a time criteria. The colum is a in "TIME" format ( 8:00 AM, 12:00 PM, etc). I need to get a count of cells that are between 8:00 AM and 12 PM . . what formula would I use?? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi ..., =countif(A2:A20,">=08:00")-countif(A2:A20,">12:00") if you want it under row 20 you can use A21: =countif(A$2:OFFSET(A21,-1,0),">=08:0...

Sort by: Site ID/Item Number/Bin for Stock Count Entry Screen
Would like an additional sort selection in the Stock Count Entry Screen. Sort by: Site ID/Item Number/Bin ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=7e69d84a-032e-4f61...

change report/query into a table
Is there a way i can change a report/ query into a saved table format .so i can use it for email replies?? No, but you can print it as a Snapshot file or a PDF. You can save the report's recordsource as a temporary table, as well, or just copy and past that query into an email. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "Alan" <Alan@discussions.microsoft.com> wrote in message news:13E09077-9090-4495-8ABC-9D437F1B5597@microsoft.com... > Is there a way i can change a report/ query into a saved...

Adding texture to Access forms
How do you add texture to the background of Microsoft Access forms? Arturo wrote: > How do you add texture to the background of Microsoft Access forms? With the Picture property of the form. Set it to an image file that gives the "texture" you want. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

Quick Access Toolbar in Office 2007
Basicly, I've read every single forum and tech support for Office 2007 programs freezing, locking and crashing when doing a large number of random tasks, which responses by onlookers seem to have the solving answer for, but I have yet to find my specific problem. When I use the Quick Acces Toolbar in either Word or Excel or any of my suites' programs, it locks and I have to force it to close. I can customize the toolbar with the down arrow on the right all day long with no problems, but as soon as I click save, open, save as, or any of the various essential commands I get...

Outlook access through a firewall
Greetings, We're currently trying to get Outlook working through a firewall, and connected to Exchange 2000. I've read from multiple sources that this requires locking down the high-numbered ports that the RPC Endpoint Mapper hands out (via a registry edit), then opening up TCP port 135 and the high-numbered ports in the firewall. We've done this, and for some reason it seems to not be working. We set up an access rule and a translation rule in our PIX, identical to the one that allows HTTP traffic to the Exchange server for OWA (except for port 135 instead of 80). However, wh...

Access and Team Foundation Server
I have an Access application (adp) that is under source control at work using TFS (Team Foundation Server). When I check-out the application I can use it in any way I want (to modify it). However, I need to bring the .adp file at home to study the code on my free time. I managed to duplicate the SQL Server database it's connected to. But when I try opening the adp file at home, I get the "lock" icon next to all my access objects (forms, modules, etc). I'm no expert in TFS but we use it at work to manage our Access applications with the rest of the team. How can...

Help with a sales process hold query
I'm trying to construct a query to show all sales orders that do not have a process hold assigned. I'm using info from SOP10100 and SOP10104. The problem I'm having is that each order could have several different types of holds assigned over time. Each of those appears as a different row in SOP10104 and each hold has a 0 in the DELETE1 field if it is still active, or a 1 in that field if it has been removed. How can I filter out orders that have any active hold assigned to them? Thanks in advance. -- Jim@TurboChef You can do this more efficiently but this is one way to...

Exchange Access to Remote Office
I have a remote office that is connected through a VPN on a somewhat unreliable internet service. We have no other options for connectivity this point. We have Exchange 2003 with the clients running Outlook 2003 on a terminal server at their location. We were excited to hear about Outlook's cached Exchange mode because it would help the users retain access to their existing email should the VPN go down. We soon realized that Cached Exchange Mode is not supported on Terminal Services unfortunately. What I want to know is it at all possible to work around this or configure Exchange differe...

print a list of reports in access
Does anyone know of a way to print a listing of all your reports in the Reports Object section. just go tools->analyse->documenter You can print out all information about the reports. Play with the "options" button, as these documenter printouts tend to be very large.... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com >> Thank you. I tried that but it gives all the detail. I was just >> interested in the names, but I will take your suggestion and play with >> the options button. Thanks again. ah...ok.....

Access 2000 file now opens up as Access 97
No one at my companies help desk can help me with this, so I thought I'd go to the experts =) I have an Access 2000 file that I've been using Access 2003 to open. I have a laptop and a desktop at my station. When I opened up the database on my laptop it was fine. When I opened it up on my desktop, it shows as a Access 97 file, and now all my make table queries are gone. I called our Help Desk and after the knucklehead touched my laptop, I can't open it up on either system in the Access 2000 file. IS did a file restore and it's the same thing. Anyone have any idea what happened...

Exchange 2003 Inbox Access Permissions
I was wondering if you might find you way to pointing in the proper direction with an issue I am having. Recently I have noticed that my users seem to have been assigned too many access rights as now other users are able to open other users Inboxes, Contact Lists, Calanders etc. even though in Outlook they did not have Permission to do so. It is as if ALL User' have Administrator access. So I am thinking that there is a setting in Exchange (Mailbox Rights) that controls this access. I could use Mailbox Rights in AD Users and Computers (though I am not sure which Right to edit) in e...

Access 2000 not responding
Can anyone help with this puzzling behaviour from Access? I can open Access databases and see all objects but none will open when I double click them - there is no error message. After that when I close the database the application window stays open and won't close when I select Exit. I can only close the application window using Task Manager and stopping the process. Not sure whether this coincided with something else I did or installed. Any ideas? Sounds like you may have gotten rid of your warnings. Did you ever use DoCmd.SetWarnings False in any of your code. If so, you may ...

Starting Visual Basic Editor crashes Outlook and cannot send email from Access
MS Outlook 2003 (11.8206.8221) SP3 MS Access 2003 (11.6566.8221) SP2 MS Access 97 SR-2 MS Visual Basic 6.0 Version 8176 VBA Retail 6.0.8169 Forms3: 12.0.4518.101 Windows XP 5.1.2600 SP2 Build 2600 Machine: Dell Precision T7400 Processor: X86 Family 6 Model 23 Stepping G Intel 2.6 Mhz BIOS: Dell A03, 5/30/2008 SMBIOS: 2.5 Current system oddity: Documents and Settings is on the D: drive instead of the C: drive (don't ask me why, I just work here...) Hi there, I recently migrated from a PC with XP SP2 to a workstation which runs XP (32 bit). Since then I have been having a couple of problem...

SBS 2008 remote access problem with Win 7
Hello, I have installed a SBS 2008 server with remote access. I opened ports 1723 and 47 on the router/modem and forwarded them to the server IP address. Now using the default Windows VPN client I can VPN into the server with Windows XP but can NOT with Windows 7. With Windows 7 I get an “Error 800: The remote connection was not made because the VPN tunnels failed.” If someone knows how to resolve the Windows 7 problem I would be most appreciative. Also what ports should be opened for a L2TP connection. Thank you so much for your help. See other post -- Russell Gro...

restricting access to documents
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) How do I restrict access to excel documents? password On 7/12/09 2:27 AM, in article 59bae46f.-1@webcrossing.JaKIaxP2ac0, "ctcuster@officeformac.com" <ctcuster@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) How do I restrict > access to excel documents? -- The email below is my business email -- Please do not email me about forum matters unless I ask you to; or unless you intend to pay! John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Wri...

"Count" expression in report
Hi I have a field, named [Type]. In a report, what is the expression to count this field “[Type]” where Type=Truck ??? =Count([Type]) where type = “Truck” Thanks On Wed, 27 Feb 2008 09:31:04 -0800, Trying wrote: > Hi > I have a field, named [Type]. > In a report, what is the expression to count this field �[Type]� where > Type=Truck > ??? =Count([Type]) where type = �Truck� > Thanks I would strongly suggest that you not use the word 'Type' as a field name. Type is a reserved Access keyword and refers to an Access Field property. For more information see: htt...

How to add same names in the numeral count in the next sheet
How to add same names in the numeral count in the next sheet,find the below i have copied.. Sheet 1 S \ No Facility Name Vendor Name 1 CDC - 1 SLS 2 CDC - 1 SLS 3 CDC - 1 SLS 4 CDC - 1 SLS 5 CDC - 1 SLS 6 CDC - 1 SLS 7 CDC - 1 SLS 8 CDC - 1 SLS 9 CDC - 1 SLS 10 CDC - 1 SLS 11 CDC - 1 MM 12 CDC - 1 MM 13 CDC - 1 MM 14 CDC - 1 MM 15 CDC - 1 MM 16 CDC - 1 MM 17 CDC - 1 MM 18 CDC - 1 MM 19 CDC - 1 MM 20 CDC - 1 MM 21 CDC - 1 MM 22 CDC - 1 MM Sheet2 Date 1-Dec 2-Dec SLS 6 4 MM 14 13 NPT 19 18 IPT 9 10 SSRM 19 21 SRS 17 17 Cabs Operated 84 83 ...