Count records in SQL by Site

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.
LVL 6
Neadom TuckerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
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
PortletPaulfreelancerCommented:
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
mbizupCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mbizupCommented:
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
Neadom TuckerAuthor Commented:
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
Neadom TuckerAuthor Commented:
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
SStoryCommented:
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
mbizupCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
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
Neadom TuckerAuthor Commented:
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
Neadom TuckerAuthor Commented:
PERFECT!!!!! I did not know I could use a table alias!  Once I understood that it totally makes since!!! You ROCK!
0
mbizupCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.