473,326 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Question on aggregate statement

I have a location table that contains all North American zip codes, the
city, county, state etc.. each record has a GUID. My problem is that
several places in the US you will have a single city and zip code that
crosses a county line. So if you need to match this table to another
in a select statement using the zip and city you can pull 2 records
that are duplicated for everything except the county.

Now for my use I don't care about the county, only want 1 record but I
need one of the 2 guids that is generated by this select statement and
really don't care which one.

The only way that I can think to do this is by grouping on the zip and
city. My problem is that I need to grab one of the GUIDs and it is not
permissible to use an aggregate on one a GUID.

I really don't want to have to pull the county in to match to this
table as it is not really a relevant field for what I am doing and it
would cause several hours of additional work to the front end entry
screen. Also unfortunately the data in my table needs to remain as it
is because it is linked with sales tax rate tables.
And yes 2 people might be the same city and zip code, maybe even next
door neighbors, but being in different counties they might pay
different sales tax rates.

Any ideas?

Jul 23 '05 #1
2 1402
Why are you using GUIDs as a key? It seems like you have a flawed model
to start with if (zip,city) has meaning to you as an entity but it
isn't defined in its own table. How about creating a new table keyed on
(zip,city)?

If you intend to persist "one of the GUIDs" for a given (zip,city) but
you don't care which then you have a potential integrity problem. A
(zip,city) with an arbitrary country will be indistinguishable from a
known(zip,city,county) - a different entity.

You didn't actually specify an alternate key but assuming (zip, city,
county) is a key in your table then try this to get the GUID:

SELECT zip, city, guid
FROM YourTable AS T
WHERE county =
(SELECT MIN(county)
FROM YourTable
WHERE zip = T.zip
AND city = T.city)

This is problematic in itself. If you add a new county (don't they
change sometimes?) you may get a different GUID for the same (zip,
city). I don't think there's a reliable answer to that unless you fix
the design. Ditch the GUIDs too. I don't see any justification for them
in a address/geography table.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
Unfortunatly what I am creating some small apps around an ERP package
to push data into this ERP package. The people who designed this
package use the GUIDs significantly for as their keys and to link
different tables.
So I have specific parameters that I have to work within so that the
data that I post will interact properly with my ERP system.

I probably should add the county to the entry form and then match up
zip, city and county. However right now I just have it as a basic
entry form that posts to a table and then the SQL scripts pick it up
and move all the information around. If I were to do this and have it
work reliably I would really need to change it to a db driven form so
that they could enter the zip and pull up all relevant cities and
counties. That would be the proper way to do it and I probably will at
some point. But for right now I need a good work around that won't
really cause any business level issues. So I will give this a shot.
Thanks,
Mike

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Steven An | last post by:
Howdy, I need to write an update query with multiple aggregate functions. Here is an example: UPDATE t SET t.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ), t.b = ( select sum(f.q)...
11
by: JustSomeGuy | last post by:
I have a structure typedef struct { string a; string b; } atype; string ABC = ("123"); string DEF = ("456");
1
by: sausage31 | last post by:
I have a table as follows.... Device LotID Result1 Result2 Result3 aaa 1 5 10 15 bbb 1 2 4 6 aaa 2 ...
10
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to...
6
by: Larry Menard | last post by:
Folks, I know that DB2 does not (yet?) support this, but I wonder if anyone can suggest a work-around. I've seen article...
2
by: rdemyan via AccessMonster.com | last post by:
I can't seem to get the following SQL statement with the ORDER BY clause to work. It does work if I order by any of the fields in the SELECT part of the statement but not if it is the field in the...
1
by: Matt | last post by:
I am writing a DELETE statement and I want to filter the records using another SELECT statement. My SELECT statement is a GROUP BY query that grabs all social security numbers from the "Data...
4
by: wrldruler | last post by:
Hello, First, I know it's against "Access Law" to save calculations in a table, but....I want/need to. I currently have sub-totals being calculated inside a form, using DMax, DCount, and...
6
by: troy_lee | last post by:
I am trying to count the total number of units for a given part number that have a Priority rating of 1. Based upon some research, this is what I came up with for my query. Access says that I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.