Combine
Sep 27, 2007
I have a question that I don't know how to really explain, so here is an example of the table I have:
35510157.32
355102267.44
35510372
The first column (35) is Employee ID.
The second column (5101) represents a certain time code.
And the Last Column (57.32) represents an amount of time.
I am trying to create a query that puts the data like this:
3557.32267.4472
There is about 3500 different Employee ID's and each ID can have up to 10 different time codes. Is there a way of doing this without doing a Make Table or Update query?
View Replies
ADVERTISEMENT
Jul 18, 2006
My application links to 3 mdb (eg. 1a.mdb, 2b.mdb, 3c.mdb) backend database.
It works as the following: if an order is placed by a company (public sale), the order will be stored in 1a.mdb, if the order is for private sale, the order will be stored in 2b.mdb. The items for public sale are stored in 1a.mdb, the items for private sale are stored in 2b.mdb. It means an order cannot combine both public and private sales, but in fact this always an issue. 3c.mdb is used for storing common data, such as customer details.
Now I would like to create an order that includes both public and private sales. How I can do that? Do I have to combine 1a.mdb and 2b.mdb into 1 database?
Any suggestion is welcome.
View 2 Replies
View Related
Jun 19, 2007
I was wondering how to go about combining both Access and SQL together.
Am I going to write the SQL codes in VB and then Access will know how to combine the tables I've created with the SQL codes?
Thanks in advance!!
Jason
View 7 Replies
View Related
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
Apr 26, 2007
Hello,
I have around 40 tables right now on hand and I would like to combine them into one big table (a table, not table formed by query) and I am wondering is there a easy way of getting it done without me physically copying and pasting all 40 tables? Thanks!
regards,
Anyi
View 3 Replies
View Related
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
Feb 4, 2008
Hi I have Two tables with identical field names with information reagarding a physical catalogue from one site and a catalogue from another site I would like to join them utilizing there manufactorer code. And ignore all the duplicates.
Any assistance greatly appreciated.
View 2 Replies
View Related
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
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
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
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
Nov 14, 2006
Hi
I have a confusing situation. I have a need of creating a combobox which displays records from different tables.
Eg.
I have a table called "house parts" and filled with records like
room, hall, garage
Secondly i have a table called "Parts" and filled with
floor, ceiling, lamp, window etc.
As u see, "house parts" could consist of "Parts" like "room" could have floor, ceiling etc.
I need to create a query that shows me all the records from "house parts" and also "Parts" in the same list.
Can anyone help me please
View 4 Replies
View Related
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
Feb 23, 2007
NEVER MIND--- I FIGURED IT OUT:o
I have a table with two columns.
FIRST LAST
James Jones
R Kevin Foster
When I use this:
Left(FIRST,1) & " " & LAST AS Expr1
I get: James Jones
R Foster
Is there a way to correct this so R Foster comes back as R Kevin Foster?
View 2 Replies
View Related
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
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
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
Oct 8, 2007
I run queries against an SQL database where I have read only access. This database has the following fields
EventNumber
BriefDescription
DetailDescription
EventCode
CauseCode
SignificanceCode
Resolution
When users enter one of the 3 "Codes", they can enter as many as are appropriate. So an event may have 3 EventCodes, 2 CauseCodes and 2 SignificanceCodes for example.
When I run my query, I get a different record for each. So for the above scenario, I may get 12 records. Problem is I only want one record for this event. Using a query, how can I combine all the EventCodes together and the CauseCodes together and the SignificanceCodes together...maybe separated by a space or comma? If I have to copy the data down locally, that's okay. I am wondering if an Update query could be used somehow, but I am not sure how to do this.
Any help is appreciated.
Jim
View 10 Replies
View Related
Dec 14, 2007
Hi,
I am using SQL Server 2005
I try this
select firstname & lastname, id from list
I get error,it seems '&' is wrong.
Please let me know how to combine the two fields
Thanks
Mark
View 2 Replies
View Related
Dec 28, 2007
I'm having problems figuring out how to "Combine" two different queries.
I have the following two queries that I've written:
SELECT DISTINCTRecordType, InvoiceNumber, DeviceNumber, ChargeCode FROM tblDetail ORDER BY DeviceNumber, ChargeCode;
Select COUNT(CallDesc), SUM(TtlCallChgs) AS TotalUsageCharge FROM tblDetail Group By DeviceNumber;
How can I combine these two records that I get a count and a sum added to the first query.
Thanks,
CR Junk
View 2 Replies
View Related
Apr 10, 2008
Is there a way to combine queries? Or combine two charts into one?
Thanks.
View 3 Replies
View Related
Apr 21, 2008
Hi~! I'm new in access vba and sql... hopefully my question won't be too dumb.
Basically i'm working on a report... which the report data/information was calculated in few different queries(which i created) from few different tables. in simple, each of the data/information was from a query... i got about 10+ data/information to display, that's why i created 10+ queries.
I done all the calculation in queries, but my problem is... how to add all these queries into 1, so i can directly create a report from the 1 query.
most of the queries was using union all/union... i tried to use union/union all to put all these queries together, but it won't works... because each of the queries using different tables/fields... even though i add those table/fields into the queries... it didn't work as well...
View 4 Replies
View Related
Jun 19, 2006
how would I combine the following three queries into one. want to replace three different queries with one, not add another.
Code:SELECT DISTINCT DeliverableDescription.TaskName, RawData.OutlineNumber2FROM DeliverableDescription INNER JOIN RawData ON DeliverableDescription.OutlineNumber = RawData.OutlineNumber2;
Code:SELECT DISTINCT Team.TeamLeadName, RawData.OutlineNumber2FROM RawData LEFT JOIN Team ON RawData.TeamLeadNumber = Team.TeamLeadNumber;
Code:SELECT RawData.OutlineNumber2, FormatPercent(Avg((RawData.Finish-RawData.Start)/(SELECT SUM(RawData.Finish - RawData.Start) FROM RawData))) AS DaysSpannedProgressFROM RawDataGROUP BY RawData.OutlineNumber2;
the output of query 1 is
TaskName | Outlinenumber2
the output of query 2 is
TeamLeadName | Outlinenumber2
and the output of query 3 is
Outlinenumber2 | DaysSpannedProgress
I would like to have one query that has all that info...
TeamLeadName | Outlinenumber2 | TaskName | DaysSpannedProgress
any ideas?
View 3 Replies
View Related
Sep 6, 2007
I have ONE table with data that looks like:
Username1, Username2, Date, EventType, Money (basically)
I have a query that combines all the Username1 entries together. It sums the money, and COUNTS the times it was entered into the database.
I have another query that does the same, but uses Username2 as the main filter.
The reason I have the two queries, is that not every entry in the main table has a Username2 in it. But I want to make sure I include those entries that have a Username2 in the totals and counts.
Im not sure if this makes sense to anyone, but if there is a way to combine the two queries I have, it would help to have some guidance. I don't have a clue how to do it in ASP (if possible) and don't know what to try inside ACCESS as I experiemnt around.
Thanks for any help or guidance. I am quite the newbie to ASP scripting. Thanks again.
View 3 Replies
View Related
Nov 4, 2007
I'd appreciate any ideas on how to combine the following code into 1 query, without "If ... Then ... Else":
If Now() >= TransitionStartDate And Now() <= TransitionEndDate Then
DoCmd.RunSQL "SELECT [Contractors Trained Query].LastName, [Contractors Trained Query].FirstName, [Contractors Trained Query].CompanyName FROM [Contractors Trained Query] WHERE [Employees Trained List].[This Years Training Date] Is Not Null OR [Employees Trained List].[Last Years Training Date] Is Not Null ORDER BY [Contractors Trained Query].LastName;”
Else
DoCmd.RunSQL "SELECT [Contractors Trained Query].LastName, [Contractors Trained Query].FirstName, [Contractors Trained Query].CompanyName FROM [Contractors Trained Query] WHERE [Employees Trained List].[This Years Training Date] Is Not Null ORDER BY [Contractors Trained Query].LastName;”
End If
Thanks ....
View 3 Replies
View Related
Sep 17, 2012
I want to combine 3 tables into one table. Each table one after the other. So one table may have 300 records so the next table will have its first record on line 301. Fields are all the same throughout each table.
View 3 Replies
View Related