I'm new to Access and have been learning how to use it for the better part of a year. I've done all the Microsoft online tutorials relevant to the work I need to do with it, and gotten a few books out of the library besides. In general, I self-teach very well given a good resource.
Here's my problem: the main area in which I need to be proficient is running queries, and I cannot find a good, comprehensive explanation of how to construct expressions or set up calculated fields.
In case I'm not clear (I've had quite a time just figuring out what to call what I need), I'll give you an example. I was able to arrange a short tutorial with someone in another department. As part of a query, she used the following statement in the "Field" section to convert date information stored as mm/dd/yyyy into just the year:
Year: IIf([referraldate]<#1/1/2003#,"2002",IIf([referraldate] Between #12/31/2002# And #1/1/2004#,"2003",IIf([referraldate] Between #12/31/2003# And #1/1/2005#,"2004",IIf([referraldate] Between #12/31/2004# And #1/1/2006#,"2005",IIf([referraldate] Between #12/31/2005# And #1/1/2007#,"2006")))))
This is the kind of thing I want to learn how to do. Unfortunately, the Microsoft tutorials don't do much more that give examples of different expressions and functions; I feel like I've been given a handful of sample sentences, a few nouns, and a few verbs--and then told to go speak English.
What I need is a comprehensive guide that not only gives me the building blocks of expressions, but tells me how to combine them into a syntactically meaningful statement--so I know what order things go in, where commas and parentheses should be, etc. Both online or print materials are fine--I've been looking on my own, but with no luck.
I have created a simple database named: tblcustomer.mdb with fields of Company, Address and Tel
I would like to create another database which records the jobs that I have done for these companies. I name it tbljobrecord.mdb with fields of Company, job 1 and job 2
My question is, how can I link the Company field (tblcustomer) with the Company field of (tbljobrecord) so that everytime I add in a new Company (tblcustomer), it will be updated on the tbljobrecord too.
I am trying to write a query showing me all tv shows on right now.
The design of the tables are unchangeable because they are given to me.
The 2 important fields in the Schedule table are:
time (which is a date/time value, for when the program starts ex: 2/3/05 8:30AM)
durations (a number giving the program lenght in minutes ex: 60 (1 hour)
I want to write a query showing me what is on right now, then problem is I do not want to use MSAccess functions like DatePart or Cint ect... because the SQL from this query will be used through on a webpage using a jscript activex ADO connection and I dont think Functions like DatePart() are supported.
Having had little luck in getting any concrete information about effective ways to manage ODBC data, and after days of long trials and errors, I thought at least I could write up a summary of what I've found to work well for ODBC sources and hopefully others may be able to contribute to this.Disclaimer: This is a far cry from being an authoritative and is woefully very subjective, being written by me and myself, using a MySQL server and Access 2003. I do hope that others who are able, can contribute more information to make this somehow more useful for those who would like to use Access as a front-end client.There are three principal issues that must be considered when you are using a ODBC data sources:1. Numbers of connections and different flavor of connections.2. Size of recordsets and network traffic.3. Binding forms to ODBC data sources.As a starting point, one should read the whitepaper on Jet and ODBC Connectivity. (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp ) This was for Jet 3.0, but should be valid for 4.0 as well. ODBC Driver and ConnectionsThe next thing is to understand what your ODBC driver is capable of. In a connection string, there is a parameter for "Options", which is usually a long integer. You should look at the ODBC driver's manual to ensure you have all options you need turned on. A good example would be to ensure that a certain data type is correctly mapped to Access's data type. In my case, I ensured that Big Integer (64-bit) were turned off because Access does not support this (and thankfully, I don't need it anyway). By far most important thing you want to look for is to ensure that the driver supports two things- Multiple statements and Active statements. Jet does try to pool all queries to the server into one connection whenever possible, but if the driver cannot support multiple statements over single connection, Jet will open another connection to submit a statement. Therefore, if you have a form that has a combobox, Jet will send two queries, one for form's recordsource and another for combobox's rowsource. In case of lack of support for multiple statement, Jet will need two connections. Even if your driver supports multiple statements, Jet may find it necessary to open a second connection if the driver cannot have more than one active statement. Active statement is when you fetch a big recordset and need to wait a bit for the full set to come over the wire. So back to that form with a combobox, if Jet find that the form's recordsource will take a while to be fetched, it will go ahead and open second connection to populate the combobox in order to allow for 'instantaneous' loading of a form from a user's POV.Wherever possible, keep numbers of connection to a minimum. As mentioned before, Jet will try to pool all statements into one connection wherever possible, but Jet cannot help you out if you issue a query using a new ADO connection, DAO ODBCDirect workspace, or create a recordset within VBA. If you want to avoid the additional overhead of another connection, you should use a stored query instead, and make sure it has a Connection string in its query property window set, so Jet knows that it uses ODBC data and pools it with its open connection to the ODBC server. The stored query can then be called within VBA without incurring another connection.DSN and DSN-less/ODBC and OLE DB connectionsThere are two ways to create connections, by using Data Source Name (DSN) or direct connection. To find right connection string, look at Carl Prothman's excellent website listing all possible connection strings (http://www.carlprothman.net/Default.aspx?tabid=81). Some people say connecting to DSN is better than without. Others say it's faster without DSN. One thing for sure is if you use DSN, you will need to distribute the DSN file to your users along with the finished database. I would recommend experimenting with both connection flavors before deciding which is better suited for your needs.To make a DSN connection DSN-less, look at Doug J Steele's example (http://www.accessmvp.com/DJSteele/DSNLessLinks.html) or perhaps this Add-in by Paul Litwin (http://www.mvps.org/access/modules/mdl0064.htm).Also, make sure you know whether you have different drivers available. I know for an example, some people prefer third-party drivers for Oracle over one supplied by Oracle themselves. Furthermore, some drivers are OLE DB which is supposedly better than ODBC (I say supposedly only because I am skeptical of Microsoft's promises of new and latest technology superseding a older technology, then dropping their claims and going back to older technology). Binding formsTo Jet's credit, it is quite intelligent in retrieving just enough rows to populate the bound form, and will continue to fill up the recordset periodically while a user is working on a row. However, Jet has some quirks where it can do something very stupid, such as asking for multiple full table scans. If you bind a form to ODBC table, Jet will do a full table scan. The simplest solution would be to add a WHERE clause to make the recordset smaller. But by far the better solution would be to set the recordsource's connection string to use ODBC instead of "(current database)". Jet will then query for keys, which it must have locally in order to manage a recordset, then afterward query a few rows at a time. If you can manage to keep keys smaller, all the better.Furthermore, if you want to use subform, do not use Master/Child linking fields. This makes Jet go ape-shit, issuing several queries to show tables and index which is quite unnecessary. Rather, leave the link blank, and set subform's recordsource with a WHERE clause to match the parent form's key, so Jet will only ask for rows that match the key only without asking for indexes and table status every time you move around.An additional benefit of making all queries for forms' recordsource an ODBC query is that you now have more control over how you handle those forms *with* Jet's intelligence. For example, you now can start transaction across multiple forms with subforms and commiting/rollbacking as you see fit, which would have not been possible using Access itself. I have been able to rollback the changes in two parent records and their related child records by issuing a SQL Pass-through query which simply says "Rollback;" and nothing was changed for any one of records, just as expected. If you have a combobox or listbox on a form, this will mean another query in order to fill in the rowsource. Ideally, you want to keep some tables local to front-end clients, especially for tables that will never change (e.g. a list of states or provinces for example). For tables that may be updated peridocially but is otherwise select-only, you need to decide whether you want to make it a dynaset or a snapshot. For a small set, snapshot is faster, but for a larger set, dynaset is faster. You will need to experiment with the rowsoource to ensure that the network traffic and time to load the rowsource is satisfactory. One problem is that Access won't accept an variable (at least I have been unable to do so) for a stored query's connection string, so if you need to change a connection string (e.g. you want to use different set of options, perhaps?), you would need to do this by hand, or at least write a function to loop through queries and updating the connection strings. Haven't tried that yet, but would imagine this is very possible.Keep an eye on SQL log when developingYou definitely will want to have the server write a log of what queries it has received from Jet to give you feedback in ensuring that Jet doesn't do anything stupid. This has told me far much more about Jet than working within Access environment. Unresolved issuesThere is only one thing I haven't yet worked on- Sharing a recordset for different controls. Suppose we have a form with a combobox and a subform, both which use same table as a rowsource and recordsource, respectively. In this case, Jet will issue two separate query to the server, even if they may use same recordset. If anyone has been able to show how to get such controls to share recordset, that would be cool.Also, I'm a bit worried about scattering connection strings all over the place, especially that it will contain a password. As I see no point in asking users to authenicate themselves every time they use a query with a ODBC connection string, given that variables can't be used in query's connection string. Would MDE make this less of a problem (I do not know if password still can be plucked out from a hex editor?). A possible solution is to synchronize Access's security with the server's security, because Jet will try to use Access's user & password for initial connection, and if a call to retrieve password was made along with a module at startup to 'fix connections' along with updating the connection string with the entered password, this may help somehow with keeping the password secret? Does anyone have more information on that?External LinkA useful FAQ (http://archives.postgresql.org/pgsql-interfaces/2000-07/msg00193.php) for linking Access with Postgresql which may be useful in giving you some ideas of how you can work with Access. If anyone wants to contribute to this, please do feel free to do so. If anyone finds anything that is dead-on wrong, please give me a good can of whoop-ass- I hate to lead blind into ditch, so to speak. :)
I have been selected to do a project by my department head. It involves programming an Access database to do various things. It has to have forms and all that good stuff. I'm trying to get the higher powers to get a web server for me to do this as a web application, but I may need to fall back on the Access idea. So I need a good tutorial on programming in an Access environment. I know it uses VB and I am pretty decent when it comes to programming with that language. What I need help with is how to do certain things like close a form using an onclick event or to print a report. There's a lot I need to be able to relearn so if anyone can point me in the right direction I would appreciate it much. I'm doing this for a Commander in the US Navy...(several ranks above me )...so I need to be able to pull this off!!
I am looking for a good Access Book. At work, I have "Access for Dummies" and "Access2000 Step by Step". Are either of these good, bad, etc. What would you recommend. I am a begginner, but know some things, so I don't want something for real beginners.
Hello for one of my System Development Methodiology classes I have to develop something that will keep track of several projects that a local hospital has going on. We are to develop this in access and it should be able to show how far till completion a project is and several people should be able to update the database.
I usually do not use access and do not know much about it but is an access database able to be updated simultaneously and remotely? If so were could I be directed to find out more about.
Hi! I have a client who wants a training tracking software built that will allow for simultaneous use by up to 850 users. Is Access a good idea, or should I use SQL, and if yes to SQL, can you point me to some great documentation supporting this? Thank you, thank you, thank you!
I 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:
I keep on getting a syntax error on the first [MU_ID]. Can anyone tell me what am I doing wrong please? You can e-mail me at Frank.Cappas@CIGNA.Com
Site: IF [MU_ID] BETWEEN ((SELECT [MU_Start] FROM [Sites] WHERE [Site] = “BRB”) AND (SELECT [MU_End] FROM [Sites] WHERE [Site] = “BRB”), “BRB”, {IF [MU_ID] BETWEEN ((SELECT [MU_Start] FROM [Sites] WHERE [Site] = “BRI”) AND (SELECT [MU_End] FROM [Sites] WHERE [Site] = “BRI”},"BRI")
I'm trying to get my head round this query and not having much luck.
I have table that contains. amongst others, two fields I need to use - Date and Amount. I already have a query that returns me the number of entries for a given month, along with the total value.
SELECT Count(*) AS [Number of Entries], Sum(NBReferral.[Amount]) AS [Total Amount], NBReferral.Date FROM NBReferral GROUP BY NBReferral.Date;
What I want is to also provide a running total (year to date). Using this:
SELECT Count(*) AS [YTD Number of Entries], Sum(NBReferral.[Amtount]) AS [YTD Total Amount] FROM NBReferral;
Combing the two queries works as long as there is only one month. Unfortunately, I'd like to see each month's YTD figure displayed as shown
Date Number of Entries Total Amount YTD Number of Entries YTD Total Amount Nov 2006 5 10000 5 10000 Dec 2006 3 5000 8 15000 Jan 2007 6 12000 14 27000 etc.
What I currently get, as you would expect, are the same YTD totals applied to each month:
Date Number of Entries Total Amount YTD Number of Entries YTD Total Amount Nov 2006 5 10000 14 27000 Dec 2006 3 5000 14 27000 Jan 2007 6 12000 14 27000 etc.
Probably a fairly straight-forward query for someone with a bit more experience. Any ideas?
I need to make one modification to it, but I am getting lost in the syntax. Right now this code is matching the text string "txtsearch" to anything in the "Engine Name" Field.
It works fine, but I need it to also look in the "type" field. I need results if the string is in "Engine Name" OR if it is in "Type"
I want to run a query that allows the user to enter the beginning date and the ending date to produce the results for all items within those dates. I've written this before using "Between", but I can't get the syntax correct.
Does anyone have an updated link to a free utility download that will auto create a backup of my database as well as do a compact and repair at a scheduled time each week. Maybe twice a week if possible! :p
In my current, new position, I have been asked to look into the possibility of finding out if there exists some good tests available to test how much skill someone has with Access.
Do you know of any existing tests or websites that do testing like that? If not, I may have to create one for us, but I didn't want to have to totally reinvent things if something good already exists.
Also, if something doesn't already exist, maybe we could get some people here to contribute to a thread with potential questions to ask.
Just wondering if anyone new of any books that contain examples of code, functions, syntax, etc. I would like to have a good reference that I could go to instead of searching the internet everytime I need to try something new.
I searched google for sample databases and most forms are rather primitive... Currently there are some features to make more user-friendly forms that I have yet to find in excess:
1. Stop updating record automatically : For some strange reason access auto-update the records the user change in a form. What I want is to only update records when the user press the "SAVE" button. This could allows better validation without forcing the user to remain trapped in an entry.
2. Better error messages Instead of annoying pop-up telling the user that he made an error, I want something like an ErrorProvider Component that tells what error the user made with a small symbol (!) which provides the explanation of the error when you over the mouse on it.
3. Using different formats for different columns in a datasheet form For some reason it is not possible to assign a special backcolor for some field that are locked to indicate they are locked if you use a datasheet form. This needlessly confuses users who hammer their keyboard trying to enter info in locked fields and have no feedback ( except maybe an incredibly annoying pop-up window ) to tell them not to enter data there.
4. Better excel-style features for datasheet forms : Good way to annoy users : prevent a way to cut and paste multiple rows/columns of information in datasheet. If you try to paste more than one column at the same time it just doesn't work.
Also why is it impossible to make the title of field to take more than 1 row ? I mean it sure looks dumb to have
I am very new to ASP and Access. I need to learn how to create a database full of images and text to feed dozens of webpages for a new site I am doing. I have googled many times and can't really find what I am looking for. I've come across ADO several times. Is this similar to what I am looking for? Thanks.
I would like to add a textbox to my main form so that users can enter in some extra data regarding the job they are working on. This text box should tie to the current record of the main database, tableJobLog.
Is it best to keep this textbox as a field in the main database, tableJobLog? Or should I create a separate table (perhaps tableJobNotes) with just this text field and link the two tables?
I would think that having it in the main table would make the table grow considerably in size after some time.
If creating a separate table is better, I would need some guidance on how to do this.
I've become aware that if I create a variable in the select statement like this in Access:
SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum
That I cannot reliably use said variable later in the same statement:
SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum FROM Table1 GROUP BY Table1.ID ORDER BY TotSum DESC;
It will ask me to "enter a parameter value" for TotSum. When I do, in this example, it still sorts in correctly, but in a larger more detailed query it gets a little confused. I instead have to re-use the equation like this:
SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum FROM Table1 GROUP BY Table1.ID ORDER BY Sum([A]+[B]+[C]) DESC;
Is there a way around this? It seems inefficient to recompute the sum 2 times where I think I only need to do it once.
