Query Tips.
Oct 25, 2005hey,
does anyone have any links i could use to read up on queries?
im very new to Access and i want to create some basic msg boxes and commands etc...
thank you.
ADVERTISEMENT
Tips Or Hints??
Dec 6, 2005I was just wondering if any one had any tips or insider secrets on how to lower the size of a database and/or get it to run faster and more smoothly. I know you can split it but is there any other way? I'm using alot of macros and going from screen to screen there is sometimes a lag. I dont know much in the way of code but im willing to learn. Any links or advice on code how too's would be appreciated aswell...thanks alot I'd also like to say these posts have helped me a great deal and i cant wait until I am confident enough with Access to help out some other people.
Thanks again
Brad
Kens Tips - Defaults
Jan 4, 2007To set the default values for controls, select the control on the tool bar and press F4. The default value properties list will appear. I like to turn off all of the 3D stuff by resetting some the values like 'Special Effect', 'Border Style', etc. This way I don't have to manually change them after the fact...
Thanks Ken have done this - however when I go off and word with a differant mdb file then come back to the original which I had open when I entered the defaults - the defaults have disappeared.
Any suggestions.
Ta
Form Design Tips
May 18, 2006Hi,
Does anyone know of any good sites where they are tutorials etc on how to create advanced functioning forms.
regards
<Control Tips> Not Working......
Dec 30, 2004I am experiencing an odd thing. The program that I have written in Access when I use it on my laptop the control tips do not work. When I use it on any other computer the control tips work fine, does anyone have any idea what the deal is?
View 1 Replies View RelatedTricks And Tips With Exporting Tbl/qry To Text
Jan 4, 2007This is not a question.
I have recently been playing with exporting a query to a delimited text file and have learned some things along the way. Some of this is bound to be old-hat to some of you. However, I thought that it might be useful to post a summary of what I've learned for the benefit of others, like me, who may face some of the frustrations I've been dealing with.
Firstly, in order to export a query or table to a text format other than the default csv, you need to create what is called an 'export specification'. As has been mentioned elsewhere (http://www.access-programmers.co.uk/forums/showthread.php?t=59828&highlight=export+wizard)on this forum, you do this by clicking on the advanced button on the 'Export Wizard'. Pick the delimiter you prefer, click save as, and save the specification with a name that you can remember.
To use the specification in an export, you use the specification's name as the second argument in the DoCmd.TransferText method. (See access help for more detail)
However, things can go wrong!
Firstly, on some occasions, the export wizard fails to open thereby preventing you from accessing the specifications you have created or making new ones. This happened to me and it turns out that the reason was that some code I was running got interupted at one stage and DoCmd.Setwarnings was set to false when it happened. This prevents the export wizard opening for whatever reason.
The solution is to create a sub that sets DoCmd.Setwarnings to True and run it. (Credit to BobLarson here (http://www.access-programmers.co.uk/forums/showthread.php?t=37696&highlight=export+wizard))
Secondly, you may get an error message saying 'too few parameters...expected #' where the # represents a number.
This problem seems to be caused by using a control in a form as a criteria in a query that you're trying to export.
One solution is to simply get rid of the form references in the criteria of the query (Credit Jon K (http://www.access-programmers.co.uk/forums/showthread.php?t=54237&highlight=export+wizard))
But if you need to keep the criteria reference like I did, the best workaround I came up with was to enclose each reference to a form control in the criteria of the query with an Eval("").
Thus, if I were wanting to use a control called 'mycontrol' on a form called 'myform' as a criteria, I would use Eval("[Forms]![myform]![mycontrol]") instead of just [Forms]![myform]![mycontrol] in the criteria line of the query.
Thirdly, you may encounter an error that helpfully says 'Invalid Argument'.
This is due to the presence of a numerical field in the table/query that has its fieldsize property set to 'Decimal'. This is a known bug with access 2000 and solutions can be found at Microsoft's knowledge base (http://support.microsoft.com/kb/263946).
In my case, I resorted to changing the fieldsize properties to Double instead of decimal, and deleted/re-created my export specification after the change and this solved the problem for me.
I hope this post is helpful for others.
Cheers! :D
Tips Needed: Access Books, Graphs, PDF
Jun 26, 2005Hello,
I am about to start a project where I must link an Access front-end database to an Oracle backend. I have about a year of Access development behind me now, and now here is the next challenge.
What I have to do is create analyses of data and output the data mainly in the form of diagrams (i.e. graphs or charts). I have seen how this can be done with Automation in Access in a book by Alison Balter (Mastering Access 2000 Development), by controlling the Excel object etc.
Here are my 2 questions:
1. I am considering buying another book on Access now, something that will contain more examples/methods for presenting information as a graph with the help of Excel. Should I stick to buying an Access book, or should I look into getting something that is more Excel programming oriented, i.e. something like an “Excel programmers compendium”? Any recommendations/tips to get me started would be appreciated.
2. The users want the graphs that I produce to be saved as a .PDF automatically at the push of a button in the Access GUI. How is this done? Do I need special software, or can I use something like PDFWriter to automatically convert the Excel graphs into .PDF’s?
Thanks in advance for the tips and have a nice day! :)
J
Need Some Tips In Creating Relationship Join Lines Between Tables
May 16, 2005Hi, I am new here and need some help with MS Access97. I am looking for a more convenient way to join tables together. The usual way that I know is to drag the field(s) in one table and drop it in the other table (within the relationship window).
Is there any other way that I can do the same without drag and drop (keyboard instead of mouse)? This is real problem to me when I need to do some modification and have lots of tables scatter across few pages and the vertical scroll of the Relationship window is not working properly with drag & drop technique.
Please help. Many thanks! :)
Query In Forms Question (printing A Single Selected Query Record From A Form?)
Oct 19, 2004What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
Queries :: Join Results Of Unmatched Query With Matched Query To Include Null
Mar 24, 2013I 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?
Queries :: Opening Design View Of Append Query Without Timing Out Source Query
Mar 4, 2015In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
UNION Query MS Jet Database Engine Cannot Find The Input Table Or Query.
Oct 28, 2005Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
Queries :: Export Query To Excel Then Delete Query Data From Table
Nov 20, 2013I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
Queries :: Update A Query Based On Results From Another Query Using Count Function
Apr 2, 2013I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
Queries :: Crsosstab Query Doesn't Recognize Expression In Source Query
Aug 12, 2015I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
Queries :: How To Insert A Prompt For Number In A Query To Calculate Against Another Field In Query
Jul 15, 2014I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies View RelatedForms :: Update Form / Query Without Updating Underlying Tables To Query
Jul 23, 2015I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
Blank Fields In Crosstab Query Based On Previous Query
May 31, 2006:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
Show All Records From One Query And The Sum Of A Field In A Related Query Record
Mar 8, 2005I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
Can someone see where I’m going wrong?
Slaughter
slaughter at mizzou dot edu
Queries :: Query To Insert Missing Rows From Another Query / Table
Oct 8, 2013I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15
Queries :: When One Sub Query Has No Records Entire Main Query Is Blank
Oct 1, 2014I have a query with three sub queries, all returning a number from different tables. But when any one of the sub queries returns a blank, the entire main query is blank.
How do I stop this from happening? I have tried NZ() on the sub queries and on the main query, but the blank still happens.
Queries :: Query Error When Data Changes In Crosstab Source Query
Aug 12, 2014I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
Query Is Too Complex... On Report, But Query Runs Fine
Oct 18, 2005Hi...
I have a query that when I run it normally (just click on it) then it runs fine. (It is a union query, getting it's data from 8 other queries (who has their dependancies)
But when I want to run a report from it, Access gives me an error saying "query is too complex".
I am flattered, but I would prefer access to work than say I write stuff that is too complex for it. :cool:
Any ideas?
I am confused by the fact that it runs when I double click the query, but the report bugs it out.
-Reenen
Syntax Error In Query. Incomplete Query Clause
Sep 28, 2005I am really stuck. I have spent two days searcinh different forums trying to solve my problem. I am trying to create an UPDATE q to my Access database. But I get either the: "Syntax error in query. Incomplete query clause" or "Syntax error in UPDATE query".
First of all here's the URL: www.innotec-as.no/login/Kunder
Login U/P either: "alfen" or "thomas".
The page opening up shows the user info, U/P and adress.
viewing the information is working perfectly - but editing it..no way.
When editing and submiting the data the above errors occour.
Try that and you'll also see the SQL I am trying to execute.
The CODE is as follows:
SQLtemp = "UPDATE 'Brukere' SET"
SQLtemp = SQLtemp & " 'navn' = '" & request("Navn") & "', "
SQLtemp = SQLtemp & " 'epst' = '" & request("Epst") & "', "
SQLtemp = SQLtemp & " 'Pass' = '" & request("Pass") & "', "
SQLtemp = SQLtemp & " 'Firma' = '" & request("Firma") & "', "
SQLtemp = SQLtemp & " 'BAdresse' = '" & request("BAdresse") & "', "
SQLtemp = SQLtemp & " 'BPostAdr' = '" & request("BPostAdr") & "', "
SQLtemp = SQLtemp & " 'PAdresse' = '" & request("PAdresse") & "', "
SQLtemp = SQLtemp & " 'PPostAdr' = '" & request("PPostAdr") & "', "
SQLtemp = SQLtemp & "WHERE 'Bnavn' = '" & request("Bnavn") & "'"
Response.Write(SQLtemp)
Response.End()
conn.Execute(SQLtemp)
rs.Update[/COLOR]
The finished SQL statement looks like this:
UPDATE 'Brukere' SET 'navn' = 'Alf Byman', 'epst' = 'alf@baccara.no', 'Pass' = 'alfen', 'Firma' = '', 'BAdresse' = '', 'BPostAdr' = '', 'PAdresse' = 'sdfg', 'PPostAdr' = '', WHERE 'Bnavn' = 'alfen'
I have tried to user single quotes, doubble quotes, brackets etc. nothing works.
The code I use for connection is as follows:
<!--#include file="../adovbs.inc"-->
<%
dim conn, rs, SQLtemp
' DSNless connection to Access Database
set conn = server.CreateObject ("ADODB.Connection")
rs="DRIVER={Microsoft Access Driver (*.mdb)}; "
rs=rs & "PWD=uralfjellet; DBQ=" & server.mappath("../../../../db/kunder.mdb")
conn.Open rs
I'll be very HAPPY for some expert help on this.
Combo Box Query To Post Result To A Seperate Query
Sep 27, 2006Hello, 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:
Query - Lookup/Query Repeating Data Entery
Nov 28, 2006I am creating a database for a hyperthetical car hire company.
I have a field with a lookup/query. The data that this query searchs for is entered into my table/form already. Is there anyway of avoiding repeating this data twice?
Iain