SELECT * FROM ((TBL_Properties INNER JOIN TBL_Property_Landlords ON TBL_Properties.Property_ID=TBL_Property_Landlords. Property_ID) INNER JOIN TBL_Landlords ON TBL_Property_Landlords.Landlord_ID=TBL_Landlords.L andlord_ID) INNER JOIN TBL_Agents ON TBL_Properties.Agent_ID=TBL_Agents.Agent_ID;
so far. But I need to join the results from another query which will be calculating SUM(Pay). Is it possible to INNER JOIN to a: (SELECT ....) nested query?
I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..
Code:
Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity from( SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center FROM [Open Items] )a left outer JOIN [Open Items] AS [Open Items_1] ON (a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity) Where ([Open Items_1].MonthEndDate=#8/31/2014#)
What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.
I want my query to calculate a date based on: 1) A Type field from my table 2) A calculated date based on other query fields.
My query has the following fields: [Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then if [Action 2] Is NotNull, Null if [Action 1] is NotNull, [Action 1] + 10 workdays if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
[Code] ....
I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.
Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.If you want a more specific description of the problem, read on, otherwise don't read on.Hi All,So I hope I can explain this ok....here goes....I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.Thanks so much. Dillon
I have four fields that typically have a quantity amount in them.
I also have a field with a date.
I would like to say something like this
If fielda = 0 and fieldb = 0 and field = 0 and fieldd = 0 and in the date field, the date is >30day from today’s date add a 1 to this field, otherwise leave it blank
I have been doing some experimentation with Queries and I have discovered something unusual.
If I have one query and I use the totals feature (only using group by) I get all the records based on the inputed criteria
If I then use that same query as an underlying recordset for a second query and I use the totals feature (only using group by) in the second query... I only get the first record of each type. See query results below..
I am trying to achieve the results of the second query in the first query (DON'T WANT NESTED Queries) and I don't really understand why the results of the queries are different when everything else is the same other than one being nested.
Example below
First Query Results (using totals with group by)
1 a 1 a 1 a 2 a 2 a 2 a 3 a 3 a 3 a 4 a 4 a 5 a
Second Query Results with the First query as its recordset (using Group By)
hi guys, i have this sql that filters 2 tables to give me the highest number against a record and then i want to update the 1st table. but it dosnt seam to work it brings back the correct data but the sql has generated a non-updatable query.
any ideas?
UPDATE [SELECT ARCtblErrorInfo.Uber, Max(ARCtblHistory.Status) AS MaxOfStatus FROM ARCtblErrorInfo LEFT JOIN ARCtblHistory ON ARCtblErrorInfo.Uber = ARCtblHistory.Uber WHERE (((ARCtblErrorInfo.Status)=121)) GROUP BY ARCtblErrorInfo.Uber]. AS test INNER JOIN ARCtblErrorInfo ON test.Uber = ARCtblErrorInfo.Uber SET ARCtblErrorInfo.Status = [test].[MaxOfStatus];
I have done smoe baisc queries to sort, filter and report on data.How do I create a nested query where I don't repeat all the information across the query results line? Is there a way I can build a nested query to give all the SMCode codes associated with each appointment in one column separated by a coma?I have a table with appointments (APPTS):
RecID ADate ATime Durantion PVID Desc Case#
Then I have a table with Orders related to these appointments (ORDERS):
RecID Case# SMCode SMDate
Here's my query:
SELECT DISTINCTROW Appts.RecID, Appts.ADate, Appts.ATime, Appts.Durantion, Appts.PVID, Appts.Desc, Orders.SMCode FROM Appts INNER JOIN Orders ON Appts.[Case#] = Orders.[Case#];
I have different product groups and they can have one of 3 rental rates(5,4,3%) I have written a nested query but I keep getting parenthesis errors, the last one I got told me I must enclose iif functions in parentheses. I know its probably a small thing but I just cant see it. By the way the server is German so ; need to be used instead of ,.
IIf([view_India]![Sub_Group] In (1730;1810;2870);5;iif Left([view_India]![Sub_Group];2) In (01;02;21;24;29) ;5;iif([view_India]![Sub_Group] In (3310;3330;3340;3360);3;4))))
I have made this sql statement in VBA where rubriek = a variable but I dont know how to implement a variable in a nested query.
SELECT "rubriek, SUM(verkoopprijs) as prijzen FROM voorwerp (inner join VoorwerpInRubriek on voorwerp.voorwerpnummer = VoorwerpInRubriek.voorwerp) inner join Rubriek on rubriek.rubrieknummer = VoorwerpInRubriek.rubriekOpLaagsteNiveau where rubriek in (select rubrieknummer from rubriek where rubriek = variable name ) group by rubriek"
Do I have to treat it as a normal select query or is there something speical that I have to do ?
The situation is I have two queries, one uses the other as a source, and the other uses DSUM with the source query as it's domain.
For example: I have one query called RetailCDState, this prompts the user for the name of a state (i.e. Texas), and then prompts for the name of the CD (i.e. Joshua Tree). The query returns the number of CD's sold for each retail store entered for that state.
I have another query called, TotalRetailState. This query uses RetailCDState as it's source. I pull some fields down into the QBE so that the CDname, state, artist name, etc will be shown. This works.
HERE IS THE PROBLEM. I have created 3 expression fields: TotalCDSOLD, TotalCDSHIP, and TotalCDRETURN. In these expression fields I have used DSUM, I want to use the query RetailCDState as the domain, which the help files said I can. However, it's giving me an expression error saying that it can't find the 'state' or 'CD Name', which is what should prompt the user when the whole thing is executed.
I get 3 error messages, one for each expression field, and then it prompts the user for the correct input, but then doesn't display the information. Here is an example of my DSUM line TotalCDSHIP: DSUM("[NumofCDSold]","RetailCDState")
I shouldn't need any criteria, because the results of the RetailCDState is what I want to total.
I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:
Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))
I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.
Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code: SELECT [tblBalances].[BalanceDate] AS AsOfDate, (SELECT COUNT([tblIssues].[IssueID]) FROM [tblIssues] WHERE [tblIssues].[Flag] = True AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code: (SELECT TOP 1 [tblComments].[UpdatedWhen] FROM [tblComments] WHERE [tblComments].[IssueID] = [tblIssues].[IssueID] AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate] ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
I have a report that is based on nested (I think thats the phrase) query's.
Complicated Query based on another query (so I can't see a way to get at the the source SQL to change or use elsewhere)
This gives a list of say 20 records I generally want printed. I use the exact same query criteria with a separate update query to add the same to a table.
However I then wanted to just pick one with exact matching ID's I select on a form.
I could not see an easy way to apply this without making another set of nested querys which seems a little excessive
Anyway, an easy way for the printed report to do this is a simple filter added after, works great.
I can't see a way to do the same for an update query.
I was wondering if I could get the record source of this report and add to my table. I have tried with
' Dim db As DAO.Database ' Dim rs As DAO.Recordset 'Set db = CurrentDb 'Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset) ' Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
And dozens of variations over some hours but a variety of errors mainly "too few parameters."
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";
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 used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items. How can i make 3rd query which will give me all but taken items from query1? (of course items from query 1 are in query2) thx in advance
[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:
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.
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;
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.
