Queries :: How To Reduce Down Data Set To Get A List Of All Unique Combinations Of Column

Jul 8, 2014

I am new to Access and am trying to reduce a dataset I am working with to make it managable for Excel. I have three columns which have three unique parameters and one column with numberical content.

Column A Column B Column C Column D
NJ Red Monday 10
NJ Red Tuesday 20
NJ Yellow Wednesday 30
NY Red Monday 35
NY Green Tuesday 40
NY Green Wednesday 60

I want to elimiate column C from the dataset, and sum column D for every unique combination of Column A & Column B. I have figured out how to reduce down the dataset to get a list of all unique combinations of Column A&B,what formula I can use in a query to sum column D on my table for every unique combination of column A&B. This is a simple SUMIF in excel, but my dataset is too large for excel.

View Replies


ADVERTISEMENT

Queries :: Manifest - Query A List And Then Show Only Unique Values

Oct 1, 2013

One Manifest can have many line items. One line item can only have 1 designation. Each manifest may have many line item with same designations.

For example:
Manifest #0001
Line Item 1: N
Line Item 2: H
Line Item 3: U
Line Item 4: N
Line Item 5: P
Line Item 6: H

Table set-up
CurrentCY
CurrentCYIDPK
WasteCategoryIDFK
LineItemInformation [Line Item 1, Line Item 2, etc]
ManifestDataIDFK

ManifestData
ManifestDataIDPK
ManifestNumber [0001]

WasteCategory
WasteCategoryIDPK
WasteCategory [H, N, U, etc]

I'd like to query the line items for each manifest so the end result has the manifest number (Manifest #0001) in a field and the designations (N, H, P, U) in fields on a report.

View 14 Replies View Related

Queries :: Job Scheduling Database - Output Unique List Of Weeks

Sep 1, 2014

I'm writing a simple job scheduling database, so I've got resource availability (by week) and job list (by week). I've written a single-column query whose only output is a unique list of weeks with resources available, and another single-column query whose only output is a unique list of weeks with at least one job allocated.

How do I join these so that I can get a single-column list of weeks that have either (or both) of resources and jobs?

So Query1 returns:
25/08/2014
08/09/2014
15/09/2014

while Query2 returns:
01/09/2014
08/09/2014
15/09/2014
22/09/2014

I want query3 to return:
25/08/2014
01/09/2014
08/09/2014
15/09/2014
22/09/2014

View 5 Replies View Related

Queries :: Run A Simple Update Query To Copy Data From One Column To Another Column

Sep 24, 2013

I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.

View 7 Replies View Related

8 Table Need To Design Queries For All Possible Combinations...

May 21, 2007

Hi,

HELP!

I have 8 tables all with a membership number as the unique identifier, I need to find all the possible combinations and then create queries for each combination, is there a quick and easy way to do this? or do I have to sit and define a query for each of the 250ish combinations?!

I'm pulling all the data together using a union query. What I need to do now is to find the combinations using either "is null" or "is not null" criteria (in the queries).

Regards
H.

View 14 Replies View Related

Queries :: How To Reduce Number Of Item With Update

Nov 20, 2014

How do you update a table by reducing a number by 10?

My assignment question is:

10 students have left GY101. Write an SQL UPDATE statement to reduce the class size by 10 for all modules taken by GY101 students.

I can display the students who take GY101 with the following code

SELECT moduleCode, classSize
FROM ROOM_BOOKING1
WHERE moduleCode IN (SELECT modCode
FROM STUDENT_REG1
WHERE sID IN (SELECT id
FROM STUDENT
WHERE courseCode = "GY101"));

But how to reduce the number by 10.

View 1 Replies View Related

Queries :: Unique Data In Access Table

Feb 21, 2015

I Have one table which have 4 column ,i.eID , NAME, PAN, DOB..in this table same id having diffrent data of Name or PAN or DOB except DOB all are text format when i m trying to select unique id which having more than 1 name it shows 0 data, but table having these,,

View 2 Replies View Related

General :: Limited Size For List Box Column To Show The Data?

Feb 26, 2013

The column in my list-box did not show all the text i save in the table field. is there any limited size for list-box's column to show the data ?

View 2 Replies View Related

Queries :: Merge Unique Data With Same Persons Record

Jun 14, 2013

