Not Equal To & Grouping Query

Apr 28, 2008

I have Table as PL2008 and RPT2. Both tables have one column named PTUNID. My goal is to first Group, sum certain Columns in the PL2008 then match PTUNID from this Grouping to RPT2 Table's column PTUNID and then show the results which do not match.

I have written following Qry but what really happenning is its giving me all the results ( match and no match in both tables ) and Sum values are incorrect.

SELECT [PL2008].PTUNID, Sum([PL2008].[Total Hours]) AS [SumOfTotal Hours], Sum([PL2008].[Actual Cost]) AS [SumOfActual Cost], [PL2008].[PRFIELD]
FROM RPT2, [PL2008]
WHERE RPT2.PTUNID <> [PL2008].PTUNID
GROUP BY [PL2008].PTUNID, [PL2008].[PRFIELD];


Any suggesstion



Thanks
Niri

View Replies


ADVERTISEMENT

"Not Equal And Not Equal" Query Not Working

Apr 14, 2006

I am trying to run a query where one field has a Not Equal parameter AND another field has Not Equal parameter (for example, Product Code Not Equal "A" AND Client State Not Equal "NY)

The result of the above query are all records that have Product Code = "A" are missing from the result. I want some of the "A" Product Codes; I just want to exclude the ones where the Client state is NY.

The list of values for each field is too big to state it in a positive way (I would have to list all the other 28 product codes and all the other 49 states).

What am I doing wrong? Thank you in advance for any suggestions.

View 1 Replies View Related

Query To Return Values That Are Not Equal

May 25, 2006

I have a query that uses a linked table from an external database and joins it with a table from my database to return records that contain the same values.

Is there a way to return only the records that are present in the external database and not in my db?

Thanks

View 3 Replies View Related

Select Equal Type - Based On Random Query

Oct 6, 2006

Good Afternoon Everybody,

Apologies for imposing on you all but I was wondering whether or not you could help me out??

Would you be so kind as to kind as to show me a practical example of how to incorporate a facility into my attached Database which serves to select an equal ratio of my field “SectionHeadings” such that out of the 30 “random” questions that it currently generates it will provides me on completion with the following:

5 From Set1
5 From Set2
5 From Set 3 …..etc etc

It currently generates the 30 questions that I need without a problem; however it routinely selects any number of one particular type, sometimes more from one subject and less of another.

All subjects headings are of equal importance to me and as a consequence would be very grateful if you could look at what I’ve already done and show me what needs to be done in order to achieve my aim.

I hope my request is possible - Any help you could give me would be very much appreciated. Thank you very much.

Best Regards

CarolW

View 5 Replies View Related

Queries :: Delete Query Where Code And Client Are Equal

Aug 8, 2013

I am trying to delete a record in tblinclude where record from tblexclude are equal to clientid and codeid

Here is the sql
DELETE tblinclude.ClientID
FROM tblexclude INNER JOIN tblinclude ON (tblexclude.ClientID = tblinclude.ClientID) AND (tblexclude.CodeID = tblinclude.CodeID)
WHERE (((tblinclude.ClientID)=1));

I get the error Specify the table containing the records you want to delete. I've searched for this but I am just not getting it today.

View 1 Replies View Related

Queries :: Criteria In Query Is Greater Than Or Equal To 15% But It Gave Values Less Than 15%

Nov 24, 2013

same as the title goes, why i can't query those figures greater than 15% only? How am i suppose to query them right?.Here is my database you cant check query1 and query 2. I am using Ms Access 2010.

Link:

www dot dropbox dot com/s/n3iuev1c5e0dfby/StockMonitoring.accdb

xD: StockMonitoring.zip

View 5 Replies View Related

Grouping Records Using A Query

Aug 2, 2005

How can I group records based on values within a particular field? For example, if I have records of people with different annual incomes, what is the easiest way to allocate the records to income bands? I assume the starting point will be to create a band classification in Excel and import as a table. How do I subsequently run a query to achieve the desired effect?

View 1 Replies View Related

Query Problem For Grouping

Sep 28, 2006

Hi,

I am trying to display the number of issues logged and closed for each particular month. My SQL query works, but the number of closed issues is where I get stuck. I can only see a way of displaying the number of closed issues per month by specifying the exact month number in the query (Loop?)

SELECT Year(bg_reported_date) AS [Year Reported], Month(bg_reported_date) AS [Month Reported], Count(dbo_bugs.bg_id) AS [Number of Issues Logged], (SELECT count(bg_last_updated_date)
FROM dbo_bugs where Year(bg_last_updated_date) = 2006 and month(bg_last_updated_date) = 1 and bg_status =5 and bg_project = 12 ) AS Closed
FROM dbo_bugs
WHERE (((dbo_bugs.bg_reported_date) Between #1/1/2006# And #12/31/2006#) AND ((dbo_bugs.bg_project)=12))
GROUP BY Year(bg_reported_date), Month(bg_reported_date)
ORDER BY Year(bg_reported_date), Month(bg_reported_date);

View 1 Replies View Related

Grouping Query Results

May 31, 2007

I have a select query from a few related tables, one relationship being many-to-many. The resulting query, when run would have several groups of records where each group has all but one field different (a text field). Is it possible to do a report where this group appears as one record with the results of that field as a list?

View 2 Replies View Related

Query Help: Grouping Like Rows

Mar 10, 2008

I have a table that has 2 colums.


Room_Number Items
1 Chair
2 Chair
2 Desk
3 Table
4 Computer
4 Radio
4 Book
4 Chair
6 Fish Tank
6 Chair


I want a query that will select rooms 1-6 and include all items for those rooms on one row. Example below


Room_Number Items
1 Chair
2 Chair, Desk
3 Table
4 Computer, Radio, Book, Chair
6 Fish Tank, Chair


I hope this makes sense. Thank you in advance.

View 4 Replies View Related

Grouping And Summarizing In Query

Apr 24, 2008

Is it possible to group and summarize fields while in a query instead of doing it in the report section? Please find the query attached.
For each day, I need to group the "Expr1" field (shifts employees worked) and "Date" field and to summarize their corresponding "Cases" and "LBS" values.

This is how I need to see it (actual groups and totals):

01-Apr-086136121317.78SHIFT1
01-Apr-086935131899.94SHIFT2
01-Apr-087169144949.29SHIFT3
02-Apr-088229169379.26SHIFT1
02-Apr-089259202454.86SHIFT2
02-Apr-086754141018.96SHIFT3

Any help is highly appreciated!
Thank you,
Peter

View 2 Replies View Related

Grouping Query Results

Jul 31, 2006

Hey,

I have a table, which is full of information, but could be broke down into something like the following:

Dan - Job 1
Dave - Job 1
Karl - Job 1
Steve - Job 2

I want a form to appear with the different job's on, and then from there I select which job I want to view the details for.

So for example, in the list it would say Job 1 or Job 2. If I click Job 1, it would bring up a form showing details for Dan, followed by Dave and then Karl. If I click on Job 2, it would just bring up Steve's details.

When I try and do the query, it lists all of the Jobs (Job 1, Job 1, Job 1, Job 2), where I just want it to group all of one kind together.

Is this possible?

Thanks

Dan

View 1 Replies View Related

Grouping Results In Query

Oct 26, 2012

I created a query and everything is OK, but i wanted to group the result, here is the code

Code:
SELECT Equipment.Category, ([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])+(([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])*0.2) AS Income
FROM Equipment INNER JOIN Rent ON Equipment.Equip_ID=Rent.Equip_ID;
I want the total for all equipment which belongs to the same category

View 6 Replies View Related

Grouping Records By Month Query

Oct 27, 2005

This is probably an easy question for you all:

I want to run a query that does a GroupBy with DATE and Counts the number of records associated with that DATE.

I can do this, but I also want the dates to group together by Month so I can count the number of records per month for multiple months in the same query. How do I do this?

Thanks very much

View 1 Replies View Related

Query Datetime Field And Grouping By Date Only

Aug 28, 2007

I'm losing my mind on this one. Here is my situation... I receive a daily excel sheet with these fields. I would like to import this workfile into access and would like to manipulate it anyway I want. The problem I'm coming into is that I can't collapse/group the datetime field into just the date when I run a query. I would like to be able to run a query on any date range the client registered, a query on any and all the unique dates the client purchased something, all the unique clients, etc. Here is a sample

Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/4/2007 10:21
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/5/2007 18:20
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 10:21
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 01:22 - 8/9/2007 05:46

I would like it to show...

Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/5/2007
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/6/2007
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007

and a different query like ( the purchase date doesn't matter here)...

Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007

I read in other places to change the format, input mask, convert, char, etc. but nothing. Please tell me exactly what to do. I don't care if the time changes to midnight or 0:00:00. I will owe you my first born child.

View 6 Replies View Related

Queries :: Grouping Number Field Into Intervals In A Query

Jul 2, 2014

I have a number field called Jobs.SquareFootage, and I want to run a query that categorizes the field into intervals. The intervals I want are:

0 to 50 squarefeet
51 to 300 squarefeet
300+ squarefeet

This is the query I'm coming up with so far

JobID SquareFootage Revenue
1 40 $5
2 30 $10
3 200 $400
4 250 $350
5 550 $600

I want to put the SquareFootage into the intervals I mentioned above, and get the total revenue for each interval.

View 5 Replies View Related

General :: Grouping Records - Query To Gather Information From Tables

Mar 7, 2013

I have a query that gathers information from the tables in my database and returns something like this:

Code:
Field Pest
1 Insect1
1 Insect2
1 Insect3
2 Insect1
2 Insect3
2 Insect4

Field and Pest are fields in two separate (but joined) tables. Field exists only once in its table, and multiple pests are attributed to each field. I'm looking to create a table or query in which the values would be listed as follows:

Code:
Field Pest
1 Insect1, Insect2, Insect3
2 Insect1, Insect3, Insect4

The purpose for this is to create a legend for a map used by another program based on the information gathered in the database. I do have other criteria for which fields and pests show up in this query.

View 11 Replies View Related

Queries :: SQL Crosstab Query With Multiple Grouping Levels - Transform Statement?

Mar 24, 2015

I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.

The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)

The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.

This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!

Code:
TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund
SELECT [Client Accounting].[Marketer]
FROM [Client Accounting]
GROUP BY [Client Accounting].[Marketer]
PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

View 2 Replies View Related

Not Equal?

May 26, 2005

Hey,

I have 2 tables that i compere together with a query and if my criteria is OK than selected data is recordet in new table with some calculation....code:

INSERT INTO tblZaloga ( Proizvajalec, Tip, DN, Predvideno, Naroceno, Zaloga )
SELECT tblACAD_Urejeno.Proizvajalec, tblACAD_Urejeno.Tip, tblACAD_Urejeno.DN, tblACAD_Urejeno.KosovSkupaj, tblIN_Urejeno.KosovSkupaj, Sum(([tblIN_Urejeno].[KosovSkupaj]-[tblACAD_Urejeno].[KosovSkupaj])) AS Zaloga
FROM tblACAD_Urejeno, tblIN_Urejeno
WHERE (((tblACAD_Urejeno.Tip)=[tblIN_Urejeno].[tip]) AND ((tblACAD_Urejeno.DN)=[tblIN_Urejeno].[DN]))
GROUP BY tblACAD_Urejeno.Proizvajalec, tblACAD_Urejeno.Tip, tblACAD_Urejeno.DN, tblACAD_Urejeno.KosovSkupaj, tblIN_Urejeno.KosovSkupaj;


Now i have a problem with racords that does not reach my criteria. The problem is that i dont know witch records are the one that does not reach my criterya? Can someone pls tell me how can i create a table like the code up there but with records that are not procesed?

THX

View 2 Replies View Related

Not Equal?

Jun 8, 2005

Hey,

Can someone pls tell me how can get not equal resoults from my two tables? I have table1 and table2 both with field "Layer" and what I want is to get only that data from table1 that does not have the same Layer as table2.Layer?

I try to JOIN table1 and table2 and i get the same resoult as with this code:
WHERE ((tblACAD_MRK.Layer)=[tblLayerji].[NeteniLayer])

but thats exactly the oposite from what i want, so i try with this code:
WHERE ((Not (tblACAD_MRK.Layer)=[tblLayerji].[NeteniLayer]))

and the code does not give me the resoult that i want, becouse i get all the layers for some reason :confused:

So can someone pls tell me what can i do?

THX

View 2 Replies View Related

Not Equal

Sep 27, 2005

I'm wanting to write this query that looks at both tables and only returns records that don't match...not for sure how to do this..


SELECT tblCombined.DLID, tblCombined.DUP, tblCombined.QRYRAN
FROM Susp_Master INNER JOIN tblCombined ON (Susp_Master.DUP = tblCombined.DUP) AND (Susp_Master.DLID = tblCombined.DLID);

View 4 Replies View Related

WHERE First Three Letters Equal Xyz

Sep 21, 2007

Hi there,

I have one question with regards to the WHERE part of an SQL query. I want to limit my query on datasets, where the first three letters of a certain field (which contains strings) equal "Exp". Is there a more efficient way than the following?


SELECT A.Date, A.Name
From tblA as A
WHERE A.Name="Exp_A" OR A.Name="Exp_B" OR A.Name="Exp_C";


Best regards,
JapanFreak

View 3 Replies View Related

Value In A Table Equal To The Sum Of Other Values

Dec 11, 2007

Hey.

Is there any way to make a field in a record automatically set it self so it's the sum of other values in the record

http://img412.imageshack.us/img412/1677/helphg9.jpg

That's an example what i want to do except i had to manually type in 25. Is there any way to get that so it does that automatically. I am happy to use forms or macros or anything as long as it occurs straight away after the data is inputted and not have to run something or open something up

Thanks in advance

View 5 Replies View Related

If Records In Rows A And B Are Equal Then

Apr 26, 2008

Hello,
I would like to show records in my query only if records in raw a and b are equal and they have same question id.
Please let me know if you can help.
Many thanks,
Debbie

View 3 Replies View Related

Compare 2 Tables Where NOT EQUAL &lt;&gt;

Feb 7, 2006

It need to compare two tables where the ID Number is not equal. Specifically I need to update new poducts in a table, but only those ID#s that are not already listed.

I know how to compare two feilds where they are not equal...
i.e. [New_Table].[ITEM_NUM]<>[Old_Table].[ITEM_NUM]
...but I'm currently limited by the join properties in the tables, since I'm comparing IDs and not the feilds. ANY IDEAS?

View 2 Replies View Related

Setting A Default To Equal Another Field

Oct 21, 2006

I have a membership database. A number of members go by their middle names or a nickname rather than their first name. I need to keep track of their real full names, but I want the new-record default of the preferred name field to equal the contents of the first name field. How do I set this in the table design? The table already has 1400 records, and it would be quite a job to manually copy each first name to the preferred name field, a job I would rather avoid.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved