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
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?
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
I have two queries containing this information (example):
View 3 Replies View RelatedI 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?
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 RelatedI 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?
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....
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
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?
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
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
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:
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 RelatedHow 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 RelatedThat 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;
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
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
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
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
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?
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!
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...
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 RelatedOk, 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.