Search results

To track the history of Insurance Costs, I suggest you create another table say, tblCostHistory, which contains fields for: A Primary Key / ID (as Autonumber) Insurance$ DateOfInsurance SS# (Long Integer, linked to SS# in present table with a 1-M relationship) & remove...

I don't think this way of running the query works. I would use the following code, which does work: Dim qdf As QueryDef Dim mysql as String mysql = ". . . . . ." 'put your string here Set qdf = CurrentDb.CreateQueryDef("", mysql) qdf.Execute

One of the benefits of using a relational database is that you are able to enter stuff like Department Name only once in a lookup table. All occurrances of it are therefore spelled the same, and if the name needs editing it is done only once! In your problem, I suggest you create an update...

Are there some common factors &/or expressions in your queries? If so, they could be the means of simplifying things (either by still using just queries, or by using variables & SQL statements in code).

IIF statements take a lot of CPU time 'cos each part of the statement is evaluated, even if it isn't subsequently used. Nested IIFs are even worse. If (excuse the pun) you can replace them with some other construct, preferably external to SQL, it may help.

Hi Steve G. Don't know of any, but you might fond some references if you do a search on the internet. I would suggest you have a go at doing it yourself. Creating SQL strings for various types of queries isn't so hard, especially since you can use the query design view to create a prototype...

Hi ENVY, Glad to help. It sounds like you've got a spelling mistake in a table name, field name, query name or some such object. I suspect a query is looking for the value of Student ID when it sould be StudentID - or some such other finger trouble. The built-in help for MS Access is...

Hi ENVY, I've attached a couple of Dbs with suggested tables & relationships: One is using a 1-1 type relationship, except there is little point in separating Payments from StudentTrips, so there is no separate payment table. The other is using a M-M ralationship, since one StudentTrip could...

Thanks Jon K. I too had a sudden flash of inspiration along the same lines (as you do when sitting on the loo :o - best place for cogitating). One way, as you say Jon K, is by linking the table from the second Db to the current Db: by creating a Select Query referencing all the fields of the...

Have you tried re-starting Access? What about re-booting your PC? Compacting and/or repairing the database? De-compiling and re-compiling the database? Copying the tables, queries, forms etc. etc. etc. to another new database? Or just simplifying or splitting the expression into more managable...

OK RV, you just try it in an Update Query, like I originally suggested! Go on, try it! Access treats both the main table and the lookup table as coming from the same database, wherever its located!!

Thanks dhx10000. A good site for answers, I hope. Actually, it looks familiar - some years ago I think came across it, but then lost the URL during a system failure (lesson: back up your favourites, too). So thanks again for helping!

I agree! That's the way I would do it if it were my problem (i.e. another M-M relationship). The resulting diagram my look confusing, but the trick is to concentrate on each part when designing your forms etc.. It helps a lot if you can print the diagram out so you can study the relationships...

Any ideas anyone? :confused:

Basically Yes. Just create a form with the text box (or whatever0 you want & the formatting you want. Then, in your query you reference the control on the form with the expression in the following format: Forms!MyForm.MyControl . The form must be open at the time the query is run & one way to...

Hi Profector. Firstly, one question - is the Total dollars ("Insurance $") a fixed amount or might it change with time? If it changes, might you want to keep a history of it? (OK, 2 questions). GrahamT

I am OK creating a SQL query to update a table in a second database from a look-up table in the second database, and I can create a query to update a table in the current database from a look-up table in the current database - simple. However, I am trying to create an Update Query to update a...