Help with union query within Access 2007 that involves a summary.

Can you please provide some sample data (in both the tables and your desired output?

Thanks.

THe problem is your grouping.  If your grouping was the same in both queries then they would produce the exact same number of results.  However, you change the last grouping field which, apparently, results in more rows and throws off your count.  

rfportilla,

There's an extra join in there not included in the other because that's where the data is located which is in a completely separate table.  I already understand my problem so now how do I show data in another table without duplicating the rows?

Have you tried joining the tblReqs-code table after you've created your query 1 reccordset.  Try something like the below:


Select DISTINCTROW T1.*,
      T2.REQNUM

From  (SELECT DISTINCTROW       qTotals.AltSortOrder,
                  qTotals.Equip,
                  qTotals.TypeSortID,
                  qTotals.Type,
                  qTotals.Name,
                  qTotals.Furnish,
                  qTotals.Install,
                  Sum(qTotals.Ex_Quantity) AS SumOfEX_QUANTITY,
                  Sum(qTotals.newqty) AS SumOfnewqty,
                  Sum(qTotals.totqty) AS SumOftotqty,
                  qTotals.Unitcost,
                  Sum(qTotals.TotCOST) AS SumOfTotCOST,
                  qTotals.TypeName,
                  qTotals.Manuf,
                  qTotals.Model_no,
                  qTotals.BidPack,
                  qTotals.Alternate,
                  qTotals.ASE,                   
                  qTotals.UNM,
                  qTotals.[Project Information]
FROM qTotals
GROUP BY qTotals.AltSortOrder, qTotals.Equip, qTotals.TypeSortID, qTotals.Type, qTotals.Name, qTotals.Furnish, qTotals.Install, qTotals.Unitcost, qTotals.TypeName, qTotals.Manuf, qTotals.Model_no, qTotals.BidPack, qTotals.Alternate, qTotals.ASE, qTotals.UNM, qTotals.[Project Information]
) T1 Left Join (Select * from tblREQS-Codes) T2 on T1.Equip = T2.projmeid
ORDER BY T1.AltSortOrder, T1.Equip, T1.TypeSortID;

I re-did the format so that this would go in Access SQL and did the following:

SELECT DISTINCTROW qTotals.*, [tblREQs-Codes].REQNUM
FROM  (SELECT DISTINCTROW  qTotals.AltSortOrder, qTotals.Equip, qTotals.TypeSortID, qTotals.Type, qTotals.Name, qTotals.Furnish, qTotals.Install, Sum(qTotals.Ex_Quantity) AS SumOfEX_QUANTITY, Sum(qTotals.newqty) AS SumOfnewqty, Sum(qTotals.totqty) AS SumOftotqty, qTotals.Unitcost, Sum(qTotals.TotCOST) AS SumOfTotCOST, qTotals.TypeName, qTotals.Manuf, qTotals.Model_no, qTotals.BidPack, qTotals.Alternate, qTotals.ASE, qTotals.UNM, qTotals.[Project Information]
FROM qTotals
GROUP BY qTotals.AltSortOrder, qTotals.Equip, qTotals.TypeSortID, qTotals.Type, qTotals.Name, qTotals.Furnish, qTotals.Install, qTotals.Unitcost, qTotals.TypeName, qTotals.Manuf, qTotals.Model_no, qTotals.BidPack, qTotals.Alternate, qTotals.ASE, qTotals.UNM, qTotals.[Project Information]) qTotals Left Join (Select * from tblREQS-Codes) [tblREQs-Codes] on qTotals.Equip = [tblREQs-Codes].projmeid
ORDER BY qTotals.AltSortOrder, qTotals.Equip, qTotals.TypeSortID;

But I get a Syntax error in FROM CLAUSE.

tblREQs-Codes has these 3 fields:
================================
reqnum        projmeid       fdescription
SLEH-001      L353      Safety Cabinet: Biological, Class II, Type B2 100% Exhaust, Includes Stand and Exhaust Transition, 54" Wide // Nu Aire // NU-430-400.
SLEH-001            Shipping Freight
SLEH-001            Accessories Package
SLEH-004      M714      Sphygmomanometer: Aneroid Type, Wall Mounted, Complete with Adult Cuff and Basket // ** W7670-01CB /
SLEH-004      M715      Diagnostic Set: Wall Transformer with Two Handles with Otoscope and Ophthalmoscope Heads // 76710/11710/31435-120 /
SLEH-004      19720      Scale: Patient, Wheelchair Platform, Electronic, Battery Operated // SE664 /
SLEH-007      13103      Processor: Scopes, Includes Workstation, Water Filteration Kit and Two Trays //Medivators//DSD-201// - [13103 has room numbers (qtys) as -2D110 (1), 2D147 (1)]
SLEH-007            Shipping cost
SLEH-008      19358      Monitor: Vital Signs, MR Compatible, ECG, NIBP, Pulse Rate, SpO2, Adult/Pediatric, with Mobile Cart and Remote Monitor //Precess/3160 Invivo/
SLEH-008            Shipping Cost
SLEH-009      06640      Chair: Blood Drawing, W/Padded Arms, Trendelenburg Option //281//Midmark/ - [06640 has room numbers (qtys) as -1C116 (1), 1D160 (1), 1D165 (1), 1D166 (1), 1D167 (1), 1D175 (1), 1D176 (1)]
SLEH-001            Initial Certification by Precision Air
SLEH-010      26195      Pasteurmatic Washer: Freestanding  Respiratory Therapy Tubing and Parts // Natus//3000 Cat # 52641//
SLEH-010      26198      Dryer: Sterile, Parts and Hoses, Floor Mounted, with Casters // Natus//54344-44//
SLEH-010            Shipping Fees
SLEH-011      02346      Treadmill: Cardio Exercise, Grade Elevation //GE//T2100 /
SLEH-011      02345      Stress Test System, Includes Monitor & Controller and Mobile Cart // GE// MAX1-CAA-XEAC/
SLEH-011            Special Handling
SLEH-012      13662      ESU: Electrosurgical Generator, GI/Endoscopy, Biploar/Monopolar, with Foot Switch // Boston Scientific// Endostat III /
SLEH-012            Shipping Cost
SLEH-013      06500      Cabinet: Flammable Storage, 45 Gallon Capacity //Eagles//1947//
SLEH-013      06502      Cabinet: Acid Storage, 30 Gallon Capacity // Eagles//CRA-32//
SLEH-013      06506      Cabinet: Flammable Storage, Capacity 4 Gallons, 1 Shelf, 1 Door Self Closing //Eagles//1904
SLEH-014      06672      Refrigerator: Floral, Glass Sliding Dlb Doors // ISI Commercail// GDM-47FC//
SLEH-015      L140      Workstation: Pathology/Gross Cutting, Elevating Height, St. Steel, with Sink and Disposal, Includes Support Frame, Dictation System, Vented // Thermo Shandon // 9700522//
SLEH-015      16225      Refrigerator/Freezer: Laboratory/Biological, 11/5.0 cu.ft Capacity, Single Door // **Jewett Refrigerato Company//PRF17
SLEH-015      16220      Refrigerator: Pharmacy, 37 cu. ft. Capacity, Glass Sliding Double Doors // ** Jewett Refrigerato Company//PRF37
SLEH-015      20646      Refrigerator: Lab, General Purpose, 49 Cu. Ft. Capacity, Dbl. Door // ** Fishe Scientific//13986-249RA /
SLEH-015      20691      Freezer: Laboratory, Single Door, Upright, Manual Defrost // **Fisher Scientific//22-650-527/
SLEH-015      23130      Incubator: CO2, Water Jacketed, Two Stackable Chambers, with Mobile Base //Thermo Scientigic//3110 /
SLEH-015            Hazadous Material Charge
SLEH-015            Freight Charge
SLEH-015            Tax Sales
SLEH-014            Delivery & Intallation
SLEH-019      02300      Defibrillator: with Monitor and Non-Invasive Pacing, Complete ECG Monitor with Paddles (Adult & Pediatric) and Battery Pack // Zoll//R-Series-ALS//
SLEH-019            SurePower Rechargeable Lithium Ion Battery Pack
SLEH-019            Sales Tax
SLEH-019            SurePower Charging Station

It might not like the brackets around [tblREQs-Codes] for your table alias.  It is usually better to use different table alias names than the original table name.

You could try something like this:

SELECT DISTINCTROW tbl_qTotals.*, tbl_reqs.REQNUM
FROM  (SELECT DISTINCTROW  qTotals.AltSortOrder, qTotals.Equip, qTotals.TypeSortID, qTotals.Type, qTotals.Name, qTotals.Furnish, qTotals.Install, Sum(qTotals.Ex_Quantity) AS SumOfEX_QUANTITY, Sum(qTotals.newqty) AS SumOfnewqty, Sum(qTotals.totqty) AS SumOftotqty, qTotals.Unitcost, Sum(qTotals.TotCOST) AS SumOfTotCOST, qTotals.TypeName, qTotals.Manuf, qTotals.Model_no, qTotals.BidPack, qTotals.Alternate, qTotals.ASE, qTotals.UNM, qTotals.[Project Information]
FROM qTotals
GROUP BY qTotals.AltSortOrder, qTotals.Equip, qTotals.TypeSortID, qTotals.Type, qTotals.Name, qTotals.Furnish, qTotals.Install, qTotals.Unitcost, qTotals.TypeName, qTotals.Manuf, qTotals.Model_no, qTotals.BidPack, qTotals.Alternate, qTotals.ASE, qTotals.UNM, qTotals.[Project Information]) tbl_qTotals
Left Join (Select * from tblREQS-Codes) tbl_reqs on tbl_qTotals.Equip = tbl_reqs.projmeid
ORDER BY tbl_qTotals.AltSortOrder, tbl_qTotals.Equip, tbl_qTotals.TypeSortID;

Here is the results of the qTotals

01030      Base Bid      SLEH-066R1      Anesthesia Delivery System:  Complete with Options and Accessories      M      7      0      7
01030      Base Bid      SLEH-067      Anesthesia Delivery System:  Complete with Options and Accessories      M      7      0      7
01030A      Base Bid      SLEH-067R1      Accessories: Mounting Brackets and BIS Monitoring Equipment      M      7      0      7
01032      Base Bid      SLEH-066      Anesthesia Delivery System:  Complete System with Options and Accessories      M      1      0      1
01032      Base Bid      SLEH-066R1      Anesthesia Delivery System:  Complete System with Options and Accessories      M      1      0      1
01322      Base Bid      SLEH-060      Cart: Anesthesia, Five Drawer Basic Station, Adult      N      9      0      9
01322      Base Bid      SLEH-089      Cart: Anesthesia, Five Drawer Basic Station, Adult      N      9      0      9
01374A      Base Bid            Accessories:Monitoring (Adult)      MA      0      0      0
01374B      Base Bid            Accessories:Monitoring (Child)      MA      0      0      0
01374CS      Base Bid            Monitor: Physiological, Modular, with CPU and 15"Color Display, with Remote Keypad, MASIMO assembly,invasive BP,printer,cable hangar,multi-gas module (C-SECT)      N      2      0      2
01374D      Base Bid            Accessories:Monitoring Invasive Pressure Interface      MA      0      0      0
01374ICU      Base Bid            Monitor: Physiological, Modular, with CPU and 15"Color Display, with Remote Keypad, MASIMO assembly, Invasive BP, Printer, Cable hangar, Multi-gas module. Includes Wall Mount. (w/o gas module)      N      12      0      12
01374N      Base Bid            Installation:Monitors (Solar)      MA      0      0      0
01374OR      Base Bid            Monitor: Physiological, Modular, with CPU and 15"Color Display, with Remote Keypad, MASIMO assembly, invasive BP, printer, cable hangar, multi-gas module (OR)      N      5      0      5
01374T      Base Bid            Training: Monitoring (Solar)      MA      0      0      0
01380      Base Bid      SLEH-049      Monitor : Level of Conciousness Monitor, Real Time EEG, Color Display, Four Lead Patient EGG      N      0      0      0
02100      Base Bid      SLEH-064      Monitoring: Holter Analysis Management Software Package, Includes CPU, 19" Monitor, CPU, and Keyboard      M      1      0      1
02300      Base Bid      SLEH-019      Defibrillator: with Monitor and Non-Invasive Pacing, Complete ECG Monitor with Paddles (Adult & Pediatric) and Battery Pack      N      24      0      24
02300      Base Bid      SLEH-055      Defibrillator: with Monitor and Non-Invasive Pacing, Complete ECG Monitor with Paddles (Adult & Pediatric) and Battery Pack      N      24      0      24
02300      Base Bid      SLEH-099      Defibrillator: with Monitor and Non-Invasive Pacing, Complete ECG Monitor with Paddles (Adult & Pediatric) and Battery Pack      N      24      0      24
02330      Base Bid      SLEH-063      ECG: Electrocardiograph, Computerized 12 Lead, with Mobile Cart and Accessories      M      4      0      4
02345      Base Bid      SLEH-011      Stress Test System, Includes Monitor & Controller and Mobile Cart      M      2      0      2
02346      Base Bid      SLEH-011      Treadmill: Cardio Exercise, Grade Elevation      M      2      0      2
02370      Base Bid            Monitoring System: Physiological and Hemodynamics (For Cath Lab and Angio)      M      0      0      0
02389      Base Bid      SLEH-064      Recorder: Event, Holter Monitoring, Battery Operated      N      10      0      10
02401      Base Bid            Vascular Flo Lab: Computerized, Complete with Mobile Cart      M      1      0      1
02401A      Base Bid            Accessories: Dicom with Sonova E Software, Temp Probe and Touch Screen      N      1      0      1
02564      Base Bid      SLEH-076      Pump: Intra-Aortic Ballon Pump      M      1      0      1
04024      Base Bid      SLEH-029      Cart: ENT, Standard Treatment, High Back with Upper Shelf, Complete with Vacuum and Pressure Pumps      M      1      0      1
04182      Base Bid      SLEH-031      Microscope: ENT, with Mobile Stand      M      2      0      2

Joeyen5,
Still giving me the syntax error in FROM clause.
Anyway I could break it down?  Get the statement going?

Third times the charm?  Give this one a shot.  I got rid of the DistinctRows.

SELECT tbl_qTotals.*, tbl_reqs.REQNUM
FROM  (SELECT  
      qTotals.AltSortOrder,
      qTotals.Equip,
      qTotals.TypeSortID,
      qTotals.Type,
      qTotals.Name,
      qTotals.Furnish,
      qTotals.Install,
      Sum(qTotals.Ex_Quantity) AS SumOfEX_QUANTITY,
      Sum(qTotals.newqty) AS SumOfnewqty,
      Sum(qTotals.totqty) AS SumOftotqty,
      qTotals.Unitcost,
      Sum(qTotals.TotCOST) AS SumOfTotCOST,
      qTotals.TypeName,
      qTotals.Manuf,
      qTotals.Model_no,
      qTotals.BidPack,
      qTotals.Alternate,
      qTotals.ASE,
      qTotals.UNM,
      qTotals.[Project Information]
FROM qTotals
GROUP BY       qTotals.AltSortOrder,
            qTotals.Equip,
            qTotals.TypeSortID,
            qTotals.Type,
            qTotals.Name,
            qTotals.Furnish,
            qTotals.Install,
            qTotals.Unitcost,
            qTotals.TypeName,
            qTotals.Manuf,
            qTotals.Model_no,
            qTotals.BidPack,
            qTotals.Alternate,
            qTotals.ASE,
            qTotals.UNM,
            qTotals.[Project Information]
) tbl_qTotals
Left Join (Select * from [tblREQS-Codes]) tbl_reqs on tbl_qTotals.Equip = tbl_reqs.projmeid
ORDER BY tbl_qTotals.AltSortOrder, tbl_qTotals.Equip, tbl_qTotals.TypeSortID;

Hello, Poster.  

I don't know if you have resolved this yet, I thought this was done already, so I didn't follow.  

Regarding my earlier comment about groupings, I wasn't talking about the join.  If you like at the "group by" portion of your query, they are the same in both queries up until the last field: qTotals.[Project Information] vs. [tblREQs-Codes].reqnum  Without knowing more, is it possible just to match the group by clause in both statements?  Will that give you the data that you are looking for?

Access doesn't would not return a different number of fields from a query on a table and a query using a left join unless you are grouping by different values.  Disctinctrow and Distinct will not help because you are looking for unique values in just some of your fields, like a primary key or unique index does.  

I don't know what your data requirements are, but with table structure and sample data, I don't know what else I can do to help.

I'm sorry I made this thread longer than what it should be.
Please note I've provided a more clearer picture of what I need.

Basically the data comes in via qTotals as shown in Example 1.
What I'm trying to do is shown in Example 2 so that the pricing does not duplicate for each equip code.
Example1.xls
Example2.xls

I think you are going about this the wrong way.  You should be using subreports.  Use two separate queries.

Query 1 selects your groups for your main report.  Groups only, based on Equip field.  You can include your summary info in here as well.

Query 2 selects your members, i.e. Requisition.  This would go in the subreport.  

Since you are using Access, you can lay this out however, you like, but the relation is one to many and should be treated as such.  

rfportilla,

Could you please post me a small sample of an access file that has the reports that does what you saying?

Here is a quick and dirty db using the data you gave me.  Run report1 to see an example of the report with a subreport.  When I set up the subreport, I just clicked and dragged query2 into the report and it started a wizard.  This is the easiest way to do this.

Caveats: I used grouping and sums in the query1 to show that you can group fields together and get sum info in the same query.  I don't have your original data so you will have to decide where it is appropriate to get sums.  Secondly, Query2 has 2 fields in it.  I think you only wanted to show one field.  You can change that in the query design by just taking unchecking show.

The key thing here is that you are grouping in one sql query and showing the related data in the second query.  This ends up being a one to many relationship: one group to many members.  Just like with forms, reports are made to work with subreports to handle that kind of relationship.

Hope this helps.
deleteme.db.accdb

Thank you rfportilla this helps a lot.
I appreciate you taking the time to do this.

But before I accept you are telling me there is no way to do this via a query only?

I thought I saw someone do something similiar with a union query that provided the results via one querly only which looked exactly like your main and subreport?

I think it's possible using a union query.  Here is something I cooked up based on the data you gave me.

I don't think this does exactly what you are looking for, but the concept is there.

The concept of the one-to-many is still here.  

1. There is a query that represents the one.  This contains the grouping syntax.
2. A second query represents the many.  This pulls everything from the table
3. In order to create a union between them, I have to match the fields.  You can see this in the union

At this point you have a table that has all of the individual data, with some blank fields, and summary data.  The summary data is duplicating a row.  In order to remove that row, you surround that with another query that groups together the summary row and the matching data row.  This is where my query fails.  I need to be able to uniquely identify the data row, like a primary key, in order to create an effective grouping.  

Is it possible to do it the way you want to, it seems like it.  But it is much more of a pain in the butt.  This took me much longer to do than the sample db with subreports.

 Good luck.  ;-)

