My database project has been fully implemented for 3+ years.
The first thought that came to mind was: Why can't you right click on your (date field?) then use a date filter? you can always copy the data sheet view to find multiple records. Be careful ! copy the sheet and use excel for further development or reporting. I once deleted from the data sheet view only to find that deletes within the data sheet view permanently change the table records.
I am using boblarson's sample database posted back up on page 1 of this thread. (It seems redundant to attach it here so I will just refer back to that post).
If I understand what is being said at the beginning of this thread, the idea is to put all assets into a single table (rather than creating a different table for each type of asset). There are basically five tables in the boblarson example:
And the most important table of all that stores the actual preference data on each asset that we really care about:
tblPreferenceDetail
In the tblPreferenceDetail table, there is basically one column for data and that column is of datatype "short text". However, depending on the preference I want to store, I may want to store other types of data - text, number, dates, etc. I don't see that this is possible as you can only have one datatype per column. Adding additional columns would defeat the purpose of using "Preferences" as structured in boblarson's database.
Do we create multiple preference detail tables for each datatype we want to use?
I am using boblarson's sample database posted back up on page 1 of this thread. (It seems redundant to attach it here so I will just refer back to that post).
On an aside. When it's time to create a new asset, would it be best practice to have a "Create new asset" button (VBA script) that pre-populates the tblPreferenceDetail with all possible preferences, and then fills in records in the tblPreferenceDetail with a dummy record for each possible preference?
On an aside. When it's time to create a new asset, would it be best practice to have a "Create new asset" button (VBA script) that pre-populates the tblPreferenceDetail with all possible preferences, and then fills in records in the tblPreferenceDetail with a dummy record for each possible preference?
I don't think you could do that directly, but maybe I'm reading too much into your post.
If you are about to add a new Asset (let's call it Equipment to keep with the database sample), you would have to know the EquipmentType; then you could get the common Preferences based on the EquipmentType. However, for the Values to be assigned to each preference, I think that's where the automatic filling in would fail. But suppose you had some defaults, and filled them --wouldn't you want some verification/quality check/confirmation that the default actually applied, or let you modify one/many as necessary, then approve all these preferenceDetails for this Asset.
I'm thinking/suggesting you would need some vetting process to approve all of the assigned preferences/details.
Is it normal to store all the data as datatype "short text"? That seems to be the result in boblarson's example. Unless we create multiple tblPreferenceDetails, one for each datatype?
However, when you have a physical asset, say a Dell Monitor, AssetNo: 125
tblAsset
AssetNo:125
EquipmentType: Monitor
PurchaseDate:23/July/2014
CurrentCustodian: Bob Jones
OtherInfoSpecificToThisAsset:
And a related table(joined on AssetNo) recording EquipmentAttributes: for AssetNo 125 based on the EquipmentType
Again, attributes you may want to record for a printer generally
EquipmentTypeDesc EAttributeName
Printer Manufacturer
Printer Print Method
Printer FAX Capable
Printer Network Ready
Printer AutoFeed
Printer Colour Output
Printer Duplex
And when you have a physical printer say Asset:789
you would have have specific PurchaseDate, Current Custodian etc and a link to a table with attributes for this AssetNo and values for each of these attributes.
Again, attributes you may want to record for a printer generally
EquipmentTypeDesc EAttributeName
Printer Manufacturer
Printer Print Method
Printer FAX Capable
Printer Network Ready
Printer AutoFeed
Printer Colour Output
Printer Duplex
And when you have a physical printer say Asset:789
you would have have specific PurchaseDate, Current Custodian etc and a link to a table with attributes for this AssetNo and values for each of these attributes.
Ok good example (Thank you). So the Manufacturer field with a datatype of "short text" would work great, and we could put manufacturer's in a separate table tblManufacturers if we wished, and use a combobox to restrict data entry to predefined manufacturers.
Let's say we tracked NumberPrintHeads or something like that for printers. It would be best for this to be of type integer/number (instead of short text), to prevent users from "Fat thumbing" an entry like "3e" (let's say they wanted to enter 3 but their finger slips and they enter 3e). But as far as I can tell, we can only include datatype "short text" for all of our preferences because there's only only colume/datafield in the tblPreferenceDetail table (to store ALL the preference details).
If we wanted to store various dates, let's say a purchase date, a warranty date, a warranty expire date, but didn't want to have the warranty date and warranty expiration date fields available for ALL assets, we would add attributes "Warranty" and "WarrantyExpire". BUT, again, these would have to be datatype "short text" and we wouldn't be able to use Access's built-in date picker and I'm assuming we'd have to write quite a lot of VBA to do data entry error checking to make sure the data entry person enters a valid date (and in a way that's consistently formatted).
I hope this makes sense. Again as far as I can tell the actual attribute data we're trying to store, according to the boblarson database example, has to be datatype short text, but this doesn't work well if we are trying to store an integer, or a floating point, or a date in the tblPreferencesDetail table since there's only one field for data and in his example it's datatype "Short text".
I looked at the sample by Bob, but soon felt I had to prepopulate some things if users were going to use it properly.
For example: I could see having EquipmentTypes --hopefully all encompassing so users wouldn't add their own spelling or terms for something already defined.
Also, for each of the EquipmentTypes, I tried to map out which attributes (Bob's preferences) would be appropriate for each EquipmentType.
Then I continually populated a list of Attribute values(Bobs' Prefdetails), as I worked on the list of attributes.
Note: Not all attributes apply to all equipmentTypes, and not all attribute Values apply to all attributes. Sounds confusing, but look at it as a long list of values (text data type) where only some values apply to some attributes and only some attributes apply to some EquipmentTypes.
ScreenSize would apply to Desktop, Laptop, Tablet, Monitor but not to Printer or External Storage.
I can see using short text to hold 24 for a 24 inch screen size. I don't see the need for integer, since I don't foresee the need to do arithmetic with an attribute value.
As for Warranty, I could see that as something to record with/or associate with the physical Asset. Perhaps Warranty Yes/No as attribute and value, but actual date, supplier etc with the Asset.
Update: I added your Print Heads example to my test
This is what I would see for possible attribute Values for the Print Heads(number) attribute for a Printer (equipmentType)
Did or does anyone have sort of a basic d.b. for this d.b. cuz im creating a similar thing with IT and im trying to figure out a base line for the d.b.
Did or does anyone have sort of a basic d.b. for this d.b. cuz im creating a similar thing with IT and im trying to figure out a base line for the d.b.
Sorry, but your question is too vague. Furthermore, the last prior post in this thread was on Sep 13, 2015. Did you read post #29 by @jdraw? Anyway, it appears that you need to do some basic research. Do a search on this website and on the internet based on the word "normalization".
ell 
