Simple Database with Primary Key resetting every month

[ProwlingEyes]

I am working at a clinic and I am given the task to create a simple and small database for patients coming for a specific test in a certain department. The clinic has its own database software application but for some security purposes the data of this department is not linked to that system. The management wants me to create a simple database for this department where:
1) Every patient will have a file number. This file number will start from 1 every new month.
2) The data table must also have the record of the entry date for the patient.
3) There is also another date field, that is actually the date on which the patient will receive the report of the test. This date is usually after two days. (not always, i.e. can be put automatically but must be editable)
4) Patient's National ID will also be recorded.
5) Bill / Receipt number and amount will have to be entered with each record.
6) Contact info for each patient will be recorded e.g. Mobile number.
7) User should be able to look for a specific or a group of records based on File Number, National ID Number or Both.
8) User should also be able to Filter the records by Entry date, Report Delivery date and Bill amount.
9) User should be able to search for specific records by File Number, Name, National ID, and Bill / Receipt Number.
10) The file number must start from 1 in the beginning of each month. So, one patient may have different file numbers in different dates.

I created one table containing these fields:
File Number, Name, Date of Analysis, Date of Report delivery, Time of Report delivery, National ID, Mobile Number, Bill Number, Bill Amount.
I couldn't put Auto Number on File Number because File Number will have to be reset in the beginning of each month and I don't know any way to do that (automatically).
The analysis date, which is usually the current date, is coming automatically but is editable. I derived it using the date() function in expression builder.
The receiving date is left empty. I don't know of any expression to put that date automatically, which is usually after two days of the date of analysis. The time field is also automatically put using default value property in table design view, but this field is editable.

Now after creating the databse i.e. one table, forms, queries and reports. I face the following problems:
1) I do not know how to tell Access to reset the file number to 1 in the beginning of each month. And even if I do that somehow, there is the problem of Primary Key i.e. we can't have duplicate file numbers and as i have mentioned before the file number must restart every month. If I make separate table for each month then the Primary key problem can be resolved but I donno how to make a new table for every month, can that be done automatically? Or should I not have a primary key in the data table? as I have only one table, that is also a plausible solution... ?
2) Another problem that I am facing right now is I donno how to print a report using a selected record in the form. I can open a specific report using a button on the form but that uses a query which asks for the information again, but I don't want that report to ask again for the information as the information for the record is already open and selected on the form.

I am still working on the solutions and will really appreciate your guys' help.
I will definitely post any solutions I find and will share it with all here..

Thanks in advance.

 

[spikepl]

Leave the PK as is. Add a custoom autonumber - search the forum for that.

that uses a query which asks for the information again

So don't use a query that asks for info. That info can be picked up by the query from the controls on the form on which your print report button is.

 

[ProwlingEyes]

That info can be picked up by the query from the controls on the form on which your print report button is.

How do I create a query that picks up certain (not all) information from the current record on active form? and then how do I open the report that uses the same query to print the said record?

 

[DavidAtWork]

to be able to automate the filenumber, I'd suggest you need an additional field in your table which will record the filenumber month and I suggest making this the 1st of each month as this could also be useful later for reporting and grouping.
This could be a combo box that will list of 1st of month dates in MMM-YY format, it can be set to default to the current month but would give you the option to select the month in the case of a different month required.
With a date set in this field, you can use the DMax function to get the current highest number and add one to it, if DMax returns NULL it will be because there are no records for that month and you can set it to 1.
For your printing problem, have your report run off a query based on the current record, set the file ID criteria to the form's current file ID

David

 

[ProwlingEyes]

to be able to automate the filenumber, I'd suggest you need an additional field in your table which will record the filenumber month and I suggest making this the 1st of each month as this could also be useful later for reporting and grouping.
This could be a combo box that will list of 1st of month dates in MMM-YY format, it can be set to default to the current month but would give you the option to select the month in the case of a different month required.
With a date set in this field, you can use the DMax function to get the current highest number and add one to it, if DMax returns NULL it will be because there are no records for that month and you can set it to 1.
For your printing problem, have your report run off a query based on the current record, set the file ID criteria to the form's current file ID

David

I have removed Primary Key from File Number field, so the user can enter any file number he wants and thus can start the number from 1 for new month.
I couldn't understand your last comment: "set the file ID criteria to the form's current file ID". i donno what that means, could you please explain that a bit?

 

[DavidAtWork]

you still a PK field which can be an autonumber and the File Number field becomes just a reference key which is generated as described in my previous post.
I couldn't understand your last comment: "set the file ID criteria to the form's current file ID". i donno what that means, could you please explain that a bit?
If you base your report on a query, go to the criteria line (of your query) on whatever is the primary key, and use the wizard to navigate to your form's PK field for the current record.

David