SELECT 
  First(Table1.Alternate) AS Alternate, 
  Table1.Equip, 
  Sum(Table1.SumOftotqty) AS totqty, 
  Sum(Table1.SumOfEX_QUANTITY) AS SumOfEX_QUANTITY, 
  Sum(Table1.SumOfnewqty) AS SumOfnewqty, 
  First(Table1.Unitcost) AS Unitcost, 
  First(Table1.Requisition) AS Requisition
FROM
(SELECT 
  First(Table1.Alternate) AS FirstOfAlternate, 
  Table1.Equip, 
  Sum(Table1.SumOftotqty) AS SumOfSumOftotqty, 
  Sum(Table1.SumOfEX_QUANTITY) AS SumOfSumOfEX_QUANTITY, 
  Sum(Table1.SumOfnewqty) AS SumOfSumOfnewqty, 
  First(Table1.Unitcost) AS FirstOfUnitcost, 
  First(Table1.Requisition) AS Requisition
FROM Table1
GROUP BY Table1.Equip
UNION ALL
SELECT 
  '', 
  Table1.Equip, 
   '',
  '',
  '',
  '',
  Table1.Requisition
FROM Table1)
GROUP BY Equip, Requisition
ORDER BY  Equip asc, FirstOfAlternate desc

Select allOpen in new window

