Create A JOIN Of Different Tables Called Join A Variable And List

Nov 16, 2013

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?

View Replies


ADVERTISEMENT

General :: Cross Join To Create Ledger Type Query From 4 Tables

Apr 2, 2013

I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .

See the attached image ...

View 7 Replies View Related

WHERE Clause On LEFT JOIN : Why Do I Get Join Expression Not Supported Message?

Nov 10, 2006

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

View 7 Replies View Related

Create Table Of Different Columns In Three Tables Join To Form A Master Table?

Jun 4, 2015

I 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?

View 7 Replies View Related

Join Three Fields Together And Create A DATE Field

Feb 20, 2007

I am editing data in a table that was designed by someone else. For some reason, they divided the date into three columns (YR, MON, DAY). I would like to join these three fields together and create a legal DATE field. I have tried the following:

SELECT YR AND MON AND DAY AS TheDate
FROM MyTable;

However, it returns an odd result. I think this might be because ACCESS does not know I want a date format.

My question is: is the above QUERY correct and how do I tell ACCESS to put the concatenated data in a date format.

Thanks in advance for any help you can provide...

View 5 Replies View Related

Queries :: How To Create A Join To Lookup Table

Oct 16, 2014

I 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 Related

Queries :: How To Join One Value To List Of Values

Jan 2, 2014

An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?

View 7 Replies View Related

Queries :: Create Join Using LEFT String Manipulation

Apr 28, 2015

I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.

I have two tables

Table1 will have data in a column that is 9 characters long
ULCABC123
ULCABC124
ULCABC125
PLTABC123
PLTABC124

Table2 will have data in a column that is 6 characters long
ULCABC
PLTABC

Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1

I was thinking something like this. (but then I can't enter design mode)
Query1 - Test

Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6)
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;

I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.

Query2

Code:
SELECT Left([ORDERDET],6) AS NEWORDDET
FROM Table1;

Query3

Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;

View 2 Replies View Related

Creating A List, From A LINK (Join) Table, For Use In Mail Merge

Oct 28, 2005

I am creating a mail-merge application.

The data stored will be dealing with Students and Qualifications that they have attained. The link table in the relationships shown/attached, allows a Student to have multiple qualifications.

When I merge the data, I will need to list all of the qualifications that each student has attained, which will need to be either in a item list, or in a comma-separated list.

The list will need to be pulled from the link table (StudentQualifications). For example:

StudentID #1 >> Qualification #1 Description
StudentID #1 >> Qualification #2 Description
Etc
Etc

So, when the data is merged, it will specify that:

StudentID #1 has acheived Qualification #1 and Qualification #2 and Etc, Etc.

How can I create this list in the first place, which can then be used in the source of the merge for the document.

Any ideas as to how I can pull multiple information into either a single string to pass to the merge or into a list in the document would be appreciated.

Graham

View 2 Replies View Related

How To Join 3 Tables?

Feb 25, 2005

helooo...

i have 3 tables -Recipes, Ingredients and Products.

Recipes table:
RecipeID -PK

Ingredients table:
IngredientID -PK
IngreRecipeID -Foreign key to Recipes table
IngreProductID -Foreign key to Products table

Table:
ProductID -PK

how do i join them into 1 recordset? :confused:

View 1 Replies View Related

Join Tables

Jul 11, 2005

HELP!!! :confused: I do have a 5 tables 4 tables do have a foreign key of the main table. I join the 4 tables with the main table but when I am editing the information I cannot edit it. and no error appears. so I am just wondering what happen with my joined tables? please help me!!!!Thank you in advance

View 1 Replies View Related

Join Tables

Apr 3, 2006

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 3 Replies View Related

To Join Tables Or Not To Join Tables

Mar 24, 2008

That is the question:
Whether 'tis nobler in the mind to suffer
with VBA Programming and the outrageous errors,
Or to take up arms against a sea of Access troubles.

Sorry I was in the mood for Shakespeare.

A quick summary first:
In the attached file I have my Relationships. One main table, Workorders with various one-to-many relationships back to their respective tables. If you look at the attachment and see a field with 'wrk' that's my foreign key.

