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 Replies


ADVERTISEMENT

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

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

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 3 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

Queries :: Query To Insert Missing Rows From Another Query / Table

Oct 8, 2013

I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.

Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.

As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15


What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15

View 2 Replies View Related

Too Many Rows In Variable Used For Query.

Jun 29, 2005

Hi, I am totally out of my depth on this one. I have managed to find ways to solve my other problems but this one won't seem to budge.

I have one large table ALLENGPUMP which has columns of data and other columns of lookups to associated tables. What I am trying to achieve is that by selecting a value from a combo box (which contains the results of: SELECT DISTINCT ALLENGPUMP.Coupling_Order_code FROM ALLENGPUMP; ) a list box will show all of the other columns from the table (and the associated tables).

In lstOther3 and lstOther4 the columns sought are in the same table and this will produce a result of multiple rows when the Coupling_Order_Code is apparent for more than one ID, as required.

lstOther5 contains the columns with lookups and only works when the Coupling_Order_Code returns only one ID. When more than one ID is returned it no longer functions and returns nothing. The best I have so far is shown below (this is from VBA) although lstOther5 only has one of the columns I am looking up whereas there are 5 in total for the data in 5 other tables.

So, to recap, the user chooses one of the list of Coupling_Order_Codes, this returns the ID for all rows that contains the Coupling_Order_Code. From these IDs I want a list box to show the data held in all of these rows, including the lookups to the associated tables.


Dim GvarCPLG_Ord As String

Private Sub cbCPLG_Ord_AfterUpdate()
GvarCPLG_Ord = Me.cbCPLG_Ord

Me.lstOther3.RowSource = "SELECT DISTINCT ALLENGPUMP.ID, ALLENGPUMP.DRW_ID, ALLENGPUMP.FW_No, ALLENGPUMP.FLANGE_MOUNTING, ALLENGPUMP.SHAFT_DETAILS, ALLENGPUMP.SHAFT_LENGTH FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """))"
Me.lstOther3.Requery
Me.lstOther4.RowSource = "SELECT DISTINCT ALLENGPUMP.ID, ALLENGPUMP.DRW_ID, ALLENGPUMP.COUPLING_REF, ALLENGPUMP.Coupling_Order_code, ALLENGPUMP.DRAWING_No FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """))"
Me.lstOther4.Requery
Me.lstOther5.RowSource = "SELECT ALLENGPUMP.ENGMOD_ID, ENGMOD.ENGINE_Model FROM ENGMOD INNER JOIN ALLENGPUMP ON ENGMOD.ID = ALLENGPUMP.ENGMOD_ID WHERE (((ALLENGPUMP.ID) = (SELECT ALLENGPUMP.ID FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """)))))"
Me.lstOther5.Requery
End Sub

Any help at all would be very, very, very much appreciated.
Thanks, Brodie

View 4 Replies View Related

Comparing Rows In A Query

Aug 26, 2006

is it possible to compare values in 2 different rows? for example if the query returns this:

1 | $5.00
2 | $6.00
3 | $20.00
4 | $30.00

i want to find out the cost difference between 1 and 2 which will be $6-$5 and then 2 and 3 which will be $20-$6 and so on. how can i make this happen in SQL? any suggestions?

View 6 Replies View Related

Query Help :copying Rows

Jan 30, 2007

Hi ,

I need to copy rows from a table to a specific location in the same table.

I suppose that it is possible to copy rows from a table to the same table
as followed :

INSERT INTO Table
SELECT * FROM Table
WHERE Condition

How can I insert into a specific location based on another condition. ?

Thanks for the help .

View 14 Replies View Related

Getting The Count Of Various Rows In One Query.

Oct 12, 2007

I've been toying with this for a few hours but I always end up with the wrong result. Hopefully an expert here can provide some insight. I have a table called colors with two columns PNAME and COLOR. There are various products and only three possible colors. E.g:

Tablename: COLORS

PNAME COLOR
-----------------------
tshirt blue
pants blue
jacket blue
jacket green
shoes green
vest black


Now what I'm trying to do is create a single query that would provide me with the count of colors in three different columns.
E.g

Blue Green Black
-----------------------
3 2 1

