File number for each day (1 Viewer)

[docxyz]

Hi everybody and i wish a happy new year for u all.

l have a database for recording patients visits in a hospital,in addition for "serial" field I want to make "fileNumber" field which represents a continous serial number changes everyday but of course all visits in the same day have the same filenumber as in follows..

Ser-----Date----------FileNum
===========================
1-------17.12.2014------1
2-------17.12.2014------1
3-------17.12.2014------1

1-------18.12.2014------2
2-------18.12.2014------2
3-------18.12.2014------2

I cannot depend on date (I think) in the desired code because I want to skip weekend and holidays so FileNum continues after weekend (Friday) from the last number as follows..

DayOfWork----FileNum
===================
Sat---------------1
Sun--------------2
Mon--------------3
Tue--------------4
Wed--------------5
Thr---------------6
Fri---------------off
Sat---------------7
Sun---------------8

I'm sorry for the long thread,and I appreciate your help.

 

[burrina]

Not sure how your form(s) are setup or if your using a Combobox. A screenshot(s) would help.

 

[essaytee]

What are you wanting to know or what are you having trouble with?

Having said that I'll throw a question your way. How is the FileNumber field any different than the Date field (other than one's a date and the other is a number)?

Steve.

 

[docxyz]

What are you wanting to know or what are you having trouble with?

Having said that I'll throw a question your way. How is the FileNumber field any different than the Date field (other than one's a date and the other is a number)?

Steve.

Hi steve, thank you for replying me, you did catch the idea very well, yes the fileNumber is the date in someway, actualy the "file" is a groupe of records of the same date, so the fileNum field still the same until the date changes, so the file number corresponds to date, the problem is that I want to skip the weekend date "Friday" so the file number continues after weekend, I hope it is clear this time.

 

[docxyz]

Not sure how your form(s) are setup or if your using a Combobox. A screenshot(s) would help.

Hi burrina, my database including forms are in Arabic, so I think screenshots would not be useful , thank you for replying anyway, happy new year

 

[namliam]

The best way to do this is simply create a table that holds your filename and a sequence (either auto number or one of your own creation). You add a new filename to the table, get a new ID and add that to the records that are added from inside the file.

 

[Galaxiom]

Since the file number can be determined by the date, storing both in the table breaches normalization.

You should have a table storing the FileNumber, associated date and any other relevant detail regarding the FileNumber such as its name. There should be a one to many relationship between the FileNumber table and the table you already have.

Calculate a new FileNumber whenever a record is added and the Max FileNumber is not associated with today's date. This is best done in a custom function.

 

[docxyz]

The best way to do this is simply create a table that holds your filename and a sequence (either auto number or one of your own creation). You add a new filename to the table, get a new ID and add that to the records that are added from inside the file.

Hi namliam, I didnt understand your idea very well, but I'm afraid you mean to add (or create) the file number manually everytime, I suppose that you mean fileNumber by saying fileName.

 

[namliam]

No, I am assuming there is an existing import process for processing your file.
In this process you code adding the filename (and date) to a tblFile, this also holds a sequence/autonumber that you "simply" update/add into your existing process.

Very much simular to Galaxiom's idea, how may have happily ignored my suggestion

 

[Galaxiom]

There are two separate issues. One is the normalization of the data structure.

The other is the generation of the filenumber.

Namliam has suggested you keep a table of file numbers that apply to corresponding dates. You would populate this table only once.

Alternatively the FIleNumber can be calculated based on custom functions that determine Workdays. There are many variations on this theme available on the Net.

 

[docxyz]

Hi again, I've got an idea , now we have a separate table contain "date" field and "fileNumber" field, depending on your suggestions, I want the fileNumber field to be calculated as follows ( if date current value not equals date previous value then fileNumber equals last fileNumber value +1), so the fileNumber value will increase by 1 whatever the date of today asa long as it is a new date, so after returning from weekend and beginning a new date,fileNumber will increase by 1, is this an applicable idea ? if yes , someone please provide me with the vba code for that, thank you.

 

[essaytee]

