Auto fill a due date column in a query

[Dexter]

Can anybody help me ?
I'm pretty new to access but have a query (for correspondence) in which, among others, there are "date received", "type" of correspondence and "reply date" columns.
What I now need is a column headed "Due Date" and wondered if there is an expression I could use whereby this column could be automatically populated, based on the entries in the "date received" column, but also to take into account the fact that the "type" column (i.e. kind of correspondence) would necessitate a different "due date".
Ahy help would be greatly appreciated

[jzwp11]

...but also to take into account the fact that the "type" column

You will need a way to correlate the "type" with a number of days that should be added to the received date to calculate the due date. What I would typically do is to have a table that defines each type and the associated frequency (in days or whatever units that are needed)

tblCorrespondenceTypes
-pkCorrTypesID primary key, autonumber
-txtCorrespondenceType
-longFreq (the frequency in days, long number field)

Then in your main table you would reference the correspondence type via a foreign key. Once the main table and the tblcorrespondenceTypes are related you can pull the information as necessary in a query to do the calculation

SELECT dateadd("d",tblCorrespondenceTypes.longfreq, maintable.datereceived) as duedate
FROM maintable INNER JOIN tblCorrespondenceTypes ON maintable.fkCorrTypesID=tblCorrespondenceTypes.pkC orrTypesID

[Dexter]

Hi
Thanks for that, I have created the table with the fields "CorrTypesID", "Type" and "Num of Days" but don't know how to reference/relate the two tables.
Also would I then enter
"SELECT dateadd("d",tblCorrespondenceTypes.longfreq, maintable.datereceived) as duedate
FROM maintable INNER JOIN tblCorrespondenceTypes ON maintable.fkCorrTypesID=tblCorrespondenceTypes.pkC orrTypesID" as an expression in a new field in the newly created query ?

[jzwp11]

A couple of suggestions first. Regarding the "Num of Days" field; it is generally recommended not to have spaces or special characters in your table or field names, so I would recommend changing "Num of Days" to NumofDays. Also, the word "Type" is a reserved word in Access, so I would recommend changing the field name to something else, perhaps CorrTypeName

In your main table where you have the correspondence type, you will need to add a new long number field called fkCorrTypesID (the fk stands for foreign key) or something similar. Now you will have to update the fkCorrTypesID field in your main table with the appropriate CorrTypesID value from your new table that corresponds with the appropriate correspondence type. You can do this manually or via an update query. If you want to do an update query, the text of the correspondence field in your main table must exactly match the text in the "Type" field of your new table. If they do, then you will want to first make a backup copy of your database and then create a new query and bring in both your main table and your new table. Create a join between the two correspondence fields (the two text fields). Then change the query type from SELECT to Update. Add the fkCorrTypesID field to the query grid. In the Update to row put in the newtablename.CorrTypeID. I would recommend verifying the query by changing from the design grid view to SQL view.
The SQL text of the query will look something like this (you will have to substitute your own table and field names):

UPDATE tblMain INNER JOIN tblCorrespondenceTypes ON tblMain.CorrespondenceType = tblCorrespondenceTypes.CorrTypeName SET tblMain.fkCorrTypeID = [tblCorrespondenceTypes].[CorrTypeID];


If you should see double quotes around the [tblCorrespondenceTypes].[CorrTypeID], please remove them.

Run the query. Access should indicate that it is going to update some records.

Go into the table and verify that the new field has been updated appropriately. Now you can remove the corresondence type field since you now have the ID field properly populated. Save the table.

Go to the relationship window and establish a relationship between the two tables.

Now when you create a query that has both tables you will have be able to add fields from either table.

If you need additional help; please post back.

[Dexter]

Now I've got it and thanks a lot, just one more question, if you don't mind.
Would I be able to highlight the "NumOfDays" field if the correspondence hasn't been completed in time (I.E. If I have a date received and the number of days for a particular correspondence type is exceeded).

[jzwp11]

Yes, you would be able to highlight a control in a form that is bound to the field. You would not be able to do any highlighting at the table level.

I'm guessing that you would have to run a little code in the on current event of the form that evaluates date received + the frequency and compares it to today's date (I assume). You will need the datediff() function most likely.

[Dexter]

O.K.
That will do for me and thanks again !!!!!

[jzwp11]

You're welcome. Good luck with your project.