Multi-Type Field

unknown27

New member
Local time
Today, 03:22
Joined
Apr 23, 2009
Messages
8
I tried looking but to be honest i'm not sure what i'm looking for, other than advice that is!! ;)

I'm designing a database to log details of boxes sent for storage. Users need to be able to enter details of new boxes and search existing data to request them back from storage. My main data table is as follows:

tblData
-------
BoxID (PK)
Barcode
DescriptionID (FK)
BoxType
StartDate
EndDate
Retention
DateAdded
Colleague
TeamID (FK)

Boxes are added to the database with the above details. The problem i'm having is with the StartDate and EndDate fields. When I started building this I was told that the boxes contain documents between two dates, the StartDate being the date of the oldest document and EndDate being the date of the most recent. Now they've told me that sometimes it's not dates they use to refer to documents, it's file numbers.

So, for a handful of (quite a large list of) DescriptionIDs they want it to record file numbers instead of dates.

I'm thinking at the moment that just making the fields Text fields is the way to go since then they'll be able to put whatever they want in, but i'm concerned that that will make it difficult to validate entries and thus difficult to perform searches on the records.

Any ideas folks?

Thanks in advance for any replies!!! :)
 
I take it you do not want your users to be able to put just anything in the box description. What I would do is only give them predefined descrptions. Create a table with the descriptions, assign each description an ID number. Create a combo box with the record source based off of your descption tables, storing the ID as a foreign key. I am assuming that you will only allow for one description per box.
 
Thanks for the reply, but it's not the Description field i'm having the problem with. I am actually doin that the way you suggested! :)

It's with the StartDate and EndDate fields, on some records it'll be fine as they will be dates. However on some records they need to be just numbers, a starting file number and ending file number.

Sorry I don't think i'm doing too well with the explanation! :o

Various teams in my place of work will be using the database. Every Monday we send boxes to another location to be stored. The boxes contain varying documents. For most departments the documents are stored by date, and any one box will contain documents between two dates.

One team though (the ones causing the whole damned issue! ;) ) don't store documents according to date but by their file number. And only this team does it this way. So in their case, there are no dates involved.

I was thinking having a seperate database for them but as the boxes alll end up in the same place and the database is generating the box ID by autonumber that would just cause more problems.

Really not sure what to do!! :confused:
 
I would just add two additional fields. One for the for the Staring Number and one for the Ending Number.
 
Yeah I did think about doing that too, but that would mean that every record is going to have two empty fields. Technically it wouldn't hurt the operation of the database but would it not waste space?
 
space loss is insiginficant

the only issue might/would be that you (ie your dbs) would need to consider whether searches for date ranges etc should ignore boxes recorded with numeric ranges. (or vice versa) Maybe add an indicator to show the type of range in the box as well.
 
Yeah it would be one or the other when searching but that's ok I can work with that.

Thanks for you help guys, I was curious about the space issue using this method but if you guys say it's fine I believe you! ;)

Finally I can finish off this database, it was starting to feel like I was banging my head against a brick wall!! ;)

Thanks again! :)
 
You're welcome!

Good luck with finishing your project ...
 

Users who are viewing this thread

Back
Top Bottom