What I'm trying to do is this:
Where-ever there is a 'wrk' field I want to add in all the fields (minus the ID Primary Key) into the main Workorders Form.

Then on the form itself I should just be able to enter in the data that is required for the fields.

So here's what I'm thinking for the coding is to Join the various tables to thlet me know if I'm on track or not. I'll start with a small one, because if I can get that right, the rest should be simple.

SELECT Model.ComputerID, Make.ComputerID
FROM Computer
INNER JOIN wrkComputerID ON Model.ComputerID = Make.ComputerID;

View 6 Replies View Related

JOIN Three Tables

Dec 12, 2005

Hi,

I've got three tables:

tblEvent
--------
Id (PK) | Event_Name



tblDelegate
--------
Id (PK) | Delegate_Name



tblBooking
--------
Id (PK) | Event_Id (FK) | Delegate_Id (FK)





I need to retrieve a recordset with the following information:

Booking Id | Event_Name | Delegate_Name


Can anyone see how to do a SELECT statement to do this?

ANy help would be great, thanks!

View 2 Replies View Related

JOIN 4 Tables!?

Dec 20, 2005

Hi,

I have the following four tables:

tblGroup:

Group_Id (PK) | Group_Name


tblSubGroup:

SubGroup_Id (PK) | Group_Id (FK) | SubGroup_Name


tblProductType:

ProductType_Id (PK) | SubGroup_Id (FK) | ProductType_Name


tblProduct:

Product_Id (PK) | ProductType_Id (FK) | Product_Name




I need to select a single Product_Name (first one which appear alphabetically) from tblProducts given a Group_Id.

Is this possible? Presumably I need to join the tables in between?

If anyone can help with this it would be much appreciated, thanks...

View 8 Replies View Related

How Can I Join These Tables?

Jun 30, 2005

there must be a way to do this, but i haven't been able to figure it out yet...

table one has three fields i need to be concerned with, member_ID, range_start, and range_end. no part of any range spanning range_start through range_end will be duplicated.

table two has several fields i need, but the field of primary concern is a person_ID that will fall somewhere within a range designated in the first table.

how in the world can i join these tables, since none of the IDs in the second table are actually listed in the first, but fall in between values?

i need to get member_ID from table one, person_ID and several other fields from table two.

any ideas?

thanks for any help,
john

View 4 Replies View Related

Need Help In Sql How To Join 2 Tables Into 1

Oct 2, 2005

hi

i have 2 tables, A and B
A table
StockDate Price
02/04/2001 1.12
04/05/2001 1.15
14/08/2002 1.14
18/09/2003 1.26
and so on 1.48
up to 2005

B Table
StockDate Price
02/04/2003 1.12
04/05/2003 1.15
14/08/2003 1.14
18/09/2004 1.26
and so on 1.48
up to 2005


i like to know how to write codes to join 2 tables into 1 table like
C Table
StockDate Price
02/04/2001 1.12
04/05/2001 1.15
14/08/2002 1.14
18/09/2003 1.26
02/04/2003 1.12 --> B Table
04/05/2003 1.15
14/08/2003 1.14
18/09/2004 1.26
and so on 1.48


regards
ellen

View 2 Replies View Related

How Do You Join Two Tables?

Jan 30, 2007

how do you join two tables?

help plz. thank you!

View 5 Replies View Related

More Than One Join Between Two Tables?

Aug 22, 2014

What is the purpose or the advantage to doing more than one join between two tables? How many joins can you have between two tables?

View 2 Replies View Related

Inner Join And Left Join

Jan 3, 2007

I am trying to do an inner join with a left join. The only problem is, I want to inner join the table that is being joined. This is how I thought it would work below, but it doesn't work.

SELECT * FROM ((Submissions LEFT JOIN Candidates ON Submissions.`Candidate Code` = Candidates.`Candidate Code`) INNER JOIN `Type Candidate Status` ON Candidate.Status = `Type Candidate Status`.`Status ID`)
WHERE Submissions.Status <> 7 ORDER BY `School Interest` DESC;

