Multiple Criteria Dmax? or Query? or something Else?

[rabuayya]

Hi there,

I have taken a course in Ms.Access where I learnt how to create tables, queries and forms. I don't have any programming background so I am using the Ms.Access interface to do everything without entering any codes.

My project is the following:
I need to create a database to track documents by giving them reference number. The reference number should be composed of 3 components: 1-Type of Document (Letters,Invoices,Receipts...etc) 2-Department#(numbers from 1 through 10), 3-Serial#

So when the user needs to track a document, they will choose the Type of document and the department#. The serial# however should be automatically generated based on pervious documents.

I created 3 tables, one for Types of documents called [TypeOfDocsTable], one for department# called [DprtNoTable], and one for reference numbers called [RefTable] where scanned copies of the documents can be attached.

I created a Form to enable the user to choose the [TypeOfDocument] and the [Department#] from drop down combo boxes.

Now my questions is:
How do I create a textbox that will automatically provide the user with the next serial# based on the [TypeOfDocument] and [Department#] chosen. Also if no previous record is available, I would like this textbox to return the value of 1 instead of blank.

I am thinking I need to somehow incorporate a Dmax Function that will provide the max serial# based on the type of document and department#

I hope I have explained my problem well. I would appreciate some help from the experts out there.

Thanks
Rami

 

[rabuayya]

I would like to give an example:

Assume these shortcuts for the type of doucment
Receipts=Rt
Invoice = In
Letter = Lt

Assume my database contains only the following tracked documents:
Rt-08-01
Rt-08-02

So if a user is tracking a receipt (Rt) belonging to Department#08, the computer should automatically provide a serial# of 03 (since 01 and 02 are existing records).

If a user is tracking a receipt (Rt) belonging to department#07, the computer should automatically return a serial# of 01 (since no records exist with [TypeOfDocument]=Rt and [Department#]=07)

Hope this example clarifies my point!.
Thanks
Rami