How do I join two tables. I have a table and a lookup table. My table has products on there that are listed as custom or basic. I have a look up table that has an ID for basic and Custom. In my table, i want it to read what the id is for each product instead of it reading "basic" or custom. Someone said that I need to join the two tables and do an update query, but I don't understand how to.
View RepliesI have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows
Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";
but I get Join Expression not supported
What am I doing wrong?
Thanks
Andrew
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
Quick question so i can clairify what I am thinking. I have two tables. Both have a job code fieldname in them. What I wantto happen is where the job code in table 1 = job code in table 2 display the job description in table two. Example
table 1
name | mo | date | code
table 2
code | job description
If i understand this correctly I would need to do a table join correct... somthing to the order of SELECT * from table2 where me.table1.code = me.code? I'm not entirely sure. Am I understanding this correct?
SELECT tech_id.CORP, tech_id.TECH, tech_id.TECHCONT
FROM tbl_PPVResearch INNER JOIN tech_id ON Mid([tbl_PPVResearch].[AccountNum],1,5) = tech_id.CORP;
im having problems updating a table. im trying to take a column from table2 and place it in table one...i tried exporting it to excel, copy and pasting it and what not, but since it has about 200k values, it only copied half, incomplete...
i tried this query, i dont know if im doing it right.
SELECT DlexLoad.pah, DlexLoad.doc, DlexLoad.vol, Bate.BATES
FROM DlexLoad
INNER Bate
ON DlexLoad.Bates = Bate.BATES
im trying to replace the Bates column from Dlexload table, with Bates from bate table...
i attach a screen shot of my tables
thanks
Have some problems with a Purchase Order (PO) System I'm trying to do.
Figure 1 is the PO entry form. The upper part of the form shows the Header, while the bottom part shows the Line, containing all the ordered item details.
I separated the information keyed in into 2 tables, namely Header table (Figure 2) and Line table (Figure 3). The header table shows only the header details. With the order ID, the header is linked to the Line table where all the ordered items detail can be found.
I want to join the 2 tables and put the data into a new table, which will later be exported to text/spreadsheet for other purposes. I know it can be done using the make-table query. But my concern is about the layout/format of the table.
I would want to put my data where there's one header row followed by the corresponding line rows. Then another new header row with its line rows. I'll give an example:
H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
Note that not all the fields for Header row and Line rows are the same. So can I put different data into the same column?
Would appreciate it if anyone could solve my problems. Thanks. ;)
Hey Folks,
I'm trying to write a query that will get all the records from one table, and only certain records from others. (Access2000) I'm getting a "join expression not supported" error however, so my question is two fold.
1. Does Access2000 support an inner and outer join the in same query?
2. Does my query below just have a syntax error thats causing the problem?
Here is the SQL, i've posted a rough table schema below too. (please note i'm using ColdFusion to query the database, so the #tstiid# is a variable passed to the query)
SELECT ((iss.iid, pg.pgid, pg.pg_name
FROM issues iss
LEFT JOIN issuespages ip ON ip.iid = iss.iid)
RIGHT JOIN pages pg ON pg.pgid = ip.pgid
WHERE iss.iid = #tstiid#
Database layout:
Table 1, named: issues
iid | issuename | a ton of other fields
------------------------
1 | April 2006 |
2 | May 2006 |
table 2: pages
pgid | pagename | pagecontent | etc
-------------------------------------
1 | Contact us | To contact us, use the following...
2 | News | Upcoming news...
table 3: issuespages
ipid | iid | pgid
--------------------------
1 | 1 | 1
2 | 1 | 2
Thanks in advance for anyones time!
Hello,
I have 2 tables.
1) Table A, which is a table of all 100,000 users
2) Table B, which is a table of all other users
I have a query qryA, which looks for everyone in table A with a certain type of job title.
I want to exclude anyone who works for a company that occurs in the table B from showing up in the qryA query.
Any ideas?
Hello,
This db I'm working on tracks the latest revisions of drawings. Multiple drawings fall under a work package, and multiple revisions of a drawing are present (listed 1, 2, 3, ...). I want to pull information about the latest revision of a drawing, including the drawing number, the tracking ID from the tbl_wkpkg_dwg_rev table, and the corresponding dwgTitle from tbl_dwg. The tables are joined through the field wkpkg.
I have my select statement partially working; it works on one table. This is what it returns:
dwg1 3 id1
dwg2 1 id2
dwg3 5 id3
Here is the statement:
SELECT r.dwg, r.revision, r.trackingID
FROM tbl_wkpkg_dwg_rev r, (SELECT MAX(revision) as maxrev, dwg FROM tbl_wkpkg_dwg_rev GROUP BY dwg) MaxResults
WHERE r.dwg = MaxResults.dwg
AND r.revision = MaxResults.maxrev;
So I would like to join it to tbl_dwg and get:
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg3 5 id3 DrawingTitle3 wkpkg2
However, when I try and join it with tbl_dwg to get the other information, it returns this:
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
I tried using SELECT DISTINCT, with no luck. I'm out of ideas, so any help would be appreciated! :confused:
Thank you!
Hi, I just a fresh user for the Microsoft Access. I have some question on the forms. I created a form and there is a field name Membership Id. In this field I created using AutoNumber format. I was requested this membership number needed to add also some Area Code and BranchCode infront of it. How could I add this into this membershipID so I could have this few info combine and shown in my form(textlabel) ? :p :p
View 1 Replies View RelatedI have a table orders details with a sub form displaying the order, I have a combo box to display the product from products table to place in the sub form products box . I keep getting the error message join key of table details not in record set I have checked my relationships and for keys and there all there .
View 1 Replies View RelatedI have most of my data in DB2 tables which I am linking to. However, there are a few pieces of data I am gathering from text files. I put them into a little Access table and tried to JOIN with a DB2 table field. My query died....
Is Access amenable to joining different types of tables? If anyone can advise I would be most grateful!
Arpeggione
I was working on an update query while joined to another table - and the error I was receiving was the query was not updatable. Er... The table that was being updated sure seemed able to be updated...
Then I wondered if the reason this didn't work was because the other table I was updating from was a query whose records were sum'd and group'd by..I ended up testing the idea by inserting the query's records into a temp table and then did the update to the target table from the temp table... which worked fine.
Basically, I want to be able to click on the New (blank) record button, and then start adding in my Attraction, Date, etc fields in the main form, however I keep coming up with the error:
Cannot add record(s); join key of table 'OrdersList' is not in recordset.
I've gone back and checked all my relationships, and the query the form is based on, and all appears to be working there fine. I can manually enter information on to the tables & queries just fine, (but obviously I don't want other users to be able to see these).
I'm wondering if it's something to do with the Auto Number, which is also my PK in table 'OrdersList'. As the first design of this database had this field set to a Text field and I would manually enter the next sequence and I didn't have any issues adding new Orders to the form.
The form is 'Orders', which is based on a query called 'Orders List'.
How to Delete data from only one table from a Join? OR How to set the ADO recordset unique table property?
On Access 2010 module I have a class that manipulate data (save, read, edit and delete) from this statement:
Code:
Private Const strNomeTblFonte as string = _
"SELECT ER.*, ET.intTipoExame, ET.txtNomeExame, FROM tblExamesTipos
ET INNER JOIN tblClientesExamesRequisitados ER ON ET.idExamesTipos = ER.intQualExame;"
Private Sub Class_Initialize()
Set mCol = New Collection
[Code] ....
Problem: I need to delete data from only one table (tblClientesExamesRequisitados) of a inner join, but only delete from the "wrong" (tblExamesTipos) table.
After going to msdn on title: Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) I attempted to address the problem with this line in the Class_Initialize():
Code:
Recordset.Properties("Unique Table").Value = "tblClientesExamesRequisitados"
But only generates this: Run Time Error 3265 - Item not found in this collection...
I know, if I open another Rst and use a Distinctrow, or open only one table, as in "DELETE * FROM tblClientesExamesRequisitados WHERE intQualExame = " & miQualExame & ";" it will resolve, but, why "Unique Table" isn't functioning?
I have a form that gets its info from a query, I would like to be able to add a new item, Customer, etc, etc. But, when I try to add one (I have a button using VBA code, ill post that at the end) it gives me the error
Code:
Cannot add record(s); join key or table tblitems not in recordset.
Code:
Private Sub cmdEventNewI_Click()
Me.Visible = False
DoCmd.OpenForm "frmItemsEdit1", acNormal, , , acFormAdd, acDialog
Me.Visible = True
Me.lstItems.Requery
End Sub ' cmdEventNewI_Click
I am trying to create a cross join or Cartesian product TABLE, not a query.
I am creating a training database. For each and employee and each training event, I want to know - is this event required, who approved it, and when was it completed. The table I envision looks like this:
Code:
EMPLOYEE_ID EVENT_ID REQUIRED APPROVER EVENT_DATE
1 1 Y WPD 9/5/14
1 2 N
I currently have 39 employees and 473 events - 39*473 = 18447 records
I was able to make a cross join query and use make table, but whenever I add a new employee or event, if I update the make table query I will lose all my existing data.
I'm doing a TAFE assignment (I'm from Australia) and I have tried to populate a field in my subform called RestaurantID with data and it has given me the error'Cannot add record(s) join key table Restaurant not in recordset.
View 7 Replies View RelatedI am currently working on ODBC linked tables to our webend system. I need to create a join to a lookup table but I cant seem to get it to work as it only seems to show me results from one of the tables not both? Ive tried LEFT and RIGHT joins plus INNER JOIN.
View 3 Replies View RelatedI have some code. I want to copy all columns from
Code:
tabela_zrodlowa = "tblGoraZlecenia"
but only one column which is called "Id_rodzajpracy" from
Code:
tabela_zdrodlooffset = "tblOffset"
How could I modify red instruction to do inner join?
Code:
Dim rec_GoraZlecenia As DAO.Recordset
Dim rec_GoraZlecenia2 As DAO.Recordset
Dim rec_GoraZlecenia3 As DAO.Recordset
Dim tabela_docelowa As String
Dim tabela_zrodlowa As String
Dim tabela_zdrodlooffset As String
Dim s As String
[code]....
I am fairly new to access, and have the following problem with the attached new DB. Thought I have designed the start of a good database with a "Junction Table". Have 1 problem, cannot enter a new name on the from, gives the error below:
"Join key of personnel table not in recordset" ...
hello people
im having some problems with my db. i have 2 tables(ATL and BATE)
in ATL, i have (im, IMAGEID, BREAK, TASK, LOC, bate) and in BATE, i have (Bate, var)
im trying to take Bate from Batetable, and insert it to ATL, even tho thers already a bate in ATL, both contain different records...
i tried this, but didnt work
Select ATL.im, ATL.IMAGEID, ATL.break, ATL.task, ATL.loc, ATL.Bate, BATE.bate
from ATL, BATE;
it worked, but BATE shows the same value for the hole row, it displays the same thing thing, even tho each row has a different value...
can anyone help me out?
thanks
Hello, exactly how Access 2002 and SQL operate is still a bit fuzzy to me.
In brief:
tableAccounts has fields for Signatory1ID and Signatory2ID, and other stuff
tableSignatories has ID for a primary key and has SignatoryTypeID as a field
tableSignatoryTypes has ID for a primary key and SignatoryType as a field
For each account, on a Form I want to show the Signatory and SignatoryType info for both signatory1 and signatory2. I have tried creating various queries and INNER JOINing the tables and/or queries together, but whenever I manage to get all the information displayed, none of it is editable. Access seems to lock the various text boxes and combo boxes.
Any thoughts?