Need Help With INNER JOIN

Sep 10, 2006

I have a database with PCs and installed hardware. An external program scans all PCs, the result of the scan can be saved as Access file.
Now I need a query to select from each PC the installed hardware (Monitor, Printer...) but only with the latest date. I have written a query MaxDatum to select the the lastest date. Here's my query code:

SELECT [Table1.needed fields], [Table2.neede fields]...
FROM (((Table1 INNER JOIN (Table2 INNER JOIN qryMaxDatum ON (Table2.Field1 = qryMaxDatum.Field1) AND (Table2.Field2 = qryMaxDatum.MDate)) ON Telle1.Field1 = Telle2.Field1)
INNER JOIN Table3 ON Table2.Field2 = Table3.Field2)
INNER JOIN Table4 ON Table2.Field2 = Table4.Field2)
INNER JOIN Table5 ON Table2.Field2 = Table5.Field2
ORDER BY Table1.Field1;

My problem: this query mulitply the number of data records so that I have 80 data records with exactly the same content per PC. How do I neesd to change the query to get only one data record per PC?

View Replies


ADVERTISEMENT

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 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 6 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

Join

Nov 27, 2007

I have two queries containing this information (example):

View 3 Replies View Related

Right Join?

Jul 21, 2005

I having a little trouble with a query I was hoping someone could help me with. Basically I have the following SQL statement

[code].....

I would like to show all records for the tblCService.CService field however it won't let me change the join to a RIGHT JOIN. I get an "ambigious outer joins" error message. As far as schema tblEList is the main table and the tblCService is a lookup table. Any ideas?

View 1 Replies View Related

Join Where Contains ?????

Aug 23, 2006

hello everyone,Please can someone tell me if it is possible to create a Join on two tables where the value from 1 field is contained within a field from the other table.eg.table1.field = "xyz"table2.field = "the xyz super thingy"This is the SQL it gave me orrigionally but this is an "= to" join which is not what i want. I tried replacing the = with a LIKE but am still getting the same results.SELECT table1.*, table2.*FROM table1 INNER JOIN table2 ON table1.modelno = table2.name;SELECT table1.*, table2.*FROM table1 INNER JOIN table2 ON table1.modelno LIKE table2.name;

View 1 Replies View Related

Only Get Top 3 From The Join

Sep 25, 2007

How do I only bring back the top 3 from the join in this query?
There will be multiple records in the InvoiceNotes table and many different invoices.

select invoice_no, inv_date, totamnt, projman, invoicenotes.note, invoicenotes.notedate
from invoices
left join invoicenotes on invoicenotes.invno = invoices.invoice_no

The InvoiceNotes tbale has an ID column so I can order by MAX(ID) DESC and then get top if needed

View 3 Replies View Related

Join Twice???

Nov 29, 2007

Not sure if I'm going in the right direction, but am stumped at this one!
I have a few tables:

tblDailyTripLog
ID; TripDate; Departing; ETD; Destination; Depot; Driver

tblDepots
ID; Name

tblDrivers
ID; Name

Now, in tblDailyTripLog.driver, the tblDriverID is stored. Same goes for Destination, Depot & Depart, the tblDepots.ID gets stored which can or will differ....

Now, when do a query, how would I show 3 different joins to the same table (ie. tblDepots) to get the name, instead of the ID for Departing, depot & Destination?

I've tried doing a Left Join and then another LEft Join, but its not working....

View 5 Replies View Related

Can I Use One More Join

Jul 20, 2005

it has already 2 inner joins can i use one more inner join within.

strSQL = "SELECT distinct z.location_id,z.location, y.company_id,y.company_name,w.category_id,w.catego ry FROM (appointment_detail AS x INNER JOIN location_table AS z ON x.location_id = z.location_id) INNER JOIN company AS y ON x.company_id=y.company_id ( INNER JOIN category AS w ON
x.category_id =w.category_id)WHERE x.expiry_date >=date() ORDER BY z.location ,y.company_name"

