Hi all,
Would appreciate help on this. I have a query which combines the results of 3 queries. Once the underlying queries each has a result I get a result in my query, but if 1 of the underlying queries has no result I get nothing. (I hope this makes sense).
How can I set my query to show results even if the underlying query doesn't.
thanks in advance.
rgs
Ginny
I am using a MS access mdb file to display some record from oracle database using odbc connection.I have a table (linked table) called map_detail in mdb as well as oracle with same table structure.I formed one query in mdb (sql query) select * from map_detail where batch_no="SSO15121".It is always fetching some other result, but when I am changing the query by changing the batch no "SSO15148" it is working fine. I noticed that for cases it working.
View 3 Replies View RelatedOk baisically im doing a quick db for my company that is organising every file stored within the server and monitoring their last access'd, modified etc... I have a query which collates all the files which can be deleted on a server and file count is something alond the lines of 300K+ however i have also Sum'd the file sizes and instead of giving me some mental file size it has given me this result: 1.29659732507E+11
I mean i am no mathematician.. cant even spell it :S anyways im guessing that the number: 1.29659732507E+11 is actually 1.29659732507 to the power of 11?
Is there anyway i can get access to show the actual number i dnt care how long it is i just want a reliable, definitive result i can show my boss!
Cheers
After searching through 18 pages of search results I have not found the answer. Would someone please tell me why this expression is not working in my query?
RCStatement: """&[RootCauseStatement]&"""
The result is "&[RootCauseStatement]&"
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
This query is getting complicated for me... I need help please! :(
I need it to give me the data for people between FirstMonth, Year and SecondMonth, Year...
Right now it's giving me only the FirstMonth, Year and SecondMonth, Year...
I think I need a between there somewhere but not sure where to put it...??
Hope this makes sense.
I'm doing the query in Access 2002
Thanks
Sarah
SELECT [FirstName] & ", " & [LastName] AS FullName, TriOct10.FirstName, TriOct10.LastName, TriOct10.Address, TriOct10.City, TriOct10.Prov, TriOct10.PostalCode, TriOct10.VolScreenCode, DatePart("m",[PRCDate]) AS Month2, DatePart("yyyy",[PRCDate])+3 AS PRCDueY2, TriOct10.PRCDate, TriOct10.MemberType, TriOct10.MemberStatus, TriOct10.ExpandName, TriOct10.RegOrgName, TriOct10.RegisteredRole
FROM TriOct10
WHERE (((DatePart("m",[PRCDate]))=[Enter 1st Month]) AND ((DatePart("yyyy",[PRCDate])+3)=[Enter First year])) OR (((DatePart("m",[PRCDate]))=[Enter Last Month]) AND ((DatePart("yyyy",[PRCDate])+3)=[Enter Last Year]) AND ((TriOct10.MemberType)="volunteer") AND ((TriOct10.MemberStatus)="Active" Or (TriOct10.MemberStatus)="probationary") AND ((TriOct10.ExpandName) Like "*" & [What Area?] & "*"))
ORDER BY DatePart("m",[PRCDate]), DatePart("yyyy",[PRCDate])+3;
Hi all.
I have a sub query that was working until a couple of days ago when it decided to stop.
I have a Table called TblFieldValues which whenever a new value is entered into my relational Db.
Each new Value is given a ValueID and is Dated (full date/time stamp)
My Sub Query ensures that for each FieldID (ie if more than 1) it selects/shows the top most Date....But it is not grabbing all the fields for some reason???
Here is the SQL limiting criteria to a bare minimum (QuoteID)
SELECT Main.FieldID, Main.QuoteID, Main.QuoteTypeID, Main.SubSection, Main.FieldValue, Main.NumberFieldValue, Main.CalcFieldValue, Main.Date, Main.ValueID
FROM TblFieldValue AS Main
WHERE (((Main.QuoteID)=[Forms]![FrmQuote]![QuoteID]) AND ((Main.ValueID) In (SELECT TOP 1 Sub.ValueID FROM TblFieldValue AS Sub WHERE Sub.FieldID=Main.FieldID ORDER BY Sub.Date DESC)));
I had a thought it might be the way the info is put into TblFieldValues, as it is often put in via Code, in fact only when it is put via code is it not showing up as a rule.
So I had a look at the code that inserts it.
strSQL = "INSERT INTO TblFieldValue ([QuoteID], [QuoteTypeID], [SectionID], [FieldID], [FieldValue], [Date], [UpdatedBy]) Values ('" & strQuoteID & "', '" & strQuoteType & "', '" & strQuoteSection & "', '" & strFieldID & "', '" & strValue & "', '" & strDate & "', '" & strUser & "');"
'MsgBox strSQL
DoCmd.RunSQL strSQL
I had Now() in replace of strDate, but tried changing to strDate and diming strDate as Date and then setting strDate = Now() but doesn't really change it in the table.
I am certain it is in this somehow? Any ideas????
Your help will be greatly appreciated...
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies View RelatedI have a query that selects from a table base on 3 entries (Name, BeginDate and EndDate) and should show me 12 other columns and their entries... I have 9 entries for a particular Name, but when i run the query i get 15 results. Some are duplicated but others are not and I don't know where to begin narrrowing it down. Any ideas where to start?
View 2 Replies View RelatedHello everybody!
I 've created a query that copies the records of a table into the same table, creating new records. (I don't know how this is called in english, sorry). For example if have a table with 2 records when the query is run I have 4 records with duplicates. The fields of each record I want to remain the same except for one, called code. For example:
this is the table before the query is run:
surname name code
tracy john 1
spencer bud 1
and this is the table after the query is run:
surname name code
tracy john 1
spencer bud 1
tracy john 2
spencer bud 2
the code is also in an another table and is included in a textbox in my main form. Everything works fine when I run the query but I must type a parameter (code) to create the duplicated table. How can I pass the parameter to the query "automatically", using the textbox value?
Thanx in advance
Hi ,
I am using Access Application with Orcale Db as backend and this application is already in production.
The problem is.. USers Complained that the system is slow, this is because there is refresh (requery) for each Insert (When they click submit, that particular record is inserted and then a refresh has to be done to bring only unused member IDs on the User screen), i had used a Sql query using NOT IN , then i realized and now changed it to NOT EXISTS, but with this new query , if i test recordset. EOF , even though the query returns more than 13000 ..records, EOF sets to TRUE and the other part (not supposed to) gets executed. I really cant figure out why, i took the same query put it in query builder in Access and ran it , it gave 13000 records..but in VBA , this recordset is not giving expected results and so..i am totally confused..The part of the Code is shown below.
Code:newquery = " SELECT A.MEMBER_ID, A.MEMBER_NAME, A.ADDRESS_LINE1.................. FROM TABLE A WHERE NOT Exists ( SELECT '' FROM TABLE B where A.MEMBER_ID =B.MEMBER_ID)AND A.MATCH_LEVEL <> 0 ORDER BY A.MATCH_LEVEL DESC"rst1.Open newquery, Cnt, adOpenDynamic, adLockOptimisticIf rst1.EOF = False ThenForm_PHS_ASSIGN.RequeryForm_PHS_ASSIGN_SUB.RequeryElse MsgBox " No records to Process!" End If
EVEn though it should requery , it gives a message No records to process which is wrong. It works with NOT IN.... BUT VERY SLOW, NOT EXISTS IS FAST BUT DOES NOT GIVE ME WHAT I WANT..
Where am i going wrong..help pls!!!!
THANKS VERY MUCH!!
SELECT Count([Unsafe Act ].[Tag]) AS [CountOfTag], [Unsafe Act ].[Audit], [qryTag].[Tag]
FROM [Unsafe Act ] LEFT JOIN [qryTag] ON [Unsafe Act ].[Tag] = [qryTag].ID
WHERE ((([Unsafe Act ].Date) Between [Forms]![frmSafetyReportOut]![startDate] And [Forms]![frmSafetyReportOut]![endDate]))
GROUP BY [Unsafe Act ].[Audit], [qryTag].[Tag]
HAVING ((([Unsafe Act ].[Audit])=[Forms]![frmSafetyReportOut]![cboConditionAct]))
ORDER BY Count([Unsafe Act ].[Source Of Tag]) DESC;
I am trying to run this query and query works fine however I am trying to run a chart on the report which shows ID's of Tag instead of txt of Tag.
Can someone tell me where I might be going wrong here. The following query works in SQL, but somewhere in the LEFT JOINS area, Access gets a little confused and says I'm missing an operator.
SELECT Tariffs.TariffCPUCID, Tariffs.TariffID, AdviceLetters.ALCPUCID, Tariffs.ALID, Tariffs.ScheduleID, Schedules.SheetTitle, AdviceLetters.[Filing Date], Tariffs.[C&E], SheetsCancelling.CancellingID, SheetsCancelling.CancellingCPUCID
FROM Tariffs LEFT JOIN Schedules ON Tariffs.ScheduleID = Schedules.ScheduleID LEFT JOIN AdviceLetters ON Tariffs.ALID = AdviceLetters.ALID LEFT JOIN SheetsCancelling ON SheetsCancelling.TariffID = Tariffs.TariffID
WHERE Tariffs.Type="E"
ORDER BY Tariffs.TariffCPUCID DESC;
I have a query that updates a field on a table with the value in another field. When I run the query from the Access control panel, the query does exactly what it should do, 100% perfect. When I call the query using DoCmd.OpenQuery in VBA in an OnClick function on a form, it does not work properly, only appending certain amounts of information, leaving some fields blank.Can anyone shed some light on this.the SQL for the query is:UPDATE tblHolding SET tblHolding.CostGRV = [tblHolding]![OrderCost]WHERE ((([tblHolding].[Item Code])=[tblHolding]![Item Code]));It populates another field in the same record in the same table as itself.tblHolding is populated by a Subform on the Form that has the button that calls this query when clicked.Hope I am making sense.
View 3 Replies View RelatedI have a unique query which lists all the films that we are screening over the next 3 months. I have added a COUNT field so that I can see how many of each films we are screening.
The problem is that i get duplicates of some films - and this may be because we may hold several copies of some films. I have attached two images which might explain this better!
What I could do with is knowing how to make it so that i get a list of films booked and how many of each, regardless of which copy of the film is used.
The SQL is:
Code:
SELECT DISTINCTROW dbo_Films.[film name], Count(dbo_Films.[film name]) AS [CountOffilm name]
FROM ((dbo_Films INNER JOIN dbo_filmCopies ON dbo_Films.ID = dbo_filmCopies.tblFilms_ID) INNER JOIN dbo_EventsFlicks ON dbo_filmCopies.ID = dbo_EventsFlicks.filmCopyID) INNER JOIN dbo_Venues ON dbo_EventsFlicks.venueID = dbo_Venues.ID
WHERE (((dbo_EventsFlicks.datefield)>=#8/1/2015# And (dbo_EventsFlicks.datefield)<#1/1/2016#))
GROUP BY dbo_Films.[film name], dbo_Venues.southhub, dbo_Venues.northhub, dbo_Films.Specilaised
ORDER BY dbo_Films.[film name];
Hi there....Im busy tonight trying to get a couple of things working correctly.
I have an UPDATE string that I am trying to get working but I am getting a runtime error 3061 saying 'To few parameters Expected 1'
I have tried the same query using the query builder and it works ok...when I try it in VBA it doesn't work.
This is my code
Dim SQLUpdate As String
Dim SQLWhere As String
Dim strComplete As String
SQLUpdate = " UPDATE tblPersonalInformation SET tblPersonalInformation.DateModified = Now() "
SQLWhere = " WHERE tblPersonalInformation.PersonalID = [Forms]![frmMain]![txtCandidateNumberReadOnly]"
strComplete = SQLUpdate & SQLWhere
Debug.Print strComplete
CurrentDb.Execute strComplete
End Sub
Can anyone see what I am missing or I am doing wrong?
Thanks evryone for your help.
Hello everyone,
Problem: I have an MS Access database in Frontpage that I'm using for my online site. Table is set up w/ a field named, "Artist" (amongst others). In this field I have artist's last name followed by a comma, a space, then the artist's first name. I am using the CONTAINS operator for my sql query statment. Everything works fine on the site if you search only by the first name, last name, or by typing in, exactly, the artist last name, comma, space, then the first name i.e. Presley, Elvis. However, if one were to type in "Elvis Presley", no records return. Why is this? I have also tried the LIKE operator and I get the same results. Here is the code where 45s is my table name and the others are just additional fields of which i have no problems:
SELECT * FROM 45s WHERE (Artist LIKE '%::Artist::%' OR Title LIKE '%::Title::%' OR Record_Label LIKE '%::Record_Label::%') ORDER BY Artist ASC
I'd appreciate any help as I've failed to get a solution from other forums and computer scientists that I've asked.
Thanks!
David
Hello, I have a combo box on a form which lists some names generated from a table.
I would like the selected name to be inputted into the 'critera' of another query called 'qryPBCustLevel' and for that query to be run.
I have tried to code this, but it is crashing at the point it trys to add the name into the query.
Can anyone help? Code listed below.
Sub cmbPB_AfterUpdate()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT DISTINCT tblTempPB.PB_NAME" & _
"FROM tblTempPB"
strOrder = "tblTempPB.PB_NAME;"
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PB_NAME] = '" & Me![cmbPB] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qryPBCustLevel")
qryDef.SQL = strSQL & " " & strOrder
'Open the Query
DoCmd.OpenQuery "qryPBCustLevel", acViewNormal
End Sub
Thanks, Steve. :confused:
I have a query that uses "<>" to find records that are not equal between 2 tables. The formula looks like this:
<> [tblWorkPerformed]![ID]
It works fine as long as there is only one record in tblWorkPerformed, but once I add a second record I get the records I dont want plus the records I do. The records I do what are there twice. I found how to only get single records of each, but it still desplays the records it should not. I tried a dummy database, and it does the same thing. Do I have something wrong or is this just not possible.
Thanks
Anthony
Hi,
I'm designing this system in which each employee has different area of strength (i.e. Math, Languages,..)
if an employee has 2 or 3 area of strength his name appears in the query more than once. I want his name to appear once.
I tried "group by" but it gave me an error. I think I'm doing it wrong.
Please Help!
CS.
I am trying to create a VB script to automate a mailing based on several query result sets from access. I have gotten to the stage that the output is correct but have a problem with the 5th and 6th record set query as they only return one record (When in fact there should be at least two for each).
I don't really understand why this is happeneing as the SQL is exactly the same as in the 2nd record set - which works perfectly. Also I've tested the SQL directly in an access query & there are no errors in the formatting that I can see... correct number of records returned.
Code:
Public emailaddress, ccaddress, Subject, body1 As String
Public baserow, toprow, countnumberofrows, emails As Integer
Public tempdir, projectlistdir, WBPATH As String
Option Compare Database
Option Explicit
[code]....
I'm trying to construct a database to track church funds. I posted before and was given a link to a suitable schema but it was too comnplicated for me! Basically I need to track money given to a church through weekly transactions - money is given weekly through a numbered envelope. Each envelope has a number corresponding to the giver. I came up with this:
tb_parishioners
parishioner_id (PK), fname, lname, address etc
tb_FWO_Nos (FWO = free will offering)
FWO_ID (PK), fwo_Number
tb_contributions
Contributions_ID (PK), parishioner_ID (Both PK), week_no, amount, date
This doesn't really work. One giver can have only one FWO envelope but each envelope has many weeks/dates and I'm not sure how to link this. I want the form to have the week no and corresponding date already there so I just enter the amount. Then it has to be able to switch to a different year. Hope this is clear? Any suggestions?
Thanks.
SELECT Conversion.[Asset Class], iif([asset class]<4000, "1", "2")
FROM Conversion;
:o Okay. I have a frustrating one. This is embarassing to me, because it seems so easy. Anyways, I have a form, where a person could select up to 3 different months and up to 3 different paper types. These combo boxes are listed in the criteria of those fields in my query. If I use the form and try to run my query, it gives me blank results. If I run the query and fill in the pop ups that show up asking for the information that the form is referring to, and I type in the same information, I get the results I expected. What am I missing here? Please help before I go bald!!!
View 8 Replies View RelatedI have 2 tables, Event and Person Particulars.
In an event, groups of 2-5 persons may be tagged to this event by a randomly generated number (using autonumber).
Let's say Tom (social security number: 12345X) is tagged to events 2, 5 & 6. There are of course other persons together with Tom in the above 3 events.
If I would like to find out who are the persons who are in events which Tom had participated in, how do I find them using a query?
Currently, I'm thinking of using a searchform where it would return his "associates" if I just query using his social security number, i.e. 12345X.