I can get these results easily by creating several queries and then getting the results into one query from these, but I'm trying to learn how to put together nested queries. Any help would be greatly appreciated.

View 12 Replies View Related

Merge Query Rows In A New One

Apr 15, 2008

Dear Friends,:)

I have 2 tables .. (GroupNo.) & (employee)

The conjunction between them as :

tbl1 tbl2
--------------------------
No. Name
--------------------------
1 John
1 Alian
1 Sali
2 Robert
2 ......
...
..
.
---------------------------

How Can I make a new Query that produce the result as :

1 John,Alian,Sali
2 Robert,.......
3 ......


Kind Regards,

View 7 Replies View Related

Repeating Rows In A Query?

Mar 27, 2014

I am attempting to perform a normalization process in a query where an organization would be operating in several different states and different countries. I have created three tables where: one table is Organization, second table is Country, and the third table is State. I made a table to record: Organization ID, Country ID and State ID. An organization could have 1 or more countries while having no US states. Also the organization could have 1 or more countries while having 1 or more US states.

What I am seeing is in my query is:

Organization | Country | State
Organization | Country | State
Organization | Country |

What I am wanting to see is:

Organization | Country, Country, Country | State, State

In the end, I want to make a mail merge from this query table.

How can I 'normalize' the query?

View 1 Replies View Related

Compare Several Hundred Rows Against Other Rows

Nov 21, 2005

Not sure if what I want to do is possible, or at least possible the way things are set up.

I have a massive table - c. 6 million rows. It contains data along these lines:

Plan#, Item, Price, Description, Colour, Value, Location, etc.

The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).

To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.

Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).

I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.

Any help or suggestions would be much appreciated.

Thanks!!!

View 3 Replies View Related

Query To Total Columns AND Rows

Jun 2, 2005

Is it possible to total columns in a query? Right now, I have a query that produces the following column counts, but I'd like to total Pending, Overdue, etc. This data is being displayed in a subform.

Process Pending Overdue Total
-------------------------------------
Engineering 1 2 3
Procurement 0 6 6
<etc>
-------------------------------------
TOTAL 1 8 9 <- this is the line I want to add

Here's what the query (qryStatusRptB) looks like thus far:
Field: Process
Table: tblProcesses
Total: Group By

Field: Pending
Table: qryStatusRptA
Total: Sum

Field: Overdue
Table: queryStatusRptA
Total: Sum

Field: TotalRecords: Count(*)
Table:
Total: Expression

Can someone explain how to get the TOTAL ROW in here? (I can do it via another query, but that won't work since the data is displayed in a subform. I've tried crosstabs without success.)

Many thanks,
Christine

View 2 Replies View Related

Query To Find Rows With Only 5 Characters

Aug 30, 2006

How do a write a query to find those rows in a field that have only 5 characters?

View 2 Replies View Related

Locking Specific Rows Within A Query

Oct 12, 2007

Hey guys,

I have a query on a form in which I want to lock the first two rows to prevent the user from manipulating the data. The user will input information in the third column.

How would I go about this

Thanks

View 5 Replies View Related

Number Rows In Access Query

Dec 15, 2004

Simple question - All I need to do is display the row numbers of a query I have created....For example:

1 St. Joes
2 Holy Family
3 St. Peters

I am not looking to rank anything, I just want a constant row of numbers for about 115 records.

Thanks So Much.

View 1 Replies View Related

Queries :: Counting Rows Within A Query

Apr 16, 2013

I am trying to get a total count of rows from a query on my tickets, Each ticket has a date and an ID number

Code:
SELECT tbl_ticket.ticket, tbl_ticket.entrydate, Count(tbl_ticket.[entrydate]) AS [Row Count]
FROM tbl_ticket
GROUP BY tbl_ticket.ticket, tbl_ticket.entrydate
HAVING (((tbl_ticket.entrydate) Between #1/1/2011# And #1/31/2011#));

I am trying to get this query built so I can attach it to a form.

I am trying to get three bits of data from this Query

1 a total count of all the Tickets in a given month

The ticket #'s and their date of entry.

So far this gives me a great list of Tickets and the date they got put in the system, but then for the total count it gives me 1 for each line. Even if some have the same date.

View 3 Replies View Related







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