Hi All,
Quick question...
If I were to do a right join on four different attributes E.g. a = a b = b etc etc and the criteria would be Attendance is null. I am getting the results back which I want but the columns which I have joins on are blank? Any ideas why?
Any help appreciated....
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 the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
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
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?
I hope someone out there can help me, I'm not an SQL experianced person, I only know enough to make problems. Anyway I believe my problem below is probably simple to someone that knows SQL very good. I have a link to a sample Access database if anyone is interested in helping me solve this.
The Sample Database (http://shadesoffire.net/files/db1.mdb)
Have a problem and can't quite seem to get it solved. I have simplified the whole thing to make it easier to understand.
I want a report that shows me the Product Category and Sum from work orders that were > $100.
I'm not quite sure how to do this, I've tried several methods of Joining and Sub queries but nothing really works. All I get in my results is product categories > 100. See I would expect the product category to be on the report even it it were $55 if it came from a work order containing total costs > 100.
I have manually supplied the results that I want, any help would be greatly appreciated. My real problem deals with larger results and numbers but these are the basics.
Linked is an Access database with one of my queries that show all work orders > 100. I would expect all those product categories in my summary report.
Redesign of the database is not an option.
These work orders are all > 100, the total on all reports should be $511
WorkOrderNo Total Cost
00001 $120.00
00003 $268.00
00005 $123.00
These product categories are associated with the above work orders and this is the result I am looking for.
Prod Cat Costs
12 $176
19 $89
22 $233
91 $13
The above results would come from this:
WorkOrderNo LineNo PartNo Cost Cat
00001 1 ABC $55.00 22
00001 2 XYZ $65.00 12
00003 1 ABC $89.00 22
00003 2 XYZ $77.00 12
00003 3 123 $89.00 19
00003 4 456 $13.00 91
00005 1 ABC $89.00 22
00005 2 XYZ $34.00 12
Hi all, this is my first post to the board. I’ve been searching through the forums but haven’t been able to put all the pieces together on my problem. First some background on my application:
I have 3 tables ISSUES, UPDATES, and PRTS. Each record in ISSUES has several records in UPDATES, and may have a single record in PRTS. The issues and updates tables have 3 “clones” (everything except the table name prefix is the same) that are used by other departments. All of the clones use the same PRTS table.
I am trying to put a query into SQL (I first built it using the query builder & an example from Microsoft for a query to find the last record) that will return each issue, along with the last update associated with the issue. My intent is to switch the table names using code depending on the user’s selection. I know how to switch “ElectricISSUES” to “PowerISSUES” below, but I do not know how to switch the source for the “qryLastUpdate”. Any help would be greatly appreciated.
issueSQL = "SELECT ElectricISSUES.Index, ElectricISSUES.Status, ElectricISSUES.Description, ElectricISSUES.Driver, ElectricISSUES.Champion, ElectricISSUES.PRTS, ElectricISSUES.SubDate, ElectricISSUES.DateClosed, ElectricISSUES.Diamond, Date()-[Subdate] AS DaysOld, qryLastUpdate.Update, qryLastUpdate.Udate, qryPRTS.Step, qryPRTS.[Next Step], qryPRTS.Designee, qryPRTS.[Ewo#], qryPRTS.BreakPointDate " & _
"FROM (qryLastUpdate RIGHT JOIN ElectricISSUES ON qryLastUpdate.UIndex = ElectricISSUES.Index) LEFT JOIN qryPRTS ON ElectricISSUES.PRTS = qryPRTS.[Prob#] " & _
"WHERE (((ElectricISSUES.Status) = 'closed')) ORDER BY ElectricISSUES.Index;"
db.OpenRecordset (issueSQL)
I really don't know how to describe this, so bear with me!
i have a table of sales data 'tSales' that holds 3 years of transactions.
Fields are
Acc
Cust
Market
Prod
Qty
Value
Cost
Material
Labour
I have another table 'tLabRate' that holds our labour rates
e.g. in 2004 £20ph, 2005 £25ph etc
in the Form:
RateID
LabRate 20.00 25.00
LabFactor 0.6666 0.8333
LrFrmDate 01/01/2004 01/01/2005
LrToDate 31/12/2004 31/12/2005 etc
What i am trying to do is display the correct factor for the point in time, e.g.
LabFactor where sDate > than LrFrmDate AND < LrToDate
How do i do this in a query?? i can't join i don't think becasue there isn't a common field....?
Thanks
Graham
Hi all
How are you?
I have a small question....
I have a db with 2 tables (Contacts, Products).
Contacts table has 6 columns (CustomerID, CustomerName, Bread, Tea, CD, Pencil)
Products table has 2 columns (ProductID, ProductName).
I want a query that show this result:
Customer NameProduct Name
JohnTea
JohnCD
MarkBread
MarkCD
MarkPencil
JaneTea
TonyBread
TonyPencil
How can I do that??
Kindly, find attached the data base.
Sorry for bothering you.
Hi All,
Head is battered with this join query. I have 2 tables which I want to join, one being individual data and one being budget data. I want to Sum the budget Cost And Budget admissions dependant on the joins.
SELECT FyldeCoastActivity.Speciality AS [Specialty Code], dbo_Specialty.SPECTEXT AS [Specialty Desc], FyldeCoastActivity.GP_Practice, FyldeCoastActivity.[Activity Type], Count(FyldeCoastActivity.Activity) AS CountOfSUSActivty, Sum(FyldeCoastActivity.BasicSpellCost0708) AS BasicCost, Sum([PBC month 8 budget].[Budget Admissions]) AS [SumOfBudget Admissions], Sum([PBC month 8 budget].[Budget Cost]) AS [SumOfBudget Cost], [BasicCost]-[SumOfBudget Cost] AS [Cost Difference], [BasicCost]/[SumOfBudget Cost]*100 AS [Cost %]
FROM ((FyldeCoastActivity LEFT JOIN dbo_Specialty ON FyldeCoastActivity.Speciality = dbo_Specialty.SPECCD) LEFT JOIN [Al Providers] ON FyldeCoastActivity.Provider_Code = [Al Providers].Trust_Code) LEFT JOIN [PBC month 8 budget] ON (FyldeCoastActivity.ProviderCode1 = [PBC month 8 budget].[Provider Code]) AND (FyldeCoastActivity.Speciality = [PBC month 8 budget].[Specialty code]) AND (FyldeCoastActivity.GP_Practice = [PBC month 8 budget].[Pract code]) AND (FyldeCoastActivity.[Activity Type] = [PBC month 8 budget].PODSUS)
WHERE (((FyldeCoastActivity.[Financial Month])<=8) AND ((FyldeCoastActivity.ExcludeReason)=0 Or (FyldeCoastActivity.ExcludeReason)=99 Or (FyldeCoastActivity.ExcludeReason) Is Null))
GROUP BY FyldeCoastActivity.Speciality, dbo_Specialty.SPECTEXT, FyldeCoastActivity.GP_Practice, FyldeCoastActivity.[Activity Type]
HAVING (((FyldeCoastActivity.GP_Practice)="p81129") AND ((Sum(FyldeCoastActivity.BasicSpellCost0708)) Not Like 0))
ORDER BY FyldeCoastActivity.Speciality, FyldeCoastActivity.[Activity Type];
The query is coming back with Very high figures which leads me to believe that the joins are not working and just summing all the data in the budget table.
I have uploaded a screeen shot of the query.
Any help would be much appreciated.
Thanks
Hi,
I am a SQL Server user and there are a few Access differences which seem to be getting the better of me... Can someone answer a simple question? I have the following query:
SELECT tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbCreditSchedules.f_CreditName, tbUserGroups.f_Created, tbUserGroups.f_Updated, COUNT(tbUsers.f_ID) AS Users
FROM tbUserGroups
LEFT JOIN tbUsers ON tbUserGroups.f_ID=tbUsers.f_UserGroupID
INNER JOIN tbCreditSchedules ON tbUserGroups.f_CreditScheduleID=tbCreditSchedules. f_ID
GROUP BY tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbUserGroups.f_Created, tbUserGroups.f_Updated, tbCreditSchedules.f_CreditName;
This comes up saying that I am missing an operator? From browsing the forum, someone suggested putting parenthesis such as:
SELECT tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbCreditSchedules.f_CreditName, tbUserGroups.f_Created, tbUserGroups.f_Updated, COUNT(tbUsers.f_ID) AS Users
FROM ((tbUserGroups LEFT JOIN tbUsers ON tbUserGroups.f_ID=tbUsers.f_UserGroupID) INNER JOIN tbCreditSchedules ON tbUserGroups.f_CreditScheduleID=tbCreditSchedules. f_ID)
GROUP BY tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbUserGroups.f_Created, tbUserGroups.f_Updated, tbCreditSchedules.f_CreditName;
However, when i do that... It says i haven't supplied a parameter! As far as i'm aware i'm not trying to supply a parameter, its just a simple join.
I would appreciate any advice.
Thanks,
Hi,I am very bad in queries and need your help on this one.I am trying to run a simple delete query having an inner join but it gives an error every time. The error being "Specify the table containing the records you want to delete"here are the details of the simulated table and the query than I am trying to run table1 : name number abc 1 xyz 2 table2 name cityabc AQuery that I am trying to run is:Delete Table1 from Table1 INNER JOIN Table2 on Table1.name = Table2.name;Thanks
View 2 Replies View RelatedHi! Im wondering why my code below Does not work!.... any suggestion???
strStatement = "SELECT SessionData.RunNumber, SessionData.SessionID, SessionData.SequenceNo, SessionData.SubjectID, SessionData.Latency, SessionData.BeamBrk1, SessionData.BeamBrk2, SessionData.BeamBrk3, SessionHeader.SessionDate, SessionHeader.ChamberNo" & _
"SessionHeader.GroupID, SessionHeader.SubjectVariables, SessionHeader.TestDefName FROM SessionData" & _
"INNER JOIN SessionHeader ON SessionData.RunNumber = SessionHeader.RunNumber;"
getQueryname = InputBox("Enter a name for New Query?", "Enter a Label or Name for Query", "Enter Query name/Label Here", 500, 700)
Set qryEmployees = curDatabase.CreateQueryDef("getQueryname", strStatement)
I've have used the usual code format for creating a column of row numbers for my query, but it doesn't seem to work. Sequence: DCount("InventoryID","Chemical Location","InventoryID <=" & [InventoryID]) I got this error The Microsoft Office Access database engine cannot find the input table or query 'Chemical Location'. Make sure it exists and that its name is spelled correctly.
So I tried this: Sequence: DCount("InventoryID","qexpFacilityInventory","InventoryID <=" & [InventoryID])And I got this error: The expression you entered as a query paramenter produced this error: Business Emergency Plans can't find the form 'frmMain' referred to in a macro expression or Visual Basic code
I think my code doesn't work because its a join query; so I'm searching for an answer. I have attached an image of the query builder in Access 2007 to show my problem. Also I'm showing the SQL code. Can someone help me with putting row numbers in a Join Query "qexpFacilityInventory"?
SELECT tblChemicalInventory.[Business Name], tblChemicalInventory.[Item Number], tblChemicalInventory.[Chemical Location], tblChemicalInventory.[Chemical Name], tblChemicalInventory.[Common Name], tblChemicalInventory.[Map ], tblChemicalInventory.[Grid ], tblChemicalInventory.[Largest Container], tblChemicalInventory.[Average Daily Amount], tblChemicalInventory.[Maximum Daily Amount], tblChemicalInventory.Units, tblChemicalInventory.[Storage Container], tblChemicalInventory.[Usage Purpose], tblChemicalInventory.[Annual Waste Amount], tblChemicalInventory.STATUS AS Reviewer, tblChemicalInventory.STATUS AS [Modified Yes/No], tblChemicalInventory.STATUS AS Comments
FROM tblChemicalInventory INNER JOIN tblChemicalProperties ON tblChemicalInventory.ChemicalID = tblChemicalProperties.ChemicalID
WHERE (((tblChemicalInventory.BusinessID)=[Forms]![frmMain]![ctlGenericSubform].[Form]![BusinessID]));
Thanks,
PC
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).
I have a table from which I want to extract selected fields from each record and write a new corresponding record in a separate table (like a summary of each record). This process will need to be repeated on a regular basis. I am using an append query so far but this appends 0 rows unless I set the join type on the query to option to 2 (I think a left outer join) - this means that each time I run the query - it appends records that have already been appended previously. What I want is the query to append only those records that exist in the primary table but don't exist in the secondary one. Can't see a join type that will do this for me - have experimented with the alternatives but it just creates more duplicates.
View 2 Replies View RelatedI am a brand new user of Access, but need to know how to do an advanced join query. I have two lists joined on one field, but the data across the two fields is not always consistent. For example, one list has a title listed as NY Branch, but another has it listed as New York Branch. How do I have access compare only a certain number of characters, rather than the entire entry?
I'd appreciate any guidance!
Hi, i am having some problems with a delete query,
This is because i am using 2 tables in the query
TBL_12a_Requisition_Sub
and
TBL_12_Purchase_Requisition_Sub
The 2 tables are linked by fields both named [Requistion Number]
Basically where the 2 tables match i would like to delete all records in TBL_12_Purchase_Requisition_Sub. The query works fine when in design view but when running it you get the error message (specify the table containing the records you want to delete). The problem with this is i have already done this in the query.
I have looked over the net and it looks like you need to do something with a select statement in sql, but i can't figure it out. Any help would be much appreciated. Thanks.
I have 2 tables. one (Data_Participant) has all of the data on individual participants. the other (Data_Main) has the data of how they participated per year. my Data_Participant table has a primary key (ParticipantID) which does not allow duplicates and the Data_Main table is setup w/ a non-primary key field (ID) which will allow dupes. these two fields are joined in my query with the Data_Participant.ParticipantID set to include all records and and only those records from the Data_Main.ID field where the records are equal.
to me, the join properties being setup like this would allow me to put any type of search criteria i would like under the Data_Main table fields and i would still get all of the Data_Participant's tables as a result, but only those from Data_Main that i had filtered.
ultimately, i'd like to see all of my Data_Participant data and only the Data_Main data that makes it through my filter/search criteria. am i not understanding how the join properties work?
Hi,
I am trying to do an update on a table with a query which has a join as follows but getting an error:
UPDATE RoleObjects
SET AccessType = 1
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
I tried to debug the code and found that the following part of the code with the Select clause works fine.
SELECT *
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
Do u have any suggestions.. ??
Thanks a lot for your time.
I'm going to try and explain this the best way I can so please bear with me if it doen't make total sense.
I have two tables
tblSales (is used when am employee makes a sale)
employee
date
tier level
tblTierLevels (shows all the different tier levels and the pay amounts)
tier level
pay amount
I want to create a report which groups by employee and shows every tier level even if they didn't sell any of those tier levels. For example...
Jane Doe Units Pay Total Pay
Tier Level 1: 0 $5.00 $0.00
Tier Level 2: 2 $6.00 $12.00
Tier Level 3: 0 $7.00 $0.00
If I create a query with a join to show all from tblTierLevels and only those from tblSales where tier level = tier level if the employee did not sell any of a certain tier it does not display their name. So I can't group by name and show all tier levels.
If I create a crosstab query this will show all employees as well as all the tiers however this lists the tier levels across (as columns) I want to list the tier levels as rows.
Does anyone know how to do this?
i have joined a jobs table and payout table together and performed a query.
however i would like to perform SUM by the job_id but crew_id fields are duplicated.
(the payout table comes from a subform that doesnt differentiate crew type so my SUM totals all the payouts whereas i want to sum the payouts by job_id when the crew id are repeated.
TOTAL JOB_ID CREW_ID
$5000 2 2
$3290 2 2
$1000 2 7
$2500 2 7
need to return $8290 for one query (5000+3290)
then $3500 from another (1000+2500)
I am trying to build a query that shows me the activity of my customers. I have a database that amongst other things holds two tables which i am trying to query.
tbl 1 - Customers
tbl 2 - Enquiries
What i eventually want to do is create a report that shows which customers have made enquiries and which ones haven't on a weekly and monthly basis.
the fields brought out of tbl1 are just the companies name, and sales person responsible for the account. Now to get all the customers to show i have learnt that you can do a left join on the query which will show all the records in the left table (tbl1) which is great and i can then limit the customer accounts that show by adding query criteria to the sales rep field from tbl1. The problem that i am having is when i try to limit the time period i.e weekly, monthly. when i enter criteria in the EnquiryDate field from tbl2 the query stops showing all the customers and only shows those that match the date criteria. Can anyone tell me how to prevent this from happening.
Thanks
I have a query where I'm selecting fields from two tables - Students and Attendance. I have selected option 2 in my Join Properties, i.e "Include ALL records from Students and only those from Attendance where the joined fields are equal". The tables are joined by StudentID. Either I'm misinterpreting what the join is supposed to be doing or it's not working.
I need to see every record in Students regardless of whether or not they have a record in Attendance. I need to calculate absences and tardies. If there's a matching record in Attendance, I subtract the date absent from the total days. If there's no matching record in Attendance, I still need to calculate total days and show it. As it is now, if there's no matching record in Attendance, it doesn't do anything. How can I get it to do what the join says, "Show ALL records in Students as well as those that match in Attendance?
Thanks