I want to get the Candidate.Status to inner join with the `Type Candidate Status`.`Status ID`.

If you can help, thanks in advance.
Dave

View 1 Replies View Related

ADO Using An Sql Statement To Join Tables

Sep 5, 2005

Hi there

I am hoping someone can give me a few pointers on how to get started on what I am trying to achieve....

I have 2 tables that form a 1 - to - many relationship.

I have designed a form that I wish to show fields from both tables.

Using ADO I would like to be able to open the form and be able to see my desired fields in the same form.


Can anyone provide any ideas on where I can get started.


Thanks for your help!!

View 3 Replies View Related

2 Tables, No Relationship, Need To Join

Dec 20, 2004

First time user!!!

I work for the school dist. and we have 2 tables for 2 different groups.
I need to pull data from them both and create a new Query.
Example
Table 1:
Name, address, phone

Table 2:
Name, address, phone

(No similarities in ether database, and we'd like to NOT export data)

I'm hoping it's an easy fix that I've overlooked.
Michelle

View 2 Replies View Related

Help Need Creating A Join Between 3 Tables

Sep 6, 2005

Hi there

I have made a form I am trying to bound 3 tables to.

The 3 tables are

tblPersonalInfo
tblBookingInfo
tblSkills

How I am trying to set it up is that I enter someones personal details in once. But using the same personal details someone can have more then one booking.
With the skills....each person can have lots of different skills.

Basically I work for a recruitment agency and I have been asked to build a database that can add new people.add skills to that person.....then add different jobs to the same person.

I have made 3 tables

tblPersonalInfo
PersonalID
chrForename
chrSurname
dtmDOB
chrEmail

tblBookingInfo
BookingID
chrJobTitle
curPayRate
PersonalID
SkillsID

tblSkills
SkillsID
Skills


I have joined all tables togeather (I think wrong but trying) the tblbookinginfo is joined to tblbooking using a 1 - Many relationship using the primary key of tblPersonalInfo to the field PersonalID in the table tblBookingInfo
I have joined tblSkills to the table tblbookingID through SkillsID

I created a query using all 3 tables and have used this to bound the form....

The form contains 3 pages and the 1st page works ok (it only contains info from the tblPersonalInfo) the 2 other pages don't seem to work as I want them to.


I would like to be able to click to add a new person and also new booking and new skills

Currently I am able to add new personal details but nothing else.

Can anyone see what I am doing wrong?

Thanks for taking the time to read this

View 11 Replies View Related

Join Tables Into A New Table

Jul 13, 2005

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. ;)

View 1 Replies View Related

Sql For Insert Into Join Tables

Nov 6, 2007

Hi,
I have an unbound form that is based on a query. On the Load-event I have populated textboxes with the underlying records (it works fine).

The underlying query is based on 2 tables that are joined on (PrimaryKey) Table1.ID = Table2.ID (ForeignKey).

The Form has one Add-CmdButton. When the user will click the Add-button the content of the textbox will get added or saved in the underlying database.

I want to know the SQL to add, so that I can write the code behind on the Click-event of the button.

I know on how to add records with a single table (writing code behind), however, I want to know on how to do when it comes with joined tables (INSERT SQL statement ?).

Thanks and regards,
Prodigy.

View 1 Replies View Related

Join Two Tables In A Query

Feb 13, 2005

I would like to join two tables with one query. Here is the twist though.
The first table would have a set of value like so:

0
50
100
300
500
750
1200
1500

the second table would have values as such

0
1000
1300
1500
1750

by just setting up the relationship to show all values from the sirt table and only those from the second table that match I get a list of values that will not work for me. What I would like to reflect
in the query is all the values from the first table and if there is for instance no value of 500 in the second table I would like to return 1000. For instance there is a zero in each table so that would be output twice. There is however no value of 50 in the second table so the query would read 50 and null or blank when I would like it to read 50 and 1000 (The next value higher).

View 3 Replies View Related







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