Solved

Count records in SQL by Site

Posted on 2014-07-23
13
201 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Expert Comment

by:SStory
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

13 Experts available now in Live!

Get 1:1 Help Now