Update Query And Joining
Jul 31, 2006
I have a database and i have one field call fdr. that field is full of data but i realized it wood be much more capable to do what i need if i use a joined table with every single fdr. so i created a table with fdr and a id column and set primary key as id. I filled in every single fdr into the table but now I need to replace the original table with the id and then have the fdr # from the new table show up.
Does anyone know how to create the update query and create the proper relationship for what i need to do?
thanks
View Replies
ADVERTISEMENT
Apr 19, 2007
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
View 1 Replies
View Related
May 8, 2013
All; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
View 1 Replies
View Related
Jun 6, 2007
TABLE 1(x)RN|Flat | Init 238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1246TABLE 2(xx)RN|Eq |Nr|Rpt 21RSFLC1055SHFLDML 238NKCR1243CHSTNRH238NKCR1246CHSTNRHI'm trying to make a query that Joins FLat to Eq and Init to Nr. The reason I am making this join is to COUNT all instances of Init, so it should count 7. Then I want that 7 to correspond to the Rpt CHSTNRH in table 2. So my finalized query should look like...RN Rpt Count238 CHSTNRH 7When I create a Join in a SELECT query it says "Type Mismatch expression". Is there a special query I need to use? Here is my SQL.SELECT x.RN, Count(x.[Init]), xx.[Rpt]FROM x INNER JOIN xx ON (x.[Init] = xx.[Nr]) AND (x.[Flat] = xx.[Eq])GROUP BY x.RN, xx.[Rpt];
View 6 Replies
View Related
Feb 17, 2006
Let's say that a table has been broken in two and I'd like to rejoin them with a query. Let's say 100 records in table1, 20 records in table 2. Here's a random example, I just made the names up off the top of my head:
PersonTable (100 records)
Person # (unique key)
Name
Address
SpouseTable (20 records)
Person # (lookup wizard to Table1, cascading edit/delete relationship, 1 to 1)
WeightOfSpouse
HeightOfSpouse
Many people aren't "married" and so have no corresponding value in the Spouse table. If I do an inner join on the two tables, on Person#, then it will cut the total # or returned records in the query to 20, to match the total in SpouseTable. Essentially, I want to append the results of SpouseTable to the end of the corresponding rows in PersonTable.
View 1 Replies
View Related
Apr 30, 2014
I'm new to access. Basically I've put this formula in a field within my query(Query A):
PERIOD: [YEAR] & "-" & [Month Number]
And I want to use this newly created field "Period" to join another table (Table A) without having to create another query.
PERIOD(Query A) -> PERIOD(Table A) = Month Name (Table A)
I need the month Name from Table A but because Period(Query A) is a formula I created, I don't know any way of joining it to Table A without creating another query. Is there another way...
View 5 Replies
View Related
Apr 5, 2013
I have two tables containing (let's say for simplicity) questions and attachments (pictures). I am trying to perform a union query to join all the questions and pictures into one report, but it won't let me union the attachment because 'the multi-valued field 'TableA.Pictures' cannot be used in a union query'.
I have searched and searched for a solution (and got kind of close) but i still can't get it to work. The best I can do is union everything like below, which gives all the questions as desired, but says #Error in the pictures column:
SELECT TableA.*
FROM TableA
Union
SELECT TableB.*
FROM TableB;
(Note tables A and B have the same structure, several yes/no and open text questions as well as one attachment field. )
View 8 Replies
View Related
Mar 14, 2006
Hey guys I have a problem on creating the append-query and the new table
I want to join couple different fields from 2 COMPLETELY different table with TOTALLY DIFFERENT fields.
>>What I have right now are the following:
Table 1
Fields: meta_theme, theme, sub_theme, root, count
Table 2
Fields: meta_AG, AG, AG_description
>>What I want at the end:
A new Table 3 that contains the following information AND is a form, which when you open will have :
1.meta_theme: automatically populated with the records from table 1
2.theme: automatically populated with the records from table 2
3.AG: This column is to be filled when someone run the form, and it will be a combo box with value from the records in Table 2-AG field
4. Comments - completely new
>>could you guys help? My brain is totally in disfunction mode... :(
View 1 Replies
View Related
Oct 25, 2006
Hi
I have an Access database that contains 2 tables. Both of these tables have the same structure and have the same field names. So:
Table 1 Fields :
Name
Address
Telephone
Fax
Table 2 Fields :
Name
Address
Telephone
Fax
Is there a way to write a query, that will show the results of both tables in one go. (None of the information in table 1 is duplicated in table 2 - I want to show all records from both tables in one new table). So, if both tables had 3 records each, the query would return:
Name - Address - Telephone - Fax
jon-uk-12345-54321 (from Table 1)
julie-uk-21451-41541 (from Table 1)
paul-ir-98545-11241 (from Table 1)
pat-uk-99585-63362 (from Table 2)
phil-uk-99985-99631 (from Table 2)
ted-uk-44444-55555 (from Table 2)
These 2 tables need to be kept separate, so I can't copy and paste the records from Table 1 into the bottom of table 2.
The field names are the same in both tables.
Can this be done?
Many Thanks
View 1 Replies
View Related
Jan 2, 2007
Hi All!
I have two tables. Table Heffalump which has field "DealGroup" and other fields as well... and table 8-YTD Principal Investments whic has field "book" and other fields as well... I need to use BOOK and DEAL Group as a combination primary key to compare against table 8-YTD Principal Investments. If there is a new DEAL GROUP then append into table "8 - YTD Principal Investments"
THANKS IN ADVANCE,
B
View 2 Replies
View Related
Mar 31, 2008
Hi Guys,
I have a problem which I cannot figure out.
I have a table which has an ID field and also a field with data. The ID field is 'not' unique.
I need to join all of the data fields which have the same ID field into one field. So it would be ID; Data 1, Data 2...
Any ideas? I would appreciate any help you could offer!
View 7 Replies
View Related
Aug 23, 2005
Seems like a simple question but I am stuck.
I need to join two strings from different records in a table
table eg
ID Desc
1 blah blah
1 more blah
2 blahring
2 blah
3 and last one
I want to join all the "Desc" strings that have the same ID to get
1 blah blah more blah
2 blahring blah
3 and last one
Can anyone help?? :o
Thanks
Paul
View 1 Replies
View Related
Oct 4, 2005
I'm trying to create a Purchase Order Database that requires a monthly summary of all the purchase orders in that month.
Examples of the fields i'm using are: Date, Vendor, Description, Item Number, Unit Price, Quantity, Sub-total, Total, etc.
Each table is a single purchase order, and there are around 3 or so every month. What I am trying to do is create a query in which all related fields are joined, and can be viewed as a single field.
For example:
Field 1 is DATE for Purchase Order (PO) 1, 2, and 3.
What I want to do is join all entries in DATE from PO1, PO2, and PO3 in a single query.
I want it to look like:
[DATE]
PO1......
PO2......
PO3......
I've tried several approaches to this problem, but they all have the same end result. It creates a seperate field for each table:
[PO1-DATE][PO2-DATE][PO3-DATE]
Any suggestions?
View 2 Replies
View Related
Aug 23, 2006
I have two tables. One stores details of all money travelling from A to B, one from B to A.
I have created queries 'qry_A_to_B' and 'qry_B_to_A' to get each set of information.
Since there are User IDs in each table that don't appear in the other, I'm using the following two queries to return all the TO and FROM values. By using both LEFT joins, I appear to be getting all entries.
'A to B data
'------------
SELECT
[qry_A_to_B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth
FROM
[qry_A to B Yearly]
LEFT JOIN [qry_B to A]
ON [qry_A to B Yearly].[ABUserID] = [qry_B to A Yearly].BAUserID
GROUP BY
[qry_A to B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth;
'B to A data
'------------
SELECT
[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth
FROM
[qry_B to A Yearly]
LEFT JOIN [qry_A to B]
ON [qry_A to B Yearly].[ABUserID] = [qry_B to A Yearly].BAUserID
GROUP BY
[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth;
How can I join these two, to provide a list of ALL information, from all ten columns, regardless of whether or not someone has both a TO and FROM value? i.e.
If they only have a TO entry, I want to see it; the other five columns would be blank.
If they only have a FROM entry I want to see it; the other five columns would be blank.
If they have both, I want them to line up on one line.
However I try to phrase the query, I seem to miss at least some of the information from one or both tables.
View 9 Replies
View Related
Feb 16, 2007
I am building a simple task tracker to keep a record of tasks being passed back and forth between our team. The schema is fairly simple as illustrated in the image below.http://img2.freeimagehosting.net/uploads/f4f3a05c21.gifI want a recordset as follows out of this database (+/- few columns. But these are the most essential ones needed) -ixProject | Project.sTitle | ixTask | Task.sTitle | TaskHistory.dtEntry | sStatus | sEmployeeAssignor | sEmployeeAssigneeThis is basically a recordset of the history of the task (who assigned task to whom) and the status (assigned, resolved, closed). I can get the query to return the user id (ixEmployee) without any problems. But to display this in the interface I will need the employees name (sUsername).I tried the following query -SELECT Employee.sUsername AS sEmployeeAssignor, Employee.sUsername AS sEmployeeAssignee, Project.sTitle AS sProjectTitle, Task.sTitle, TaskHistory.dtEntry, TaskHistory.sDescription FROM(((Project INNER JOIN Task ON Project.ixProject = Task.ixProject) INNER JOIN (Employee INNER JOIN TaskHistory ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignor)ON Task.ixTask = TaskHistory.ixTask)INNER JOIN Employee ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignee <<< THIS IS THE PROBLEM LINE)WHERE (SELECT LAST(TaskHistory.ixEmployeeAssignee) FROM TaskHistory) = 1ORDER BY TaskHistory.ixTask, TaskHistory.ixTaskHistoryThis is the error I get -Join expression not supported.State:S1000,Native:-3530,Origin:[Microsoft][ODBC Microsoft Access Driver]Can someone explain how I can solve this problem? I am currently running two queries - one to retrieve the usernames, and the other to retrieve the history with the user id's - and replacing the user id's with usernames when displaying the records, but would like to have a cleaner solution.
View 2 Replies
View Related
May 30, 2007
This gets even more confusing as I go.
Two tables again needing to be joined. But only need to fill the data in the first line.... yes, Pain in the ARSE> here's a sample.
PAYLINES TABLE
EMPLOYEE --------- DATE -------- Paytype
121614000 -------- 12/31/2007 --- 300
121614000 -------- 12/31/2007 --- 122
121614000 -------- 12/31/2007 --- 100
121614000 -------- 12/23/2007 --- 100
TAXTABLE
EMPLOYEE --------- DATE -------- CPP ---- EI
121614000 -------- 12/31/2007 --- 1.23 --- 2.35
121614000 -------- 12/23/2007 --- 2.32 --- 1.35
The end Query I need is
EMPLOYEE --------- DATE -------- Paytype --- CPP ---- EI
121614000 -------- 12/31/2007 --- 300 ------- 1.23 ---- 2.35
121614000 -------- 12/31/2007 --- 122 ------- NULL ---- NULL
121614000 -------- 12/31/2007 --- 100 ------- NULL ---- NULL
121614000 -------- 12/23/2007 --- 100 ------- 2.32 ---- 1.35
The TAXTABLE only needs to assign it's data to the FIRST row of the resulting query
View 3 Replies
View Related
Jan 30, 2008
Hello! I need to join 2 tables, 1 table with all of the fields, and another with just 2.
The link between the 2 tables is an ID field in field 2 wich is long integer.
The problem is, the ID field in table 1 that correlates to the ID field in table 2, is in the datatype text.
I cant change the datatype of the field in table 1 because it is a linked table to a txt file. And some of the values in there are dashes, indicating no ID. (not sure why).
So, is there ANY way to link the2 tables with the 2 different datatypes
Thanks in advance!
View 5 Replies
View Related
Feb 17, 2005
I have two tables in my database. one is linked to a Client.txt and the other is tblNewClient.
the idea is that this database is a portable version of the actual database, meaning that it will be operating on its own without a connection to the actual back end.
Now the problem is when a new customer is added, i want it to add itself to the tblNewClient.
but when i go into my Find CUSTOMERS FORM i need to be able to search threw both tables.
i want to creat somthing like a union query but in vba.
I figure i need to have 2 recordsets in vba. one for each table and then i need to have a 3rd recordset wich will hold all the info.
im not sure how to copy a whole recordset and set to another table.
Im thinking of somthing like
set RsAll = RsClient & RsNewClient
This obviously does not work but it is what i want to accomplish ,and i want the Rsall to be an actual table and not a public Recordset
View 3 Replies
View Related
Feb 7, 2007
I hope the title isn't misleading. I just need some advise. I'm trying to bend my mind around how to accomplish this.
I'm putting together a database for a trucking company. The employees are paid according to the loads they haul. I have a setup right now where the manager can put in all the information (including driver, $$ rate, date, etc), and the invoice will be calculated and created in a report. Further, it also creates paystubs for each driver. This is where I am having trouble. You see, I can use a query and detail section in a report to add up the amount earned for each driver from each load. No problem there. My problem is that deductions are made to these paychecks for various things which change every week. I haven't quite figured out yet what the best solution would be for inputting this deduction information and then joining it to the payment information.
I hope this is somewhat clear what I am doing. If not, please let me know and I'll clarify. I can try to strip a database, but it still might be too big.
Thanks in advance for any help.
-opiv6ix
PS - If anyone has an eloquent way of choosing a pay period, I'd love to know about it. Right now I'm building the report off of undefined fields [Startdate] and [Enddate] in the query, prompting the user when s/he opens the report.
View 5 Replies
View Related
Dec 15, 2005
Hi whats the syntax for left joining more than 2 tables if each of the tables has a common column???
View 4 Replies
View Related
Feb 10, 2006
I need to do a join on two tables, one is in the current database, but the onther one is an another database. I am struggling with the syntax. Can anyone advise where I am going wrong?
This is the code, but it says the usual 'Syntax error in FROM clause'
SELECT za06_report_2006.wbs, za06_report_2006.cost_cntr, wbslist.description
FROM za06_report_2006 LEFT JOIN wbslist in '\ukdewgs002gdatapcdatafinancedocumentwbslis t.mdb' on za06_report_2006.wbs= wbslist.[wbs element];
All table names , variable names, and paths are correct.
Thanks for any suggestions
(I am very new to SQL)
View 1 Replies
View Related
Oct 25, 2006
Hey everyone. I have this situation: I work for a veterinary company and I have a project which includes 4 tables.The first one has a list of all of our clients.The other 3 are : a table for the year 2004, one for 2005 and one for 2006. Not every client visits us every year and I need a query which will make certain action (different sorts of calculations) with the number of animals that we take care of.
To make it more clear:
During 2004 one client with ID 12345 visited us with 3 animals.
He didn't show up in 2005 but he came back in 2006 with 4 animals.
Trying to do so I joined the tables properly (in my opinion one-to many) and I only get the sum if a client has visited us for all the 3 years. If he appears in just two tables or one I don't get a result.
This is the problem. Has anyone got a clue?
Thanks in advance
Kyriakos
View 3 Replies
View Related
Feb 10, 2007
Hi all,I am having problems joining two tables together.
Table1
ID Name
1 A
2 B
3 C
Table2
ID name
1 D
2 E
3 F
I would like to make the query same
ID name
1 A
1 D
2 B
2 E
3 C
3 F
Can anyone help please? It would be much appreciated.
Many Thanks, mimic
View 2 Replies
View Related
Jul 23, 2007
Hi all,
In a table, I want to join the texts in two fields to a new field by a backslash.
eg. Canon(Field1) and Digital Cameras(Field2) = Canon/Digital Cameras(Field3).
Can anyone please help me with a solution?
Cheers
Bud
If the question is not clear please refer to the excel sheet.
View 9 Replies
View Related
Sep 14, 2004
is it possible to join these two queries together, so that the 2nd query appears at the bottom of the first?
Code:SELECT OEEModelMCTotals.Machine, OEEModelMCTotals.EventCode, OEEModelMCTotals.CodeDescription, OEEModelMCTotals.SumOfTotalTime, OEEModelMCTotals.Occur, OEEModelMCTotals.PlannedTime, [OEEModelMCTotals]![SumOfTotalTime]/[OEEModelMCTotals]![PlannedTime] AS [%Schedule], [OEEModelMCTotals]![SumOfTotalTime]/[OEEModelMCTotals]![Occur] AS Avg, OEEModelMCTotals.Output, OEEModelMCTotals.Throughput, OEEModelMCTotals.ValueAddedimeFROM OEEModelMCTotalsWHERE (((OEEModelMCTotals.EventCode) Not Like "n204" And (OEEModelMCTotals.EventCode)<>"n301" And (OEEModelMCTotals.EventCode)<>"n303" And (OEEModelMCTotals.EventCode)<>"x104" And (OEEModelMCTotals.EventCode)<>"x117"));
and
Code:SELECT OEEModelOtherTotals.EventCode, OEEModelOtherTotals.CodeDescription, OEEModelOtherTotals.Machine, Sum(OEEModelOtherTotals.TotalTime) AS SumOfTotalTime, Sum(OEEModelOtherTotals.Occur) AS SumOfOccurFROM OEEModelOtherTotalsGROUP BY OEEModelOtherTotals.EventCode, OEEModelOtherTotals.CodeDescription, OEEModelOtherTotals.Machine;
i have attached a spreadsheet with the outcome i am after....i dont want it in excel but have used this for my demonstration.
you will see at the bottom of the sheet i have highlighted the info added in blue.
can this be achieved ?
if so how please.
cheers
Andy
View 1 Replies
View Related
Sep 21, 2004
Hi...Well I have 2 table with same characteristic [ Id_no(Autonumber (primary key)), Name(text), Address(text)]...is there a way I can combine this 2 table into one...is there any issue regarding the uniqueness of the primary key..
View 1 Replies
View Related
