# Thread: Calculated Queries - advice (2000)

1. ## Calculated Queries - advice (2000)

Hi,
Can anyone direct me to a post or website that has a list of different expressions used in calculated queries. I'm trying to display records based on the values in 4 different fields and would just like to learn more about how to do it.

Field1=Value in

2. ## Re: Calculated Queries - advice (2000)

As far as I can see, Field1 does not come into the sort order. First add the columns you want to display to the query grid.

Then add the following columns to the query grid, in this order:

<table border=1><td>Field:</td><td>Field4</td><td>IsNull([Field2])</td><td>IsNull([Field3])</td><td>Sort:</td><td>Descending</td><td>Descending</td><td>Descending</td></table>
Clear the Show: check box for these columns. The sort order is based on the fact that True corresponds to -1 and False to 0, so True < False.

3. ## Re: Calculated Queries - advice (2000)

Thanks Hans,
Is there any good posts or websites that you know of that I'll be able to learn a bit more about calculated queries. I'm refering to this one as well as previous ones you have helped me with.

4. ## Re: Calculated Queries - advice (2000)

Not offhand, but try a Google search. I'm sure there is some wheat among the chaff.

5. ## Re: Calculated Queries - advice (2000)

Addition: I have found the Access 2000 Sample Queries Database from Microsoft very useful. It contains lots of examples of queries, with links to Knowledge Base articles describing the techniques used in them.

6. ## Re: Calculated Queries - advice (2000)

Thanks Hans,
I'm now looking at it.
Google seemed to be very vague as to my query, lots of people offering courses though!

7. ## Re: Calculated Queries - advice (2000)

Hi Hans,
Sorry to have to contact you again. I'm getting muddled in my own confusion. I have attached a database that I'm stuck on. I'm unclear as to where I'm going wrong with the QryMain. I am trying to get the Chambers to be displayed in FrmPayments. I thought that I had based the Frm on QryMain and that QryMain would cross reference with the chambers..........how wrong am I!
If you wouldn't mind, could you have a quick perusal over it and see where I've gone wrong? With regards to the queries these all relate to a previous version that I had messed up, but I'm okay with these.
One thing I would like to question (yes, another!) is, is there anyway to 'archive' data? The circumstances are that at the end of the month the a report will come into work detailing all payments that have been made for the previous month. We then go through the list and mark the Actual Paid onn the Database with the figure given to us on the list. The reports will then be produced from this. The problem is that the payments, although all relating to the previous month, have no date format. They may have been sent (DateSent) in January, but not paid till March. Basically after the reports are done on a monthly basis and the Actual payments have been entered (Actual) on the database, reports would be produced and all the actual payments would be archived. Can this be done electronically by moving all records with figures in Actual to another log/database? The other problem that I foresee is that each entry on TblMain can have numerous entries relating to it on TblPayments.Not all of these entries will have a figure in Actual.

8. ## Re: Calculated Queries - advice (2000)

Why do you have a field named Chambers in tblCounsel and also in tblPayments?
Why do you refer on frmPayments to fields that you have not included in qryMain?

9. ## Re: Calculated Queries - advice (2000)

Hi Hans,
Yes indeed and had made a mistake. It all seems to be working okay.
I do have a problem with a relationship between CFIN in TblMain & ID in TblPayments. I need a one to many relationship with enforced Referential Integrity. I'm trying to do this between the between two primary keys, both autonumbers, is this not possible?
With regards to my previous post, is the archiving possible or is this going to be a nightmare to do?
And lastly, the CFIN numbers relate to each file and therefore are never duplicated. There are occassions when a CFIN number needs to be altered to a previous number that relates to an old file where a payment has been missed but the actual entry has been archived. Can this be done with an autonumber? Can I also start the autonumber at a higher figure, ie 1024? I'm also guessing that the CFIN number should not be the Primary Key if I intend to alter it at a later date, albeit very rarely. The URN could be the Primary.

10. ## Re: Calculated Queries - advice (2000)

