Queries :: Combine Several Records In A Table Into One Record

Jan 22, 2014

How can I combine several records in a table into one record?

Suppose that I have a table like Table1 in the attached image.

Then I want to combine all records with the same value for Key1 in one record.

The result is shown in Table2 in the attached image.

I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?

Alternatively I could accept to turn to VBA that could do it. Any good links about this?

View Replies


ADVERTISEMENT

Queries :: Combine 4 Tables Data That Falls Within Dates - SQL Query Returns No Records

Oct 2, 2013

I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:

Code:
SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC
FROM [1733_All Print Orders]
WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013#
GROUP BY [1733_All Print Orders];[Application]

[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.

I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records

To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.

View 1 Replies View Related

Queries :: Access Split Zeros When Combine Fields Of A Table

Apr 25, 2014

I have a table "MansComps" which includes two fields, the "Index" field which contains a number, e.g "5" and the "Tabletype" field which contains a letter e.g "T". i have created a query based on this table, which combines these two fields and displays the result to a third field which I call "Components".

field: Index
1, 2, 3,....

field: Tabletype
T

field: "Component" has to be like this:
T00001, T00002,...

I have managed to combine the fields in my query using the expression:

Component: [Tabletype] & " " & [Index]

but the results appear without the leading zeros, like T1, T2..I have set my Index content to be a number and as a primary key and the Tabletype as a text. I also tried to change the format of Index to "00000" which works for the Index field separately but not for the Component field.Should I do something in the format of the Component field?

View 4 Replies View Related

Queries :: Query Multiple Records Between (Weeks) Where No Record In Child Table?

Jul 1, 2014

I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:

clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty

Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:

noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2

I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:

fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......

I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.

View 5 Replies View Related

Tables :: How To Combine Records Of Three Tables Into One Table

Jul 30, 2013

I have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.

View 3 Replies View Related

Queries :: List Of Dates And Records With No Matching Record OR Existing Record With Higher Date

Nov 24, 2014

I've been asked to get some information from my database and I'm a bit stuck.

I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field

My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.

So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)

I can manage a query that looks at a certain date that it prompts for on each run:

Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));

I would like a query that lists all dates in a range, and shows the same information for each day listed.

View 9 Replies View Related

Combine Records

Oct 26, 2005

I need to know how to combine two records. What I have is a text file that is imported into a table. The problem is, the text file has 4 fields on one line then 3 fields on the next line. Is there a way to combine these two lines into one record? I do not want to do this in the text file. I want to import the file and run some code to combine the two records into one record, delete the second line, and go to the next two records. What can I do? Sorry for any spelling but I have to run. Thanks for any help.

View 1 Replies View Related

Help Combine Records

Dec 19, 2007

Hi,all
There are 3 records of my table:
DID DNom DBr DF1 DF2 DF3 DF4 DF5 DF6 DF7 DF8 DF9 DF10
38 103 1012 22 2133 33
39 103 7 9 99
40 103 10 20

/"DBr" shows how fields should fill/

I want combine these 3 records to one record. What i need to do ?
DID DNom DBr DF1 DF2 DF3 DF4 DF5 DF6 DF7 DF8 DF9 DF10
38 103 1012 22 2133 33 7 9 99 10 20
thx

View 6 Replies View Related

Combine Two Records Into One

Jul 21, 2005

I have two records:

------------------------------------
field1 | field2 | field 3 | field4 |
---------------------------------
A | 1.1 | 1 | |
---------------------------------
A | 1.1 | | 2 |
---------------------------------

Is there any way to combine to records into one like that

------------------------------
field1 | field2 | field 3 | field4 |
------------------------------
A | 1.1 | 1 | 2 |
------------------------------

Thanks in advance for any ideas

View 1 Replies View Related

Combine Records - Please Help Me

Dec 20, 2006

Hello Everybody

I am trying to set up a query for a table which has following 3 fields
GroupNo
Name
Points

which has values in the following manner

GroupNo-Name-Points
204-------Jack---- 20
204-------Ryan---40
204-------Tita-----30

202-------Jack----35
202-------Ryan----24

205-------Jack-----56
205-------Ryan----73
205-------Tita------45

Is it possible to combine the above records by the GroupNo in the following manner with 7 different fields now?