INNER JOIN category AS w ON
x.category_id =w.category_id

View 3 Replies View Related

Join Help

Apr 17, 2007

I have 2 tables
customer
dialcodes

now I need to loop through the whole customer table and pull up all customers where the count of the following statement is more then 1

select count(geographic) as mycount from dialcodes where [phone1]like code + %
or if phone2 is not blank then the count of the following statement
select count(geographic) as mycount from dialcodes where [phone2] like code + %

I want to return all records from the customer table where the phone fields don't match the beginning codes from dialcodes.

can i do this with a join?

View 2 Replies View Related

Self-join Not 100% Working - Please Help

Feb 11, 2006

I'm trying to create a database of Publications (instruction manuals) where one publication is a modification to a parent modification. I'm storing all 'publications' in one table for better maintenance. This means that I have to use a Self-Join if I want to make one the parent, and one the child. Fundamentally this is no different than the Employee/Supervisor example I've found in many places:

http://www.databasedev.co.uk/self-join_query.html

I created a query with two copies of the same table with the 2nd one renamed for easier viewing. The query final comes up, but when I choose "ParentPub", it doesn't give me a drop down showing the other pubs so that I can choose one to be the parent.

What did I do wrong? I should be able to open up the table 'tblPubs', and get a drop down menu showing me all the pubs I can assign as a parent.

Thank you for your help,
Enrique

View 3 Replies View Related

Complex Join

May 17, 2007

Hi,
I was wondering if someone can give me a possible solution to my join below:

select tableA.name
From tableA, tableB
where tableA.id IN (Select id from table c where .......) <--I would like tableB.prodId to make a join with the tableA.id's that are in this select sub query


Thanks

View 1 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 Query

Jun 6, 2005

Hi All,

I have 2 tables. [1] AvailableCars [2] CarPics

[1] AvailableCars consists of fields as Zone (eg. ZoneA, ZoneB, ZoneC) , CarCategory (eg. CarAA1, CarAB1, CarAA2 ... and so on), DailyRates, and Valid DateBounds (FromDate, ToDate).

[2] CarPics consists of CarCategory and Carpicture (Path of the car image).

Now I want to Bring Only those cars from the AvailableCars table and CarPics which satisfy the criteria of falling between Valid Date Bounds...I am using join as the following :

SELECT
AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY, AVAILABLECARS.DAILYRATES, AVAILABLECARS.FROMDATE, AVAILABLECARS.TODATE, CARPICS.CARPICTURE
FROM CARPICS
JOIN AVAILABLECARS.CARCATEGORY = CARPICS.CARCATEGORY
WHERE
AVAILABLECARS.ZONECODE = "ZONEA2XY" AND
AVAILABLECARS.FROMDATE BETWEEN "DATEVAR1" AND "DATEVAR2"
ORDERBY AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY

Using above query, I am not able to get the desired results. :o

Can Any body help me in solving this problem ? :confused:

Thanks in Advance,

Regards,

Jigs

View 3 Replies View Related

Query With Join And Where

Jul 14, 2005

I need to write a query which retrieves all the records from first table and those matching from second table and also satisfies a criteria.

I had

Table 1
F1
F2
F3

Table 2
F4
F5
F6
F7

I had to get all the records from Table1 and also those matching with Table2
So i did left join and getting all the records.
And I also need to check whether F3 in Table1 is in between F6 and F7 of Table2.

So I wrote query like this

SELECT Table1.F1, Table1.F2, Table2.F5
FROM Table1 LEFT JOIN Table2 ON (Table1.F1 = Table2.F4) WHERE Table1.F3 BETWEEN Table2.F6 AND Table2.F7;

I am getting only those records which are matching where condition.
I need to get all the records including which match the criteria and also those which don't match.

Please any suggestions?

Thanks

View 3 Replies View Related

Join Question

Aug 11, 2005