I apologize, I didn't have time to try out until later today.  Thanks for the reply.

The following is what I have thus far but getting a syntax error in FROM clause.
If you don't want to comment anymore on it I understand.

SELECT 
  First(qTotals.Alternate) AS Alternate, 
  qTotals.Equip, 
  Sum(qTotals.SumOftotqty) AS totqty, 
  Sum(qTotals.SumOfEX_QUANTITY) AS SumOfEX_QUANTITY, 
  Sum(qTotals.SumOfnewqty) AS SumOfnewqty, 
  First(qTotals.Unitcost) AS Unitcost, 
  First(qTotals.Requisition) AS Requisition
FROM
(SELECT 
  First(qTotals.Alternate) AS FirstOfAlternate, 
  qTotals.Equip, 
  Sum(qTotals.SumOftotqty) AS SumOfSumOftotqty, 
  Sum(qTotals.SumOfEX_QUANTITY) AS SumOfSumOfEX_QUANTITY, 
  Sum(qTotals.SumOfnewqty) AS SumOfSumOfnewqty, 
  First(qTotals.Unitcost) AS FirstOfUnitcost, 
  First(qTotals.Requisition) AS Requisition
FROM qTotals
GROUP BY qTotals.Equip
UNION ALL
SELECT 
  '', 
  tblREQs-Codes.Equip, 
   '',
  '',
  '',
  '',
  tblREQs-Codes.Requisition
FROM tblREQs-Codes
GROUP BY Equip, Requisition)
ORDER BY  Equip asc, FirstOfAlternate desc
 