1. You cannot create a one-to-many relationship with an AutoNumber field on both sides. The link field on the "one" side can be an AutoNumber, but since an AutoNumber is unique by definition, the link field on the "many" side can not be an AutoNumber.

2. Unless the number of records becomes very large (and I mean in the hundreds of thousands or more), I would not physically move records to another database. I would add a Yes/No field Archived (or something like that) to the appropriate tables, and set this field to Yes for records that don't have to be displayed in the standard forms and reports any more. You can create queries that select the non-archived records (by having No in the Criteria line for the Archived field), and use these for standard forms and reports.

3. An AutoNumber field can never be changed once it has been created. As the name indicates, the value is assigned automatically. If you want an ID field that can be modified later, you should not use an AutoNumber, and you should set Cascading Updates for all relationships involving the ID number, so that related records in other tables will be updated automatically.

(AutoNumbers are meant to be a handy way of creating an ID that is guaranteed to be unique, but essentially meaningless. If you really want to, you can make an AutoNumber start at a predefined value, by appending a record with the desired value - 1; see ACC2000: How to Use an Append Query to Set Initial Value of an AutoNumber Field.)

If you want to use URN as primary key in tblMain, you should use a text field (possibly also named URN) in tblPayments as link field. It should *not* be the primary key in tblPayments, since you want to enable multiple payments for one record in tblMain. The relationship between tblMain and tblPayments should have referential integrity enforced, cascading updates. If you also set cascading deletes, records in tblPayments will be deleted automatically if their "parent" record in tblMain is deleted; if you don't set cascading deletes, you can't delete a record in tblMain if it has associated records in tblPayments.

11. ## Re: Calculated Queries - advice (2000)

Thanks Hans,
Just a couple of points that I would like to clarify if this is okay.
1. I think that i will use the URN as the Primary Key. With regards to the CFIN autonumber, by standard this increases by one unit for each CFIN number, I just used the autonumber as I thought this would be the easiest way to do an incremental figure. Presumably this will still be okay as with regards to point 2, if an entry has a Yes/No figure for archiving, this can be changed to No, therefore reactivating the case and allowing the user to generate a new entry on the same file with an identical CFIN number.
2.This would be a great idea. Presumably the user could do this on FrmMain after the figures have been entered. Presumably I would base the form on QryMain (although this would need altering) to ensure that the FrmMain only displays entries with Archive Yes/No displaying yes.
3.Covered in point 1.
The rest I'm okay with, I could just see myself heading for a black hole and getting in trouble.
Thanks for your help yet again.

12. ## Re: Calculated Queries - advice (2000)

Using an Autonumber field to generate an automatically increasing value is fine if you can live with the limitations:
- The number cannot be changed afterwards.
- If a record is deleted, there is a gap in the sequence that will never be filled any more.
- If the user starts to create a record, than cancels it, an AutoNumber value will have been used up, creating a gap in the sequence.

You can change the filter or the record source of frmMain on the fly to show all records, or only non-archived records, or only archived records. You could have check boxes for this, or a combo box, or command buttons - whatever is most convenient.

13. ## Re: Calculated Queries - advice (2000)

Hi Hans,
Hmmmmmmm, not too sure if an Autonumber would be wise as the number has to be incremental with no gaps. Can I effectively run a calculation to create an 'Autonumber' that will go up an increment based on the previous entry? i.e. The previous file with the CFIN number that has been saved in the database.

14. ## Re: Calculated Queries - advice (2000)

You could set the value in the Before Update event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.CFIN) Then
Me.CFIN = Nz(DMax("CFIN", "tblMain") ,0) + 1
End If
End Sub

Make sure that the Default property of the CFIN field is blank!

15. ## Re: Calculated Queries - advice (2000)

Thanks Hans,
I seem to still be having problems with the relationships, can't work out why.
I have:
TblMain: URN (Primary)(Text) One to Many/Referential Integrity/Cascade Delete Records relationship to TblPayments: URN
TblPayments: URN(Text) (This has a lookup field to take the URN from TblMain) One to Many/Referential Integrity/Cascade Delete Records to TblCounsel: AdvocateID