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
Hi all
I have two tables I need to merge them in one query.
The problem is that I need to have all the records in the new table without dublication. How can I do that?
Regards,
dallagmm
I have two reports that I import into Excel.They both show the locations in the warehouse where our product is supposed to be located. Over years of lack of maintenance they no longer match.What I would like to do is link these two sheets in Access so that the information can be updated as I progress and correct the information.Both have a common row or field called Locations. The problem is that some of the locations doesn't exist in both of the sheets. One of the sheets also has duplicated records for the same location.
I assume that I need some type of query to accomplish this; I have copied and pasted some samples of the information in the spreadsheets as well as the results that I am looking for.
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?
My goal in Excel was to combine them all into a single Pivot Table which I did. The issue is everytime I add a sheet I need to redo the Pivot Table, which would be a hassle as I will continually be adding sheets. I'm hoping that there is an easier way this could be done using Access. The individual sheets are emailed to me and then I put them into one Excel file, is there a way to take that Excel file with all the sheets and import it to Access and be able to sort the data as I would with a Pivot Table in Excel?
View 4 Replies View RelatedI have multiple tables that are created from some software that puts them each into the access database. each table is one year of data. the software cannot put all years into one table because of memory. I want to combine these tables into one. In addition, I need to do some manipulatins: the dates come over as MM-DD-YYYY strings and I need to convert them to dates MM/DD/YYYY, and also I need to parse a string to extract the General Ledger account number. I know how to use this easily from within Excel VBA using dateserial, and Instr etc., but now sure how/if I put some code into access and have it called to do this for me automatically. Each night the most recent year is automatically updated in access, so the combination file needs to be refreshed whenever the most recent year file changes.
I currently doing all of this in excel: first initialize the NewCombinedTable, then one by one dumping each year file into an excel worksheet, doing the work needed, then appending it to the NewCombinedTable. However, I think it would work much quicker if I was able to trigger the Combination to happen in Access along with the data manipulations.
I also need to know whether DAO or ADO is best for me to use with 2010...
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?
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.
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 RelatedHello,
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
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
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.
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
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
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?
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.
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
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
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?
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!
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
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
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
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
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
Is there a way to combine queries? Or combine two charts into one?
Thanks.