As mentioned before on a different topic I am building a database to sort through publication authors. I currently have the table sorted in a way that only the main author is listed for the 3000 records. Now I need a way to remove duplicate entries on a new table. Each entry is a different publication. Each record has a DOI code that is unique and an author which might be a duplicate. I want a new table listed by author once and a new field that list all of the DOI numbers for that author.

I know how to do a query to show only the unique names, but how to I also get the database to combine the records that share the same author.?

View 7 Replies View Related

General :: Changing Column Colour In List Box - Alignment For Cells Data

Jan 7, 2014

How can change column color in list box and also how to make alignment left or right for cells data.

View 1 Replies View Related

General :: Can't Change Listbox Column Header Name When Data Source Is A Share-point List

Jul 30, 2013

The following code works to change the column header name for a listbox in form view when the data source is a local table, but not when the data comes from a sharepoint list.

Code:
sqlstatement = "SELECT ID, PONum as [PO Number], ActDate as [Date], VendorName as [Vendor Name], Service, BuildingNumber as [Building Number], ReservationDescription as [Description], POAmount as [Amount], QuoteType as [Type of Quote], Comments" & _
" FROM ActivityLog" & _
" WHERE (Activity = 'AcceptReservation') AND (PSCName = '" & Me.PSCCombo4.Column(0) & "')" & _
" ORDER BY ActDate;"
'MsgBox sqlstatement
Me.EditPOListBox.RowSource = sqlstatement
sqlstatement = ""

Also it appears that there is no such thing as a caption property for a sharepoint list column.

View 3 Replies View Related

Forms :: List Boxes - Single Column Versus Multi Column

Apr 3, 2013

How do I select the first column of a multi-column list box (called "List1") for a query.

A single column list box works fine.

Code:

SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;

View 6 Replies View Related

Unique Column In Table

Apr 2, 2008

Hai all,


i am creating one table like CREATE TABLE XX( CODE NUMBER, NAME VARCHAR(10).

I AM INSERTING THE 5 RECORDS LIKE

OUTPUT IS
--------------

CODE NAME
1 A
2 B
3 C
4 D
2 A

problem is There is no unique column in that table.

is there any unique column in ACCESS. like

for Exp: in SQLBASE is ROWID is unique column.
in ORACLE is ROWNUM is unique column.


pls help

Thanks & Regards

Narasareddy

View 2 Replies View Related

Populate A Column With Unique ID

Nov 22, 2011

I have a table with about 700 records in it that I import. In my code I am using Alter table to add a column called BlID. I want to populate each record with a unique number. Because of other things that are happening I cannot use auto number, so I am using the date (with the date, hours, min, and seconds). My problem is that I cannot think of a ways to loop through this and insert a the date and time in each record.

I am using MS Access 2007.

View 6 Replies View Related

No Unique Column In A Database

Sep 28, 2014

It may be that there is no unique column in a database. Then it will not be possible to create a Primary key based on a single column.In that situation a COMPOUND KEY has to be prepared by combining two or more columns.how this COMPOUND KEY is prepared.

View 2 Replies View Related

Queries :: How To Retrieve Column Data Other Than Specified In 2 Sql Queries

Apr 14, 2014

I have a table (tbl1) contains sample records in Basecode column like S2378797 , R1165778 , W1165778 , N1165778

Description EID Basecode
----------- ---- ---------
ssdad 1001 S2378797
gfd 1002 S1164478
gfdsffsdf 1003 R1165778
ssdad 1004 M0007867
gfd 1005 N7765111

[code]....

And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query

Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));

How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this

Description EID Basecode
----------- ---- ---------
ssdad 1001 2378797
gfd 1002 1164478
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967

Third query not working

Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (IsNumeric(Left(Base,1)) AND Left(Base,1) Not In ("W","N"))
AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));

View 2 Replies View Related

General :: Unique Values For Two Column Combobox

Jul 18, 2013

Here's my dilemma: I have a table that keeps track of job postings and how many people applied for each posting. So, for instance, I can have 20 people apply for a job posting say....on July 18. On another column I have the shift name for that posting such as DAY Shift. So, I have 20 people that have applied for the Day Shift on July 18.

What I'd like my combobox to show me in just one instance of July 18 in column 1 and one instance of Day Shift in column two. I have tried using the wizard and then going to the properties of the combobox and attempted to edit the SQL statement to use the DISTINCT value to no avail.

