Solved

Count records in SQL by Site

Posted on 2014-07-23
13
202 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
  • 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 48

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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now