Solved

Count records in SQL by Site

Posted on 2014-07-23
13
214 Views
Last Modified: 2014-07-23
I am working in ACCESS but I need help with a SQL Command.  I have a one to many query.

I have the following data

CustID   SiteID   Address          Key
1               33      12 Doll Rd        0
1               22      14 Cow Ct        -1
1               79      24 ABC Dr         0
2                2       19 Red Rd        -1

What I need to do is count in ascending order the SiteID from 1 to however many sites there.  So in my example I would need it to look like this.

CustID   SiteID   Address          Key   Loc#
1               33      12 Doll Rd        0        1
1               22      14 Cow Ct        -1       2
1               79      24 ABC Dr         0       3
2                2       19 Red Rd        -1       1
2                4         2 Gray Rd       0        2
33             99       3 Job Ct            -1      1

Each customer may have a number of different locations.  I have no idea if I can do this in a query or if I need to run an update query to do this.  Your help would be much appreciated.
0
Comment
Question by:Neadom Tucker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 40214111
Something like this
select SiteID,Count(SiteID) as SiteCount
from yourtablename
group by SiteID
order by SiteID

Open in new window


Should be a start
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40214128
I can't help on the Access SQL.

But if it was MS SQL Server I would use row_number()

select
     *
    , row_number() over(partition by CustID order by SiteID) as SiteCount
from yourtable
0
 
LVL 61

Expert Comment

by:mbizup
ID: 40214322
If you're trying to count the locations per customer you'll have to organize/sort your data by customer and site id.

Try this  -- the order by sorts the data, and the subquery 'counts' the sites, starting a fresh count for each customer:

SELECT t1.CustID, t1.SiteID, t1.Address, t1.Key, (SELECT COUNT (t2.SiteID) FROM  YourTable t2 WHERE t2.CustID = t1.CustID AND t2.SiteID <= t1.SiteID) AS [Loc] 
FROM YourTable t1
ORDER BY t1.CustID, t1.SiteID

Open in new window


(You'll have to replace both instances of "YourTable" with your actual table name)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 61

Expert Comment

by:mbizup
ID: 40214372
As an aside - counting/ordering by siteID  might not be necessary.  It IS necessary based on the limited fields you have shown here.

What is critical is that you have *some field*,  whether it is SiteID, an autonumber or a date created field which can be used to clearly and consistently identify an order from which you can generate the 'Location Number'.
0
 
LVL 6

Author Comment

by:Neadom Tucker
ID: 40215089
MBizup....
So I am not sure where the second table comes in.  

I have three tables total.
tblCustomer
tblServiceSite
tblCustomerSites

The tblCustomerSites is what ties my customers to their location.

My initial thought was to use the tblCustomerSites to count the number of locations.  Just not sure how to do that in your query.  In my tblServiceSite I do not store the CustomerID.  I know I can make this happen via a simple query.  Just not sure what the point is if I have it already in the tblCustomerSites table.

Thanks for clarifying!
0
 
LVL 6

Author Comment

by:Neadom Tucker
ID: 40215102
SSTory

I tried the following:
SELECT tblCustomerSites.CustomerID, tblCustomerSites.SiteID, Count([SiteID]) AS LocNum
FROM tblCustomerSites
GROUP BY tblCustomerSites.CustomerID, tblCustomerSites.SiteID
ORDER BY tblCustomerSites.CustomerID, tblCustomerSites.SiteID;

Open in new window


But I ended up with the following:

CustomerID     SiteID   LocNum
1                         3           1
1                         6           1
1                         7           1
1                         8           1
10                       24         1
10                       30         1
10                       38         1

Not quite what I was looking for.
0
 
LVL 25

Expert Comment

by:SStory
ID: 40215133
Your example output doesn't look like a count to me. If you do this:
SELECT  tblCustomerSites.SiteID, Count([SiteID]) AS LocNum
FROM tblCustomerSites
GROUP BY tblCustomerSites.SiteID
ORDER BY tblCustomerSites.SiteID;

Open in new window


Does it have what you want except missing some fields?
Having customer ID which will differ keeps it from doing counts per siteid.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 40215173
There is actually only one table in the query I posted.  It is joined on itself to get the Location number.  The t1 and t2 are 'aliases' for the same table, one for each time it is used.

Try this (I'm assuming the field names (custID, SiteID,Address and Key)  are correct as specified in tblCustomerSites:

SELECT t1.CustID, t1.SiteID, t1.Address, t1.Key, (SELECT COUNT(t2.SiteID) FROM  tblCustomerSites t2 WHERE t2.CustID = t1.CustID AND t2.SiteID <= t1.SiteID) AS [Loc] 
FROM tblCustomerSites t1
ORDER BY t1.CustID, t1.SiteID

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 40215205
Okay - the query I posted seems to give the results you are describing (a site number increasing per location, with a set per customer)Location number
Try this sample, and compare to your database.  You may need to modify field names.  The field names in your original question are inconsistent with your last post (eg: is it CustID or CustomerID?)

If you need additional information in this query, you can join it to other tables as needed.
TestQuery.accdb
0
 
LVL 6

Author Comment

by:Neadom Tucker
ID: 40215207
SStory,

Ok so I did what you said in my first query and I got the following:
Location Count Query
I then took the following Query to show me the customer ID to the SiteID to the Location Number:
SELECT tblCustomerSites.CustomerID, _qrySiteLocationNumber.LocNum
FROM _qrySiteLocationNumber INNER JOIN tblCustomerSites ON _qrySiteLocationNumber.SiteID = tblCustomerSites.SiteID
ORDER BY tblCustomerSites.CustomerID;

Open in new window


And I get the following:
Customers with Location Counts
Not quote what I was looking for.  I am typically good with this stuff but I have never had to do something like this before.
0
 
LVL 6

Author Closing Comment

by:Neadom Tucker
ID: 40215230
PERFECT!!!!! I did not know I could use a table alias!  Once I understood that it totally makes since!!! You ROCK!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 40215247
Glad to help out :)

Aliases are very cool things... you can use them for table or field names, or even naming subqueries.   Aesthetically, they help make your queries more readable.  Beyond that, they are very useful when it comes to SQL gymnastics.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question