Select allOpen in new window

Uhm, I don't know what your table names are, but assuming that you spelled them correctly, try wrapping tblREQs-Codes in [].

For example, change line 29 to:

FROM [tblREQs-Codes]

Generally speaking, use underscores(_) not hyphen (-) to avoid problems like this.  Aside from not having the tables in my db, this is the only problem I had.

Hey rfportilla,

I tried the above - it didn't work.   So I tried to make it as simple as possible and created TableA for qTotals and TableB for tblREQs-Codes.  Then I used your query above and started refining.  I've got the Query1 refined enough where I think I'm getting warmer but still getting an error - you tried to execute a query that does not include the specified expression 'Equip' as part of an aggregate function. I know this has to do with adding the Equip field but not sure on the correct syntax to add.

Please note sample provided and check out Query1 - Design before running.  Forgot I had a way to save down the file to .mdb.
Sample.mdb

All of what you state is true and I agree.

But to be fair, you gave me this sql statement - for comment 09/20/10 10:02 AM, ID: 33717098 and you have already the correct database from my Excel samples above.  Why can't you take the deleteme.accdb and do a Query3 with the union described above.  Again all I'm trying to is what Example2.xls is trying to show at comment - 09/19/10 12:14 AM, ID: 33710222

I do admit I should not have provided another database - Sample.mdb that has made it more confusing.  But it was in response to your SELECT statement that could have included the same tables already provided by yourself.  If it doesn't work this time around then I'll close it based on the reports/subreports solution.

I attached the sample db.  There are 4 queries.  I did not use TableB, b/c based on the excel query that you posted, all of the data is available in TableA.  If this is not the case, then I just don't understand the relationship.  

Let me explain the queries.  Query1 is a grouping query that provides the headers.  Query2 has all of the data.  Query3 combines the records from Query1 and Query2 in a Union and removes the duplicate entries by grouping.  Query4 is the same thing as Query1,2,and 3, but in one step with everything combined.  

I hope this helps.  
Sample-1-.accdb

Heck yeah.

Perfect.  You the man.

Thanks rfportilla.

I wish I could give you more than 500 points for that.

ok

Got a little carried away there.

projmeid in Table B is the same as Equip in Table A.
reqnum in Table B is by itself no relation to Table A except via the relationship between projmeid and equip.

I'm trying to show all reqnums in Table B for everyone in TableA without duplicating the cost per row item.

Having requisition field in TableA is confusing and should be ignored.  Because the problem is that there are multiple requisitions per equip line item which can only be found in TableB.  Originally requisitions was created to be only for one equip code then they changed the dynamics on me later down the road saying we have multiple requistions per equip item - thus I had to create TableB to hold such.

So you would have to show the reqnum field not requisition to be correct.

This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.