Hi again, I've got an idea , now we have a separate table contain "date" field and "fileNumber" field, depending on your suggestions, I want the fileNumber field to be calculated as follows ( if date current value not equals date previous value then fileNumber equals last fileNumber value +1), so the fileNumber value will increase by 1 whatever the date of today asa long as it is a new date, so after returning from weekend and beginning a new date,fileNumber will increase by 1, is this an applicable idea ? if yes , someone please provide me with the vba code for that, thank you.

This new table you have created should also have a Primary Key, simply an auto incremented number, along with your fields date & number (and anything else directly related). Your date field should be indexed and set for 'no duplicates'. So impossible to enter duplicate dates. Your file number field could also be indexed and set for no duplicates. Any other tables which previously held the date and/or the file number fields now only need to contain a foreign key to the Primary Key of your new table.

A problem I see is what happens if a date is entered out of sequence, your file number data will be 'out of sequence'. I'm assuming this is not what you want. This is not insurmountable though does require additional coding to trap for this type of scenario.

Steve.

 

[Galaxiom]

This new table you have created should also have a Primary Key, simply an auto incremented number, along with your fields date & number (and anything else directly related).

There is nothing to gain by adding another field as a surrogate PK. The FileNumber is a perfectly suited natural key.

Your date field should be indexed and set for 'no duplicates'. So impossible to enter duplicate dates.

Definitely.

Your file number field could also be indexed and set for no duplicates.

This would be automatic by making thr FileNumber the PK.

 

[essaytee]

There is nothing to gain by adding another field as a surrogate PK. The FileNumber is a perfectly suited natural key.

This would be automatic by making thr FileNumber the PK.

Yes, I agree. I should have added that it's my preference to utilise a random/auto number as Primary Key, something that the user never sees or needs to worry about.

Yes, using the user generated and user visible File Number as primary key is valid but does require a little more thought and consideration in implementation and that is what I was alluding to.

Steve.

 

[docxyz]

A problem I see is what happens if a date is entered out of sequence, your file number data will be 'out of sequence'. I'm assuming this is not what you want. This is not insurmountable though does require additional coding to trap for this type of scenario.

Steve.

Hi sreve, the "date" feild should be calculated by "date()" so it always shows the date of today, so never out of sequence, at least this is the rule for "visitDate" in my "tblMain" that holds main visit data which in turn I want to add the "fileNum" field to it.

 

[docxyz]

Calculate a new FileNumber whenever a record is added and the Max FileNumber is not associated with today's date. This is best done in a custom function.

Hi Galaxiom, now I have a form for recording data of new visit, this form including fileNum field and date field, date field always shows date(), I wander if we could populate the fileNum field using onLoad event for the form using a function like this (if date not equals previous date "in previous record" then fileNum current equals fileNum previous +1), as I create a new system the fileNum should also continues from the last fileNum at the last working day using the old system "starting from fileNumber 300"
I appreciate if anyone could provide me with vba code for the previous idea.

Dear Galaxiom, I appreciate your replies and ideas very much.

 

[docxyz]

Dear Mailman and Steve, thank you both for your replies, and appreciate further help, so I can finish this new system at the appropriate time.

 

[Galaxiom]

Sorry. Christmas parties have been getting in the way.

Here is a rough demo.

See the DefaultValue property for the FileNumber control on the form. It uses the function to get a the appropriate file number.

 

[docxyz]

Sorry. Christmas parties have been getting in the way.

Here is a rough demo.

See the DefaultValue property for the FileNumber control on the form. It uses the function to get a the appropriate file number.

You are my super hero, I'll try this after few hours from now, I wish you a happy new year, you deserve it ;-)

 

[Galaxiom]

There are a number of rough things about the demo. For one I have not put index the date field the FileNumber table.

Another is how is how it doesn't deal with more than one user entering the first record of the day at precisely the same time. It probably isn't a big issue but if I get a chance today I will add this for the sake of showing how it is done.

The whole technique when done properly is a bullet proof alternative to autonumber or DMax +1 which are frequently used to allocate numbers.