View 14 Replies View Related

Created List Box With One Column - Display All Characters Of List Item

Jan 2, 2014

I have created listbox with one column (contains one column only), now i would like to display all the characters of list item (want scroll bar to listbox).

How do i display all text of list item, I have already fixed Column Widths to max length (22";0.1"). However when scrolling to right, it is going to next blank column of list box, which is created only to change Column Widths property.

View 12 Replies View Related

Queries :: Data From Rows To Column

Apr 6, 2014

I need to change rows data into column.

I have a table name as Temp_Report1 with fields Lab_No, Test Name, Reslt, Test_master

Lab_no
Test Name
Result
Test_Master
4.414
Hemoglobin
00
HAEMATOLOGY
4.414
Red Cells
01
HAEMATOLOGY
4.414
E.S.R
02
HAEMATOLOGY
4.414
Reticulocytes
03
HAEMATOLOGY
etc

The another table where I want to copy data is New_repo2 with fields Lab_no, v1, v2, v3 etc. Need as

Lab_No
V1
V2
V3
V4
V5...
4.414
Hemoglobin
Red Cells
E.S.R
Reticulocytes
Etc

How to change this from rows to one column.

View 1 Replies View Related

Queries :: Copy Data From One Column Into Another Of Different Table?

Jun 9, 2013

I have two tables pertaining to the same database.

Table [Purchase_Order_Details] amongst many fields have Columns - (Purchase Order #) which is the Primary Key and (Work Order #)

My second table [Order Details] also has a column (Work Order #) . Now I have added another column to this table i.e. (PurchaseOrderNo) - this column is blank as of now.

However I would like to run a query to fill the (PurchaseOrderNo) column depending upon the (Work Order #) which is present both the tables.

View 14 Replies View Related

Queries :: Get SQL From Data On Multi Column Listbox

Oct 22, 2013

I wish to generate a new query consisting rows that I have selected in a multi colum list box. May I know if it is possible to work? I am totally lost now

View 10 Replies View Related

Queries :: Retrieve Column Name When Data Is Null

Oct 31, 2013

I have a table in which i want a column name as output when the data in it is null.

View 2 Replies View Related

Queries :: How To Retrieve Column Data Using SQL Query

Apr 14, 2014

I'm trying to retrieve data based on the contents of one column.

Sample table

Code:
Description EID Basecode
----------- ---- ---------
ssdad 1001 S2378797
gfd 1002 S1164478
gfdsffsdf 1003 R1165778
ssdad 1004 M0007867
gfd 1005 N7765111
gfdsffsdf 1006 W5464111
gfd 1005 N7765111
gfdsffsdf 1006 A4000011
gfdsffsdf 1006 W5464111
ssdad 1001 2378797
gfd 1002 1164478
ssdad 1001 965000
gfd 1002 780000
yjgk 4456 540000
kjhkh 2009 150000
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967

I'm retrieving all **Basecode** column data starts with only letters other than 'W', 'N' by this query

Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE Not
IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");

And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query

Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));

How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this

Code:
Description EID Basecode
----------- ---- ---------

ssdad 1001 2378797
gfd 1002 1164478
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967

Third query not working

Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N"))
AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));

View 5 Replies View Related

Combine Two Columns To Display Information In Third Column As Unique Identifier

Jul 23, 2015

So i have a access database with a main field that we can call vendor # (LIFNR) and another called Company code (BUKRS). There are multiple company codes under a single vendor #. Example:

LIFNR
BUKRS

0000010535
1010

0000010535
5060

0000010535
5610

0000010536
1010

0000010536
5060

0000010536
5610

What I am trying to do is create a 3rd column where i can have a unique row for each of these fields without it repeating. There is no unique identifier in this table and that is what i am trying to achieve.

View 3 Replies View Related

Queries :: Single Row Data Required As Column Header

Nov 4, 2013

Is there any way I can use data in the row as a column header ? I only have 1 row of data which i want to use as a header to I can link it to other tables/queries.

(Access 2007 query)

View 1 Replies View Related

Queries :: Find A Way For Access To Find Unique Dates And Unique Names?

Aug 1, 2014

I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:

Start Date/Time
End Date/Time
Employee

At the moment this is what the format of my report looks like (I removed other unnecessary fields):

StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe

I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?

View 2 Replies View Related







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