GroupNo---Name1---Points1---Name2---Points2---Name3---Points3
204---------Jack------20---------Ryan-----40----Tita------30
202---------Jack------35---------Ryan-----24
205---------Jack------56---------Ryan-----73----Tita------45

Any help in this regard will he highly appreciated.

Thanks

View 5 Replies View Related

Queries :: Query That Will Return Records From A Table That Have Related Records In Another Table

Mar 4, 2015

I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.

View 3 Replies View Related

Combine Records With Same Project Name

Nov 22, 2005

I have a DB table that contains two fields: Project and Minutes. The time tracking software records each time I punch in or out under a certain project but records multiple instances for instance:

http://web.cortland.edu/leonardl/access_pro_min_tbl.gif

I want to combine the records that have the same project. Maybe a "make table query"? I am not sure. If anyone could offer any assistance I would be very greatful.

Thanks!

View 4 Replies View Related

Queries :: Combine Two Queries Result Of Second Query In Another Column

Mar 15, 2014

i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.

First Query

SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName;

Second Query

SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN
HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));

View 2 Replies View Related

Combine Fields Into One Record

Apr 24, 2007

Hi,

I have a table that has records with some data that is duplicated and some that is not. For example,

ID start_date finish_date customer
2 2-1-07 3-3-07 Jane Smith
2 3-7-07 4-30-07 Joe Shmoe

I would it to end up looking like this:
ID start_date finish_date customer1 Customer2
2 2-1-07 4-30-07 Jane Smith Joe Shmoe

With the start_date being the Min of both start_dates and the finish_date being the Max of both finish_dates.

Is this possible?

Thanks in advance!

View 3 Replies View Related

General :: Taking Two Records And Combine Them Into One

Jul 20, 2012

So I have two tables. One table "tbl_SRVASSET" lists out servername, serialnumber and asset tag. Second table "tbl_SRVNIC" list out the same servername, IPAddress and IPAddressTypeDesc.