Hello All: I am trying to do a query to join two tables. One is our table of products we sell by a certain manfaucturer. We sell about half the items that Manufacturer makes. The other table is everything that manufacturer makes.

I am updating prices. I am pulling out Product ID from both tables and our price from our table and new prices from the manufactures table. The Query I did only shows matching fields. I want to remove old items from out table, and possibly add new items from the Manufacturer.

I hope this isnt too confusing. Would this be a many-to-many join?

Thanks

View 6 Replies View Related

Left Join

Nov 21, 2005

Hi
I am trying to use left join on the following tables.
Employees
----------
EMPLOYEE_ID
FIRST_NAME
LAST_NAME

and

Weekly_Timesheets
-------------------
EMPLOYEE_ID
WEEK_NO
YEAR_NO
TOTAL_HOURS_WORKED

All I want is to pull all the employees with their corresponding total worked hours. There are cases when some employees forget to fill their time sheets in a week and might not have entries in weekly_timesheets table. Since I want all the employees irrespective of whether they have a record in weekly_timesheets or not I am using left join.

The left join I am using is as follows:

SELECT EMPLOYEES.EMPLOYEE_ID,
WEEKLY_TIMESHEETS.TOTAL_HOURS_WORKED
FROM EMPLOYEES
LEFT JOIN WEEKLY_TIMESHEETS
ON WEEKLY_TIMESHEETS.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID
AND WEEKLY_TIMESHEETS.WEEK_NO = 46;


The moment I am trying to execute this statement Access is crashing. I couldn't figure out what am I doing wrong.

Kind Regards
Bhanu

View 6 Replies View Related

Query With Inner Join

Nov 30, 2005

I am trying to create a query which shows only the newest note for every prospect. I have the code below, but I get a 'Syntax error in JOIN Operation' when I try to run the query and TBLNotes is highlighted.


SELECT TBLProspect.CompanyName,TBLNotes.Note, TBLNotes.NoteDate
FROM TBLNotes AS a
INNER JOIN TBLProspect ON TBLProspect.LeadID=TBLNotes.LeadID
WHERE ((((Select Count(*) from tblNotes where LeadID=a.LeadID and NoteDate>=a.NoteDate)) In (1)))
ORDER BY a.LeadID, a.NoteDate DESC;

Where am I going wrong?

View 1 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

Join Table

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

Join Two Queries?

May 3, 2006

Ok, I have having a problem joing these two queries. Can anyone please help me out here. These are the results of my two queries:

These are the values in the A1 field of QueryA

COCKBURN 2
GT KW 1
GTN 1
IPP GT1
IPP GT2
KALG F5
KALG F6
KWIN C6
MUJA 1
MUJA 2
MUJA 3
MUJA 4
MUJA C5
MUJA C6
MUJA D7
MUJA D8


These are the values in fields A1 and X1 in QueryB:

COCKBURN 86.83
GT KW1 10.44
GTN 1 16.13
IPP GT1 62.84
IPP GT2 62.56
KALG F5 15.12
KALG F6 20.83
KWIN C6 37.78
MUJA 1 23.44
MUJA 3 8.69
MUJA 4 23.44
MUJA C5 84.56
MUJA C6 89.01
MUJA D7 86.22
MUJA D8 84.65


In QueryB, the entry MUJA 2 is missing. What I need is to take the X1 field from QueryB and join those values with the A1 field from QueryA as follows:


COCKBURN 86.83
GT KW1 10.44
GTN 1 16.13
IPP GT1 62.84
IPP GT2 62.56
KALG F5 15.12
KALG F6 20.83
KWIN C6 37.78
MUJA 1 23.44
MUJA 3 8.69
MUJA 2
MUJA 4 23.44
MUJA C5 84.56
MUJA C6 89.01
MUJA D7 86.22
MUJA D8 84.65


How can this be done. I need this in another query. I have tried the join statements, but I get errors every time. Thanks in advance.

View 12 Replies View Related







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