Overflow Error Access Union Query
UNION query that has started to generate an "Overflow" error message when I execute it. The SQL is "select * from query1 union select * from query2". Both "query1" and "query2" execute fine when I try them separately - I only get the "Overflow" in the union query. Both "query1" and "query2" have identical fields (20 of them) in identical formats - the data is just sourced from two distinct tables. Only "query1" has a calculation to add two numbers together, so I can't see anything likely to cause a "divide by zero" error. What is particularly puzzling is that this union query has been used (unchanged) on a weekly basis for the last two years without error...! Anyone have any ideas? Thanks, Mark -- Mark Wickett - Adelaide Bank - Australia Mark Wickett, Jan 2, 2008 #1 Advertisements John Spencer Guest IF you are adding two integer numbers together and they exceed the maximum value of an integer field (32K) then you will get an overflow error. What are the field types involved? '==================================================== John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '==================================================== Mark Wickett wrote: > Hi, > > I have a UNION query that has started to generate an "Overflow" error > message when I execute it. > > The SQL is "select * from query1 union select * from query2". > > Both "query1" and "query2" execute fine when I try them separately - I only > get the "Overflow" in the union query. Both "query1" and "query2" have > identical fields (20 of them) in identical formats - the data is just sourced > from two distinct tables. Only "query1" has a calculation to add two numbers > together, so I can't see anything likely to cause a "divide by zero" error. > > What is particularly puzzling is that this union query has been used > (unchanged) on a weekly basis for the last two years without error...! > > Anyone have any ideas? > > Thanks, > Mark > > -- > Mark Wickett - Adelaide Bank - Australia John Spencer, Jan 3, 2008 #2 Advertisements Mark Wickett Guest Hi John, Field types are both doubles - and actually, the nature of the source data is that one field is always negative (or zero), so the addition could only ever reduce the size of
FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: http://www.pcreview.co.uk/threads/overflow-message-in-union-query.3371640/ Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. Microsoft: Access Queries and JET SQL Forum at Tek-Tips HomeForumsProgrammersDBMS PackagesMicrosoft: Access Queries and JET SQL Forum Overflow Error on Union Query http://www.tek-tips.com/viewthread.cfm?qid=1281075 thread701-1281075 Forum Search FAQs Links MVPs Overflow Error on Union Query Overflow Error on Union Query AlexCuse (Programmer) (OP) 21 Sep 06 10:54 Hi All,I have a four piece union query that is giving me overflow errors.The query is designed to return a result like this:Product 1Actual2006Qtr1Planned2006Qtr1Actual2006Qtr2Planned2006Qtr2Actual2006TotalPlanned2006TotalProduct 2Actual2006Qtr1Planned2006Qtr1....With actual data off to the right.Before I added totals this query was working fine, and I have tested and re-tested to find that it is the last two queries in the union.Both run correctly on their own, but don't seem to work together.Am I missing something obvious here?Here's the Query (last two pieces).And YrQtrMedia.Quarter is referenced in the first query:CODESELECT "ACTUAL" as [TYPE] ,"zATOTAL" as [QUARTER], YrQtrMedia.YEAR,YrQtrMedia.MEDIA, Sum([SOURCE].[NET_QUANTITY]), "xxx" as [Gross Leads],"xxx" as [Gross Response %],Sum([qryCountKeycodesLEADS].[CountOfKEYCODE]) AS [Net Leads], Sum([qryCountKeycodesLEADS].[CountOfKeycode])/Sum([source].[net_quantity]) AS [Net Response %], Sum([qryCountKeycodesLEADS].[SumOfAPPS]) AS [Sales], IIf(Sum([qryCountKeycodesLeads].[SumOfAPPS])=0,0,Sum([qryCountKeycodesLEADS].[SumOfApps])/Sum([qryCountKeycodesLEADS].[CountOfKeycode])) AS [Conversion %], "xxx" as [Creative Cost],Sum([SOURCE].[MEDIA_COSTS]) AS [Media Cost], Sum([SOURCE].[PROD_COSTS]) AS [Print/Prod/Ltrshp Cost],"xxx" as [Postage Cost],Sum([SOURCE].[MEDIA_COSTS]) + Sum(SOURCE.PROD_COSTS) as [Total Marketing Cost]FROM (YrQtrMedia LEFT JOIN SOURCE on YrQtrMedia.QUARTER = SOURCE.QUARTER AND YrQtrM
Popular Forums Computer Help Computer Newbies Laptops Phones TVs & Home Theaters Networking & Wireless Windows 7 Windows https://www.cnet.com/forums/discussions/microsoft-access-union-query-overflow-message-143481/ 10 Cameras All Forums News Top Categories Apple Computers Crave Deals Google Internet Microsoft Mobile Photography Security Sci-Tech Tech Culture Tech Industry Photo Galleries Video Forums Video Top Categories Apple Byte Carfection CNET Top 5 CNET Update Googlicious How To Netpicks Next Big Thing News On Cars Phones Prizefight Tablets overflow error Tomorrow Daily CNET Podcasts How To Top Categories Appliances Computers Gaming Home Entertainment Internet Mobile Apps Phones Photography Security Smart Home Tablets Wearable Tech Forums Speed Test Smart Home Top Categories Tour CNET Smart Home Smart Home News Smart Home How To Best Smart Home Devices Cars Top Categories Car Reviews overflow error access Best Cars New Cars Used Cars Deals Top Categories Cheapskate Best Tech Under $50 All Deals Tech Deals Non-Tech Deals Audio Deals Cell Phone Deals Desktop Deals Laptop Deals Hard Drive & Storage Deals Printer Deals Tablet Deals Camera Deals Monitor Deals Software Deals TV Deals Web Hosting VPN Services WordPress Hosting Domain Names Download Sign In / Join Sign In to CNET Join CNET Member Benefits My Profile Forums Sign Out US Other editions United Kingdom Australia China France Germany Japan Korea CNET en EspaƱol CNET Forums Office & Productivity Software microsoft access union query overflow message Office & Productivity Software forum About This ForumCNET's Forum on office & productivity software is the best source for finding help, troubleshooting, or tips from a community of experts. Exchange knowledge, and get help on all the latest office software and productivity tools--from various office suitesword processing to spreadsheets.Real-Time ActivityMy Tracked DiscussionsFAQsPoliciesMod