Help with a simple query

Hi guys and gals,

Can anyone help me with this query? I am having some issues with
pulling in all names even though (it appears) I have the join correct.

SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
Sum(tmpReports.NumOfSets) AS SumOfNumOfSets
FROM tblEmployee LEFT JOIN tmpReports ON tblEmployee.UserName =
tmpReports.UserName
WHERE (((tblEmployee.IsCQATech)=True) AND ((tmpReports.CompleteDate)
Between #2/8/2010# And #2/12/2010#))
GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;

What am I doing wrong?
0
EAB1977
2/16/2010 1:16:47 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
882 Views

Similar Articles

[PageSpeed] 46

You have defeated the left join by applying criteria to the right side table.

You MAY be able to fix this using a bit more criteria.  The problem

SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
Sum(tmpReports.NumOfSets) AS SumOfNumOfSets
FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName = tmpReports.UserName
WHERE tblEmployee.IsCQATech=True AND
(tmpReports.CompleteDate Between #2/8/2010# And #2/12/2010#
OR tmpReports.UserName is Null)
GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;

If that does not work you Can use a sub-query in place of tmpReports but based 
on tmpReports.

SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
Sum(TEMP.NumOfSets) AS SumOfNumOfSets
FROM tblEmployee LEFT JOIN
    (SELECT UserName, NumOfSets
     FROM tmpReports
     WHERE tmpReports.CompleteDate Between #2/8/2010# And #2/12/2010#) As TEMP
ON tblEmployee.UserName = TEMP.UserName
WHERE tblEmployee.IsCQATech=True AND
GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;

IF you save that in design view Access will modify it slightly to use square 
brackets and a period to denote the subquery.  This means you cannot use any 
square brackets in the subquery.
SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
Sum(TEMP.NumOfSets) AS SumOfNumOfSets
FROM tblEmployee LEFT JOIN
    [SELECT UserName, NumOfSets
     FROM tmpReports
     WHERE tmpReports.CompleteDate Between #2/8/2010# And #2/12/2010#]. As TEMP
ON tblEmployee.UserName = TEMP.UserName
WHERE tblEmployee.IsCQATech=True AND
GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;

If you need to use square brackets in the subquery (for example a parameter 
query), you will need to use two queries.  The first query would be something 
like:

Parameters [Start of Period] DateTime, [End of Period] DateTime;
SELECT UserName, NumOfSets
FROM tmpReports
WHERE tmpReports.CompleteDate Between [Start of Period] And [End of Period]

Then you will use that query as if it were the table tmpReports (in your 
original query).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

EAB1977 wrote:
> Hi guys and gals,
> 
> Can anyone help me with this query? I am having some issues with
> pulling in all names even though (it appears) I have the join correct.
> 
> SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
> Sum(tmpReports.NumOfSets) AS SumOfNumOfSets
> FROM tblEmployee LEFT JOIN tmpReports ON tblEmployee.UserName =
> tmpReports.UserName
> WHERE (((tblEmployee.IsCQATech)=True) AND ((tmpReports.CompleteDate)
> Between #2/8/2010# And #2/12/2010#))
> GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;
> 
> What am I doing wrong?
0
John
2/16/2010 2:10:43 PM
EAB1977 -

Are you getting an error message?  Is the data not what you expect? I see a 
couple things that might cause you to get data you don't expect.   

Is NumOfSets always populated?  If not, change Sum(tmpReport.NumOfSets) to 
Sum(nz(tmpReport.NumOfSets,0)).

The LEFT JOIN will work like an INNER JOIN if you put criteria on the 
tmpReports table that requires information.  You can fix that by changing 
((tmpReports.CompleteDate)Between #2/8/2010# And #2/12/2010#)) to 
(((tmpReports.CompleteDate) Between #2/8/2010# And #2/12/2010#)) OR 
(tmpReports.CompleteDate is null))

If it is something else, tell us what you are getting, and what you expect 
to get...

-- 
Daryl S


"EAB1977" wrote:

> Hi guys and gals,
> 
> Can anyone help me with this query? I am having some issues with
> pulling in all names even though (it appears) I have the join correct.
> 
> SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
> Sum(tmpReports.NumOfSets) AS SumOfNumOfSets
> FROM tblEmployee LEFT JOIN tmpReports ON tblEmployee.UserName =
> tmpReports.UserName
> WHERE (((tblEmployee.IsCQATech)=True) AND ((tmpReports.CompleteDate)
> Between #2/8/2010# And #2/12/2010#))
> GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;
> 
> What am I doing wrong?
> .
> 
0
Utf
2/16/2010 2:53:02 PM
John's second query did the trick. Thanks!

Eric

0
EAB1977
2/16/2010 4:19:36 PM
Reply:

Similar Artilces:

help with nested iif statement
I have a table and I want to create a query that has a new field " Type of Shipment" base on a field "Item Category" If the item category field equals ZDIR, ZTRN ...etc up to 23 different item categories the "Type of Shipment field will equal DIRECT else I would like it to return "Stock" Any help will be greatly appreciated. Thanks Hi, for the iif statement. What you can do is: IIf(category = 'ZDIR' OR category = 'ZTRN' OR .............,'DIRECT','') Having a long nested if is a headache. What i can suggest ...

Making a query to get data from a parent-child table
Hello, Im quite new to VBA coding with MS Access and may do things that most of you wouldnt so I would appreciate any further insight into question if your willing to provide it. Im creating a system for my cousin so he can basically create invoices for his company. His company has 2 different mailing addresses so I have used a table (tblCompany) to store this information. The main data is stored on a parent (Client) and child (Product) table. When I open the parent table, I can view all the products that were shipped to the particular client. This works great. ...

Determine NT 4.0 SP Level help..
Is there an easy way to figure out what service pack a NT 4.0 server was running by looking at a file time/date stamp from a restore tape?? We need to restore Exchange from a few years back, and it's critical to match the OS service pack in order to the get info store to mount... When looking at the tape catalogs, is there an easy way for me to figure out what NT service pack was running??? Did NTDETECT.COM or NTLDR change with each SP??? If so, is there a matrix anyway showing file time/date stamps and the associated service pack? Any help is greatly appreciated! Troy Tr...

Simple question on combining workbooks.
I have two multi-sheet workbooks that I need to combine (the macros need to be combined too). How do I do this? I know it CAN be done, I just can't remember how. -- madbloke ------------------------------------------------------------------------ madbloke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14422 View this thread: http://www.excelforum.com/showthread.php?threadid=264637 Copying sheets an be done via the worksheet tab right-click menu, or by dragging the worksheet tabs between workbooks. You can also drag modules from one workbook to anothe...

Re: New here
Success! Many thanks for your help. Philo. ****************** Philocophus wrote: > I recently upgraded from XP Pro to Vista Ultimate. recently I have not > been able to install Microsoft .NET Framework 2.0 Service Pack 2 > Security Update for Windows Vista Service Pack 1 and Windows Server 2008 > (KB974469). The installation failed repeatedly with error code 8007370B > popping up. > > I downloaded and ran MSRT malaware remover and after a couple of hours, > all was OK. Still no success in insalling the 2.0 service Pack 2. Ran > Protection sca...

Reading a COMPLEX CONTENT : Stan Can you help?
Hi STAN, Stan: Thanks for your response to my previous post on reading a XSD file using your article in "https://blogs.msdn.com/stan_kitsis/archive/2005/08/06/448572.aspx". it works quite well but I have one problem.. I am not able to read a Complex Content.. Here is a portion of the XSD that contains the complex content. I need to read the elements under it and could not get an handle to it.. Could you please help? Thanks, Ganesh *********************** <xs:complexType name="UserBasic"> <xs:complexContent mixed="false"> <xs:exte...

Integration Manager
Hello, I have an integration that uses DTS to load the source file into a temp table for the integration to read and add to the GL. If the source (which contains a SQL query to a view on the temp table) is empty (no results returned from query), how can I catch that to use for error-handling? Thanks You can add the following VBScript code in your Before Integration event script: Option Explicit Dim oConn, oRS, totalRecords set oConn = CreateObject("ADODB.Connection") oConn.Connectionstring = "database=YourCompanyDatabaseHere" GPConnection.Open(oConn) set oRS...

Help Conditional Formatting
Help: I'm using conditional formatting on some cells to highlight particular values. The conditional formatting on 2003 only allows 3 levels of conditional formatting. Anyone know if there is a way to increase the number of levels? Thanks for any advice. -- Dab Cut off: yourhead to respond Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do...

Help with Linq to Dataset
Hi, I have a problem with Linq to Dataset that have N rows and 13 columns, the first colums represents a bank and the other 12 are the months. What I need is to get rows that have the same bank and one chosen month. Dim months() As String() ={"Jan","Feb".......} Dim banks= From q In dtTempDataTable _ Where q.Field(Of String)(0) = bank_ Group By CC = q.Field(Of String)(0), DD = q.Field(Of Double)((Array.IndexOf(months, singleMonth) + 1)) _ Into Group _ Select New With...

How to get a query to call the parameter form
I am trying to use a form to get query parameter dates as is detailed in the help article "Make a query ask for input" The query does not open the date range form when the query is used If the form is opened in advance and dates entered then the dates in the form are used what is missing? How is the open form macro called ? WillyNPG wrote: >I am trying to use a form to get query parameter dates as is detailed in the >help article "Make a query ask for input" The query does not open the date >range form when the query is used If the form is opene...

Table help!
Hi I have a table which was fine until another programmer got at it now columns are missing I know they're probably just hidden but how do I bring them back? There are arrows beside some of the column headers. I'm assuming Access 2007. The arrows don't mean anything about being hidden. Right click on any one of the column headings. Select Unhide Columns. Put a checkmark in the dialog box for any of the columns that are hidden. If none of the boxes are missing a checkmark, there are no hidden columns. It's also possible that the columns are so narrow that it look...

I need help with a formula.....
I'm trying to add up cells going from left to right and what ever my answer is.. if it's greater then (1) then I want to report just one. example. If I'm checking somebodys work and I look at 12 diff areas, and they get 3 out of the 12 wrong, I want to count it as one error instead of three. I've been tring the following. SUMIF(F11:Q11,"<0=1") all i get is 0 I hope this makes sence. Thanks for the help =MIN(SUM(F11:Q11),1) Vaya con Dios, Chuck, CABGx3 "photoman27" <photoman27@discussions.microsoft.com> wrote in message news:4C8EEA53-4AE...

Simple question: Can I a lookup across multiple columns? (ie, if c and d = j and k)
Say I want to match a value from column a and column b with a value tha is somewhere in column j and column k, and if it does make the value o the selected cell the value in column l. Ex: A B C J K L 1 2 2 3 x 4 5 6 7 y 8 9 1 2 z In that example the lookup would give back the value of z for the firs c column entry and nothing for the others since 4 and 5, and, 8 and do not match up with any two columns in j and k -- Message posted from http://www.ExcelForum.com Something like this might work for you: I put the table in A1:L3. I put 1 i...

How to query for Parent Records with no matching Child Record?
2 tables...tblParent and tblChild... ALL tblChild records have one related record in tblParent with DDD in fldTitle SOME tblChild records have _another_ related record in tblParent with CCC in fldTitle (So some tblParent records have ONE related tblChild record and some have TWO.) I want to find the tblParent records that have NO tblChild record with CCC in fldTitle (so these will be the tblParent records with only ONE related record... the one with DDD in fldTitle) I have both tables in the query grid with... tblParent.fldName and tblChild.fldTitle pulled down to the query grid Crite...

new music store help...
Ok so, my wife and I are opening a music store in a couple of weeks and in the future I would like to set up a couple of computers for people to search out music websites ONLY. I have NO experience setting up a network but here is what i'm looking at The store's main computer Dimension 2400 Series Intel® Celeron® Processor at 2.4GHz with 400MHz front side bus BC244A Operating System: Microsoft® Windows® XP Home Edition¹² Memory: 256MB Shared DDR SDRAM at 333MHz Keyboard: Dellâ„¢ Quietkey® Keyboard Monitor: 15 in (15.0 in viewable) E152FPb Flat Panel D...

Data from web query to a single cell
Hi All, How do I make data coming from a web query to populate a single cell . The data from http://finance.yahoo.com/d/quotes.csv?s=vclk&f=sl1 occupies two cells one below the other. Any help would be greatly appreciated. Thanks Kash Strange. xphome xl97sr2 put symbol, value in same cell xl2002 put symbol in col A and value in Col B. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "khosa" <khosa19@yahoo.com> wrote in message news:8ac3d73b.0307311518.1d8efb1c@posting.google.com... > Hi All, > > How do I make data coming from a web query t...

I get a black box instead of my graphic when printing. Help!
That would more than likely mean you need to update your printer driver. Visit the website of your printer manufacturer, such as www.hp.com for HP printers and search for your printer driver under the driver download section. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "Kschnese" <Kschnese@discussions.microsoft.com> wrote in message news:F55C9A56-8753-4A5B-B3F4-48AD2104B232@microsoft.com... > ...

3 Caluclations From Similar Formula
kkknie Thank you very much. This worked a perfectly. Celtic_Avenger :) :) :) :) : -- Celtic_Avenge ----------------------------------------------------------------------- Celtic_Avenger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1410 View this thread: http://www.excelforum.com/showthread.php?threadid=26544 ...

Pivot Table Query #2
Pivot Table Query Being fairly new to pivot tables, I'm sure there must be answer to a situation we have that would ease the viewing and analysis of our sales data. Basically we have four divisions each selling a range of up to 60 products and we try to keep the sales stats on a monthly basis. Currently we have a worksheet for each division with the Column A being the Product type and columns B, C etc onwards being the month of sale, Each row represents the product being sold. We are careful that, say row 16 on each sheet is product ABC to maintain consistency throughout the w...

Update Query not updating selected table
I have created an update query to update a master file. When I run the select query to view the records I need to update - I see the correct data. When I change query to update query and enter the table to update and run update query. The query is updating the source table (Change Form) instead of table indicated (AMT Per MAS) to update to....Strange.. I am using Access 2003 Below is the SQL. UPDATE [AMT Per MAS] INNER JOIN [Change Form] ON [AMT Per MAS].ID = [Change Form].ID SET [Change Form].[FROM AMT Assignment] = [AMT Per MAS]![FROM AMT], [Change Form 2].[TO AMT] = ...

Produce report from two queries from one table?
I am currently working upon a database which is being used to produce pricing qutoes. I have ran two queries to show item of products which have been quoted for and one which shows a sum of the remaing unquoted products. Each product is placed into different sections which relates to its main function, be it electrial or machineary. I am hoping to have a report which will allow me to show all quoted products under the sections, but have the unquoted products for the sections as one line entry in the report. I have ran a normal report showing full list details of both quoted and un-quo...

Can I modify Sql server reference in CRM 3.0 ?urgent please help
Hi, Its very urgent for me to do this,I had CRM3.0 and SQl Server 2000 on same server.I referred Sql server 2000 in CRM 3.0 while installation.But now I had to change in CRM 3.0, the sql server reference to Sql server 2005(Sql server 2005 is installed on another server.Is that possible now?Please help me.I am badly in need of help thanks in advance for your assistance ...

help files in Pub2000
When I try to use the MS Publisher help I get error messages. Specifically when I click on a hotlink in a help topic it says internet explorer script error any assistance would be nice Pete OS: Win2k IE: 6 MS Pub: 2000 Pete, 1. Open Internet Explorer 2. Go to Tools>Internet Options>Advanced>Browsing Header 3. Be sure to put a check mark next to "Disable script debugging" and "UNcheck Display a notification about every error." -- Brian Kvalheim Microsoft Publisher MVP For FAQ's, Templates and More: http://www.kvalheim.org Activewin Staff Management ww...

HELP....PLEASE Update not complete
I have been using money essentials for a few years now with no problem. I get a pop up that it is time to renew. I d/l the new ? version of Essentials now I cant update any of my accounts. I tried to do what the support page says and stop the services to set them up again but after I do that it tries to go to any of my accounts and immediately pops up "update not complete" like it never even goes to any site. After three hours on the phone being bounced back and forth for three hours from digital river to Money tech they wanted to charge me forty dollars for support. When I to...

seem to have lost my mp3 sound... help
I seem to have lost my mp3 sound although I can still hear computer sounds.. I remember there used to be a sound device thingy were you could enable disable sound devices but I cant seem to find it. can someone please help.. TIA ...