The issue is that I have records in the "tbl_SRVNIC" with the same server listed numerous times (because it has 2 or 3 NIC's). So there is obviously a one to many relationship betweeen the two tables (common I guess). What I'm trying to do is be able to take the servers in the "tbl_SRVNIC" table and a new table or create a query with a single record for that server and have all 2, 3, 4 or maybe more IPAddress's assigned to them listed in the same record. This is what I'm having trouble with...

The end result is that after I've got this new table that lists only a single server and it's associated NIC's in the same record I can then combine that data with the "tbl_SRVASSET" via a basic query so my end result is a brand new table "tbl_SRVDETAIL" that looks like this:
SERVERNAME, SERIALNUBMER, ASSET, SERVER NIC1 IPADDRESS, SERVER NIC1 IPADDRESSTYPE DESC, SERVER NIC2 IPADDRESS, SERVER NIC2 IPADDRESSTYPE DESC, SERVER NIC3 IPADDRESS, SERVER NIC3 IPADDRESSTYPE DESC, (and maybe more columns if there are more, but that is a variable as some servers have 2 NIC's, Some with 3 or more so...).

View 1 Replies View Related

Queries :: Retrieve Records From One Table Where Dates Are Between Records In Another Table

Dec 30, 2013

I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.

ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.

I do have a history table set up like:
tblEmployeeHistory
ID (PK)
EmployeeID (FK to Employee table)
ManagerID (FK to Manager table)
MoveDate (date employee assigned to manager)

The Employee table is set up like:
Employee ID (PK)
EmployeeName
ManagerID (FK to Manager table)

The Manager table has the ManagerID and ManagerName.

That's the employee side of things; then I have the tables that store the audit results:

tblAudit
AuditID
FileNumber
AuditDate
EmployeeID (FK to employee table)

tblAuditResults
AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions)
Answer

How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?

View 14 Replies View Related

General :: Combine More Records In One Using Comma Delimited

Jul 20, 2012

I'm a new Access user. I'm looking for an easiest solution to combine more records into one, using comma delimited. Let's say that I have a table T1 with two columns Code and Client like this:

Code Client
X C1
X C2
Y C1
Y C3
Z C1
Z C2
Z C4
Z C5

I need to see those records in a Query, grouped by "Code" with the Client's value combined like this:

Code New
X C1, C2
Y C1, C3
Z C2, C4, C5

View 3 Replies View Related

Combine / Concatenate Multiple Values In Different Records That Have Same ID

Mar 13, 2012

I have to concatenate the data in multiple records into one record. They have "skus" associated with them.

I have two columns.

ColumnSku: Which contains a product sku
ColumnModel: Which contains a model numbers

ColumnSku can contain the same sku hundreds of times
ColumnModel can contain the same model several times but not for the same sku

What I need to do is this: For every time a sku is shown in ColumnSku, take the model in ColumnModel and join them together separated by a comma.

For example

ColumnSku|ColumnModel
SKU1111|Model11111
SKU1111|Model22222
SKU1111|Model33333
SKU1111|Model44444
SKU1111|Model55555
SKU9999|ModelHHHHH
SKU9999|ModelJJJJJ
SKU9999|ModelMMMMM

Would end up like this
ColumnSku|ColumnModel
SKU1111|Model11111,Model22222,Model33333,Model44444,Model5 5555
SKU9999|ModelHHHHH,ModelJJJJJ,ModelMMMMM

How do I do this?

View 1 Replies View Related

Queries :: How To Do Combine Two Different Queries In UNION Query

Oct 28, 2013

Am trying many times in UNION query but its not working because there is different field names and only some fields are matched. So I need to both query's are combine in 1 query.

They Quays details:

1.Customer Credit Transaction1
2.Customer Credit Transaction2

Above both queries are already combined in UNION query as (Customer Credit Transaction Final) its not a problem.So now I need to do combine the above Union query Customer Credit Transaction Final & ReceiptformQry.

The both query details:

Customer Credit Transaction Final
SELECT BillBook1.TID, BillBook1.BILLNo, BillBook1.BILLDate, BillBook1.Customer, BillBook1.BillMode, [BillBook1 Vat Details].[TOTAL Rs] AS [CC Amount]
FROM BillBook1 INNER JOIN [BillBook1 Vat Details] ON BillBook1.TID = [BillBook1 Vat Details].TID
WHERE (((BillBook1.BillMode)="Credit"));
UNION ALL

[code]...

Above fields are need to merge in Union query or otherwise. and remaining fields are needed to show separately.

View 6 Replies View Related

General :: How To Combine Four Fields Into One Field In Same Record

Jul 9, 2014

Using Windows 7,
Access 2010.

Is there a way to combine four fields into one field in the same record? I am trying to build Equipment Code field by using the abbreviated values of Category, Item, Type and Dimension fields.

Example: if Category=Storage System (SS), Item=Wire Deck (WD), Type=Flared (F) Dim=42x52 are the user selected values the corresponding equipment code field would be: SS-WD-F-42x51 This is done in order for all users of the database to build a proper code in the correct format.

What I have so far: I have the four fields and the equipment code field setup in a form, the equipment code text box has a control source of: =[Category] & "-" & [Item] & "-" & [Type] But when I select values of the four fields and the equipment code is populated I try to save the record and I get an error stating that the Equipment code field needs to be filled out. Three fields (Category, Item, and Type) are look-ups from other tables and Dimension is typed in.

View 14 Replies View Related

Combine Queries

Jun 8, 2005

Hi,

I have a problem to solve,

Table1:InitialMeasurement
NoQuantity
112
234
345

Table2:FinalMeasurement
NoQuantity
145
377
4 67

Table3:CombinedMeasurement
IndexInitialFinal
11245
2340
34577
4 0 67

how can I join table1 and table2 to table3?

Many thanks,
Galantis

View 1 Replies View Related

Combine Two Sql Queries - See Example

Jul 11, 2006

I have the following two queries

Query1:
SELECT TAGS.ID, ALARMS.*
FROM TAGS RIGHT JOIN ALARMS ON TAGS.NAME1=ALARMS.NAME1;

Query2:
SELECT SUBGROUPS.*, Query1.*
FROM SUBGROUPS LEFT JOIN Query1 ON SUBGROUPS.ID=Query1.ID;


How do I combine these to make one?

View 4 Replies View Related

How To Combine 2 Queries?

Sep 29, 2006

I have the following 2 queries:

SELECT DISTINCTROW tbl_members.surname, Count(tbl_years.year) AS CountOfyear
FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member
GROUP BY tbl_subscriptions.subscription_fee, tbl_members.surname
HAVING (((tbl_subscriptions.subscription_fee)=0));

This query displays a list with the surname of the member and the Count of the Years he/she did not pay the annual subscription, hence where subscription_fee = 0

TRANSFORM Sum(tbl_subscriptions.subscription_fee) AS SumOfsubscription_fee
SELECT tbl_members.surname, tbl_members.name, tbl_members.mobilephone
FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member
WHERE (((tbl_years.year)>Year(Date())-"6"))
GROUP BY tbl_members.surname, tbl_members.name, tbl_members.mobilephone
PIVOT tbl_years.year;

This query displays a list with the surname, name, mobile phone of the member along with the money he/she paid for the last 5 years as you can see from
WHERE (((tbl_years.year)>Year(Date())-"6"))

My question is: is it possible to combine those 2 lists and have one where all of the following columns will be listed?
Surname, Name, Mobilephone, Count of years with 0 payment, a column for each year of the last 5

Thanks in advance.

View 1 Replies View Related

Possible To Combine Two Queries?

Jun 21, 2007

I have two queries that I am interested in combining into one if possible. I'm trying to learn Access and SQL on-the-fly, so feel free to point out any noob mistakes I am making.

The first query simply pulls certain records from a table:

SELECT Sensor5.LaneName, Sensor5.SensorTime, Sensor5.Speed, Sensor5.Volume
FROM Sensor5
WHERE (((Sensor5.LaneName)="NB1" Or (Sensor5.LaneName)="NB2" Or (Sensor5.LaneName)="NB3") AND ((Sensor5.Volume)>0) AND ((Sensor5.SensorDate)="4/17/2007" Or (Sensor5.SensorDate)="4/18/2007" Or (Sensor5.SensorDate)="4/19/2007" Or (Sensor5.SensorDate)="4/20/2007" Or (Sensor5.SensorDate)="4/23/2007" Or (Sensor5.SensorDate)="4/24/2007" Or (Sensor5.SensorDate)="4/25/2007" Or (Sensor5.SensorDate)="4/26/2007" Or (Sensor5.SensorDate)="4/27/2007"));

The second query then takes averages and sums from this first query, grouping by a third field (SensorTime). This results in weeks of data being compiled into a single record for each time interval in a 24-hour period. See below:

SELECT SpeedWeekday5NB.SensorTime, Avg(SpeedWeekday5NB.Speed) AS AvgSpeed, Sum(SpeedWeekday5NB.Volume) AS SumVolume
FROM SpeedWeekday5NB
GROUP BY SpeedWeekday5NB.SensorTime;

Is there any way I can streamline this process by combining the two queries into a more complex single query, or should I leave things as-is? Any advice is much appreciated!

View 5 Replies View Related

Combine Two Queries

Aug 3, 2007

I would like to take these two queries and combine them into one if possible. This is the first query:

SELECT DISTINCT [LINE 2].[CASE ID] INTO [TABLE 1]
FROM [LINE 2] INNER JOIN NOLDBA_OBLIGATION ON [LINE 2].[CASE ID]=NOLDBA_OBLIGATION.ID_CASE
WHERE (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0) AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>#6/30/2007#) AND ((NOLDBA_OBLIGATION.DT_END_VALIDITY)=#12/31/9999#));

And this is the second query which is based on the results of the first query:

SELECT NOLDBA_CASE_ROLLUP.ID_CASE INTO [TABLE 2]
FROM [LINE 2] INNER JOIN (NOLDBA_CASE_ROLLUP LEFT JOIN [TABLE 1] ON NOLDBA_CASE_ROLLUP.ID_CASE = [TABLE 1].[CASE ID]) ON [LINE 2].[CASE ID] = NOLDBA_CASE_ROLLUP.ID_CASE
WHERE ((([TABLE 1].[CASE ID]) Is Null) AND (([NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]-[NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID])>0))
GROUP BY NOLDBA_CASE_ROLLUP.ID_CASE;

Can this be done and if yes can someone show me how? Thanks

View 4 Replies View Related

Combine Queries

Aug 7, 2007

me again...

is it possible to combine two qeuries and run at the same time?

query1
ALTER TABLE customerDETAILS
ADD COLUMN ACCOUNT TEXT(16)

query2
UPDATE customerDETAILS
SET customerDETAILS.ACCOUNT = customerACCOUNT.ACCOUNT_NR
where............

how do i create a column in the Details table and then update it to the ACCOUNT_nr in the Accounts table, at the same time?

regards
B

View 2 Replies View Related







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