Solved

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

Posted on 2010-09-13
28
481 Views
Last Modified: 2013-11-27
All please note the first SQL statement I have that is a subtotals query of which has a total of 591 rows.  

When I change it to the second SQL statement (shown below which adds another table to the mix called tblREQs-Codes) I get 695 rows an extra 104 rows.

Obviously there are multiple reqnum fields per Equip but I don't want the charges to be doubled per line item.  

When I run a report on top of the second SQL statement it increases the charges by $5,000,000 dollars.

Is there a way I can use a union query to just display my reqnums per equip item shown and not have the total price increased?
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]

ORDER BY qTotals.AltSortOrder, qTotals.Equip, qTotals.TypeSortID;



===============================

'2nd one....

===============================



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, [tblREQs-Codes].reqnum AS Requisition

FROM qTotals LEFT JOIN [tblREQs-Codes] ON qTotals.Equip = [tblREQs-Codes].projmeid

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, [tblREQs-Codes].reqnum

ORDER BY qTotals.AltSortOrder, qTotals.Equip, qTotals.TypeSortID;

Open in new window

0
Comment
Question by:stephenlecomptejr
  • 14
  • 8
  • 3
  • +2
28 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 33667893
Can you please provide some sample data (in both the tables and your desired output?

Thanks.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33668702
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.  

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33671506
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?
0
 
LVL 2

Expert Comment

by:Joeyen5
ID: 33671906
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;
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33675820
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.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33675871
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
0
 
LVL 2

Expert Comment

by:Joeyen5
ID: 33675898
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;
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33675948
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
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33676014
Joeyen5,
Still giving me the syntax error in FROM clause.
Anyway I could break it down?  Get the statement going?
0
 
LVL 2

Expert Comment

by:Joeyen5
ID: 33676159
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;
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33688763
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.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33710222
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
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33710407
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.  
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33710949
rfportilla,

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

Expert Comment

by:rfportilla
ID: 33711047
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
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33713635
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?
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33717098
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

Open in new window

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33742396
I apologize, I didn't have time to try out until later today.  Thanks for the reply.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33754487
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

 

Open in new window

0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33762284
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.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33764200
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
0
 
LVL 9

Accepted Solution

by:
rfportilla earned 500 total points
ID: 33764904
I am sorry, but this db confuses me more than anything else.  

1. What is the relationship between tableA and tableB?  You don't have one defined.
2. There are lots of errors in your SQL.  It's hard to tell what you are trying to accomplish.  I don't think you understand what the union does.  From what I see I think you should be using a join to relate tableA and tableB?
3. The Union is for compiling two similar record sets into one.  It looks like you are trying to use it to relate two tables. You need a union in the query you are trying to do, but not the way you are using it.
4. You understand your data better than I do.  You understand what summary data you are trying to get better than I do.  You need to understand how the query I wrote works so that you can display your data correctly.  But, if you don't have the background to understand the sql, this is going to be very tough.  Please review my previous post where I am explaining the parts of it

This is an advanced sql statement and in my opinion, the wrong way to get this done.  This really needs to be done with a report.  Sorry, but I can only do so much in this forum.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33765974
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.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33766513
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
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33766878
Heck yeah.

Perfect.  You the man.

Thanks rfportilla.

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

Author Comment

by:stephenlecomptejr
ID: 33766895
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.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34118957
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server views 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 Access…
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…

746 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