Skip to main content

Replies sorted oldest to newest

I tried several different model train inventory programs and found something I liked in each one, but none had all the features I wanted to I took those features I like and created my own database using Microsoft Access 2010 (yes, I know, kind of outdated, but it works!).

Here's the fields I have for each item:

Seq # (this allows me to sort according to my own sort sequence)
Cat No
MA (once I enter the data from the spreasdsheet I place an "X" in this field; that way I can compare the total of X's in this column with the number of records in the database and tell if I've missed any - or when I get several items I know which have been entered into the Access database)
Description
Color(s)
Series / Set
Manufacturer
Scale
Group (my own groupings)
Road Name Code
Road Name Description
Road Logo
Reporting Mark
Category
Type (a sub-catrgory of category - to break them down into smaller types (primarily categories and type from Yard Office)
Min Curve
Length
Built
Status
Configuration
Years Cataloged
MSRP
Purchase Date
Sold by
Purchase Price
S & H / Tax
Calc. Total
% MSRP Purchase Price
Item Condition
Best Value
% MSRP Best Value
CY Greenberg Value
% MSRP CY Greenburg Value
MITrains (software) Value Date (unfortunately this program is no longer available)
MITrains Value Price
% MSRP MITrains Value Price
Qty
Box Type
Box Condition
Images (up to 8)
Notes

With the Access database I can create just about any report. Sometimes those reports help to to identify erroroneous entries.

paulp575, how's the Access db coming along? I have resorted to Access on a number of occasions and am doing so now with my new foray into 0-gauge trains. I have just populated my first go of an "out-of-the-box" accdb--no VB code. I can see already that I have no clue on how to pull all the major car items together. Items such as major car groupings (engine, box, gondola, hopper, etc.) , car truck types, coupler types, coupler actuators, collectors, and on the list goes. How have you normalized your car inventory? I would like to work with you on this one. I'm not up to speed on Access's lifespan but I'm hoping it will be around for another decade at least.MSRR Car Inventory Db

Attachments

Images (1)
  • MSRR Car Inventory Db: MS Access Car Inventory Form, Tables, and Relationships
Last edited by TheLloyd
@TheLloyd posted:

paulp575, how's the Access db coming along? I have resorted to Access on a number of occasions and am doing so now with my new foray into 0-gauge trains. I have just populated my first go of an "out-of-the-box" accdb--no VB code. I can see already that I have no clue on how to pull all the major car items together. Items such as major car groupings (engine, box, gondola, hopper, etc.) , car truck types, coupler types, coupler actuators, collectors, and on the list goes. How have you normalized your car inventory? I would like to work with you on this one. I'm not up to speed on Access's lifespan but I'm hoping it will be around for another decade at least.MSRR Car Inventory Db

Database is doing fine.

I think what I ended up with is a flat database, i.e., no cross-reference(?) tables.

It's now about 677 MB. I'd love to figure out a way to shrink that size - although my desktop handles it quite well.

Never figured out how to not have to enter a code and the text that goes with the code but when I print a report it automatically populates the text field. I think that requires some type of table, but never could figure that out.

paulp757, depending on your comfort level, would you send me your table (thirty or so rows with as few blank entries as possible) and I'll take a look at your MS Access 2010 database and provide you with any ideas I may have. Are you writing VBA for your forms and reports?

When I first start a database, I run the Wizards on sample data so that I can refine how best to normalize the data. In this way I may delete Access objects and start fresh again. Once I'm satisfied that I have the data normalize, then I feel better about investing time in code and improvements.

@paulp575 posted:

Database is doing fine.

I think what I ended up with is a flat database, i.e., no cross-reference(?) tables.

Make copies of your database/objects prior to experimenting,

Use the Access table analyzer to provide some clues as to managing your data. This feature having Access create the new related tables will generate Lookup fields in the main table of similar data. The Lookup fields are a pain to work with and you can change/eliminate them down the road. But you will get a idea of ways you may improve your db structure. The Table Analyzers also allow you to make select fields and make your own tables in the same way. If you don't like what was created, just delete all the new tables and return to square one with your original table.

It's now about 677 MB. I'd love to figure out a way to shrink that size - although my desktop handles it quite well.

I have 250 cars in my accdb (v2016), 2 forms, 2 reports, and some linked images. It is under 5mb. Are you running Access compact and Repair?

Never figured out how to not have to enter a code and the text that goes with the code but when I print a report it automatically populates the text field. I think that requires some type of table, but never could figure that out.

Not exactly sure what you mean on the reports. Access can run reports with no VBA code. Again, use the Report Wizard, select the table(s)/query and fields. The wizard will run you through some more options, like grouping, sorting, sums, and generate a report. You may edit this report to better suit your needs or delete it. Or are you saying you have a form which you select fields from and then generate a report via VBA code?

I have only been scale model railroading it for a year now and it is way more complex than I expected it to be. I'm sticking with 0-gauge Lionel for now to keep sane. I would really like to see how you have been using your database...tested over the years it has served you, so that I could get a handle on...well, for now, car inventory, which includes maintenance, repairs, parts, and some minor financial information on the cars. The database should assist us and for you this has occurred. If you do move in the direction of upgrading it, make backups to fall back on in the event you decide the upgrade idea is not worth the effort.

Last edited by TheLloyd

Wow! That's a lot of responses.

I always have a backup of my database. After making updates, I make a copy of the database. And I also back it up during the night to an external drive (I use a backup program; runs nightly). Used to also back up to the cloud but the program I was using I ran into problems and their tech support was clueless so when my subscription expired, I let go of the cloud backup program.

I always repair and compact - even periodically when I'm making updates just in case something goes awry.

IIRC most - if not all my report I manually created. While the wizards are great, they sometimes limit the report. Way back in the late 90 to early 2000s, my employer paid for me to attend Access college courses at the local college. Paid off in the long run. I still have the books, but seldom reference them any more because I don't think I've created any new reports in a long time. If I need a new report, I just copy one of my current reports and modify it to meet my needs.

I'm not sure if I've every used the table analyzer. Maybe one of these days I'll give it a try. Does it create "lookup" tables for a database?

As a computer user starting in 1980 I'm fully aware of the risks of upgrading to more recent programs and OSes. I came from the school of "if it ain't broken, don't fix it". That's why I'm still using Office 2010. Works great for me.

I was going to send you my database via e-mail,. but you don't have your e-mail in your profile.

All good stuff. Yes, the table analyzer will do its best to make sense of a table's field data and it may/will generate new tables with that field/data. The end result will be a new "main" table with lookups to the newly generated related table. (It will automatically name tables, or you may name them. If the analyzer is doing you right, get the table names on target. Also, while running the analyzer, you may relocate fields from any of the tables and generate cascading relationships from the "secondary" tables.) The source table initially selected will remain unchanged, but the analyzer will rename it (*_OLD). You also have an option to create a query of the table analyzer's guess work before you finish the table analysis. Typically, on a new project I give the analyzer a go on its own, take a gander of what it came up with, then in rounds 2, 3, 4, take over the process of defining/normalizing the data. It's really just a baseline to point me in the right direction.

I took a look at the Stecotec promo and for the price it seemed like a good deal notwithstanding the preponderance of node based treeview UI. I'm guessing it will fall short in the "nitty-gritty" of car truck coupler types with varying actuator types and reports. Has any railroader reviewed the Stecotec app?

I'll add my email addy to my profile. Let me know if I botched it.

What is "IIRC"?

Thanks for the quick reply and look forward to checking out your table.

Last edited by TheLloyd

Update: I have been using this (see attachment) cascading hierarchy for a year now and find it useful tool to organize and track cars by type and sets. The attachment is a sample with drill down for cars along with examples of how other train related items maybe organized. Your data will be in the final cascade (items) and how you group items is totally up to you. It is a forgiving structure as you may rename, or group based on your needs. I have examples of car, publication, layout, and memorabilia items at the top with but only the cars are really grouped in this sample. Once you reach the item you may click on its row and a detail form will open for that item (however, for this sample database only car information populates as there are no other item specific tables for publications, layouts, etc.

This is MS Access database and if you like it, just copy-past tables with definitions only (make sure to control Auto Naming feature) to purge sample records. Oh, the detail form does have an Image control for your photos but no photos in this sample, so no image loads. The arbitrary cascades are at six levels deep but you could cut that down to your needs as well. Just keep in mind that your data is at the final level and it is related by the car ID (so your main source of data is not compromised). Also, you can update fields in the final level with an update query (provided in this sample) so that at the item level all you need to add is the car ID number.

Attachments

Files (1)
Sample Access database using car inventory table and arbitrary cascading (drill down) hierarchy.

Six months later, more robust and tested. Any prior (above) database object is obsolete. If you have an interest, I'll get a copy to you of what I have been using. In the meantime, I hope this Private YT video will give you a good idea of what it has become.

YouTube video of sample database: MS Access Scale Model RR Database Sample FC

OneDrive download of sample database (its actually two databases, collection inventory and car maintenance): For the sample files (Sample.zip @51.1 mb)I need a valid email address to send a OneDrive link to you. If you have a public contact email address listed in your member profile, that should work. I would prefer to use OGR's private messaging to request database files and confirm access to zip file download has been sent to you, so that this post is not cluttered with the likes should interest in the database continue. However, I'm uncertain that OGR's PM will work in this capacity as with the first request I was able to use it and believe it went okay, but for some reason on the next request, my roll came to a halt as I did not have permission to PM that particular member. So if we can figure a way to grease the OGR private messaging, and if I could get some feedback on how the flow of request-downloads and condition of sample databases is going, that would helpful.





Attachments

Images (3)
  • mceclip0
  • mceclip0
  • mceclip1
Last edited by TheLloyd

Ted. zip file of the databases was just loaded up to google drive and an email notice sent to your profile address (cando...). I think I will set up my OneDrive for future downloads but for this first round I went with the google drive due to the zip file size. Let me know if you have any questions.

@TheLloyd posted:

Six months later, more robust and tested. Any prior (above) database object is obsolete. If you have an interest, I'll get a copy to you of what I have been using. In the meantime, I hope this Private YT video will give you a good idea of what it has become.

MS Access Scale Model RR Database Sample FC



Can you please send me the link?

E-mail address is in my profile.

Thanks

Ted, I believe we have gone full cycle, and you have the database files either from the google drive or from the OneDrive database zip file link I sent to your profile email address. Please, update me if this is not the case. Did you get the PM I sent you?

paulp575, I believe we have gone full cycle, and you have the OneDrive database zip file link I sent to your profile email address. Please update me if this is not the case. My attempt to PM you failed.

@CSX Indy posted:

FYI: This is a node-based app that uses a navigation similar to a folder directory tree. You could use your operating system's folder structure in the same fashion to organize items by simply creating the folder hierarchy and storing photos and documents such as text files or spreadsheets. This approach would only cost you the time you put into it.

@RKirk posted:

Hello The Lloyd. I would be very interested in having a copy of your Access database program for toy train inventory. Is it still possible to get a copy? Thanks.

Yes, the total size of the sample database(s) is 55MB. This includes sample car images for both the car inventory and car maintenance databases. If you could contact me through my email listed on OGR, and provide me with your email address, I would list you through that email address on my OneDrive and provide you a link to download the database zip file.

@TheLloyd posted:

Da, me, don't know why I didn't check your profile first for email address. Sorry for any confusion. If you have an email address in your profile, no need to use my profile email address. I will use your OGR email address and send you the download link directly.

I would like a copy as well.  My e mail address is in my profile.

Thank you in advance'

Joe

The sample database with the cascading hierarchy is very nice. When I allowed macros in the trust center no images show and I get a debugging error, but everything works as in the YouTube video. I still am at a loss about how to enter any new items and images, and how to delete items from the database. If anyone can provide some guidance it would be appreciated. Thanks.

Firstly, you will have to "roll up your sleeves" on this DB. I have not automated some key items like adding photos. For the car inventory DB there are really three parts, aquisition, cars, and cascades. The Car_ID is a primary field related to other tables. Keep that in mind. To add cars you must create an Aquisition record and a car record. You do this in the Acquistion form. I typically just use the Acquistion Date Picker and then below in the Car section, select the car manufacturer. This will add that car and link it to that acquisition record. If there is no photo, you may be getting an error message. You can Ok the error message and move on. (I have not added the error checking code but it could use it.) So, you must add a photo of the newly added car to the "Image" folder. The images are listed in a table called..."tblImages", I think. you can see the format used in the tblImage table. Just follow that format: the image file path, the full image file name, and the new Car_ID. Keep in mind that your folder structure is going to be different than the one I have in the Sample DB. The programming solution to this would be to use the Access parent folder to locate the images in a sub folder but I was in too much of hurry to do the right thing. (I needed to start adding cars.) There may need be a refresh so make sure you have that out of the way and the new image should load for the new acquisition/car record. To delete a car go to the tblCar table and delete that record. You may also delete all the other records for that Car_ID.

To add a car to the Cascade use the CascadeAddEdit form, create the hierarchy down to the "Item" table at the very end of the hierarchy, enter the Car_ID in the Car_ID field. You may manually add the other data in that car's cascade record or you could use the Update query to automatically generate the rest of the car's data. At this point, a link will be generated in the Cascade subform to the Acquistion form. It is a clickable link and opens the Cascade Lookup form where it highlights the car in the Item subform listbox (this is also clickable and open's that car's detail form).

So, the images use folders and files and must exist as such on your computer. It may give you insight to check out the DB's relationships...insight on object's dependencies.

I'll see if I can get the image error check coded...I need to do that and that would take care of one issue. Also, there should be a better means to delete car records but I hate to delete car information. A better choice for me, was to retire a record and filter out (exclude) retired records from the Acquistion record set. To do this you just need to add a checkbox in the Acquistion table and form, then add a where clause to the forms SQL...where Retire Is Not Select.

Here's the bottom line for me: if it is something that other RR'ers will use, I'll start working on a user-friendly version. There are a number of issues it has just for me to use it, so I need to fix it up. I'll upload another train DB with no records So that there will be the Sample database and a ready to use database with no records. I'll send you a OneDrive link when it is uploaded...say in a week or so.

Last edited by TheLloyd

Some members have requested a blank copy of the 2 databases which comprise the MS Access application I use. Because there is no train information (images included), the zip file is much smaller, and fits as an attachment.

Notes To Users: I have somewhat tested it ("... Demo v1.3") empty/blank and seems to be functioning as designed. However, the real test is when variations of users attempt to use it. There may be various and sundry error messages popping up (mostly null reference issues). If so, don't panic and "Ok" through them. Let me know too. That being said, feel free to provide me with issues and comments as to your findings while keeping in mind that it is free and I'm not a DB developer but believe that we can (as I have found with this DB app) make something that helps us spend more time running the best we can.  Keeping it on track, I'd say.

I'll keep the OneDrive link to the version 1.0 sample DB (with images and around 50 car records) active...so that downloading will remain available.

Side Note: I'm considering starting a new post for Access DB RR management tools. I'll be seeking OGR and others advice on this tact.

Attachment deleted 6/7/2024 to keep only current database for OGR downloads. See following post for current downloads.

Last edited by TheLloyd

Chuck, OneDrive requires me to send the link via OneDrive email. My current procedure is to snag OGR member's email addresses and plug that addy into the OneDrive "Share" method which I just did for you. You should be receiving an email with the OneDrive link to a Sample.zip file for download. Good Luck.

Last edited by TheLloyd

Sorry, the only non-user friendly folder my e-mail system has is called SPAM, and I did receive one message just a few minutes ago that mentions OneDrive.  However, that message was not sent by you (at least, not according to the e-mail address you use here), it contains a link to a file called the_witts has recieved a file to your onedrive "2024 Contract Agreement .xlsx", and it is addressed to an e-mail address I only use for special purposes.

Unless you can confirm that the file link in that spam e-mail is from you, and can also confirm how you got ahold of my other e-mail address, I will not even attempt to open it.

I am wondering if this is a problem caused by the OneDrive mail system?

Chuck

Last edited by PRR1950
@PRR1950 posted:


the_witts has recieved a file to your onedrive "2024 Contract Agreement .xlsx"

I am wondering if this is a problem caused by the OneDrive mail system?

Chuck

The .xlsx did not come from me. You are listed as having edit access to my OneDrive folder. That credential will allow downloads. OneDrive is terrible to manage. About all I can do is upload and share. OneDrive only provides me with a list of email addresses to which I have (hopefully) shared a file/folder with. Your yahoo.com address is listed. I'd say hang tight and we will see what tomorrow may bring. Also, just so it's clear, the OneDrive database is an older version and it does contain records which allows for a better tutorial. The zip file attachment above is the latest version and it is blank, no records, along with some bug fixes and a few more bells and whistles.

I just sent your OGR email listing a copy and paste OneDrive link. Let me know if that worked.

Last edited by TheLloyd
@NYC 428 posted:

I would like a copy as well.  My e mail address is in my profile.

OneDrive file relay has been ditched. I'll attempt to keep current version for download in this post.

Known Issues:
Access Trust Center settings must allow macros to run in database folder.
Access Linked File Manager must relink both Inventory and maintenance linked tables.
For VBA references see attached reference image in this post.
2024 0607 

Current downloads fix frmPhotoGlam form issues. Also, if there is a photo portfolio, no need to list all photos as long as there is one tblPhotoGlam record per car with a file name and a path, click on the hyperlink to open photo viewer which should allow for viewing all photos in the folder.

Attachments

Images (1)
  • MsAccessVbaRefs 2024-06-06: VBA references
Files (2)
Two car inventory records both with maintenance records. One car has photo portfolio.
Last edited by TheLloyd
@TheLloyd posted:

Just sent it. Let us know how it goes. Seems to be a snag in the system and members are not receiving the OneDrive link. Also, just so it's clear, the OneDrive database is an older version, and it does contain records which allows for a better tutorial. This zip file is the latest version, and it is blank--no records, along with some bug fixes and a few more bells and whistles.

Demo (blank) DB for keeping track of scale model RR items
Known Issues:
Access Trust Center settings must allow macros to run in database folder.
Access Linked File Manager must relink both Inventory and maintenance linked tables.
For VBA references see attached reference image in this post.

Sorry, have not received anything yet.  I did check my spam folder and its empty.

@NYC 428 posted:

Sorry, have not received anything yet.  I did check my spam folder and its empty.

Okay, time for plan "B". I'll see if I can get a sample database setup with a few records and if the zip file is able (small enough) to attach on an OGR post, I'll do so and ditch the OneDrive relay. If it works, I'll post here: https://ogrforum.ogaugerr.com/...7#184720406078248857

Thanks for the update.

Last edited by TheLloyd

Add Reply

Post

OGR Publishing, Inc., 1310 Eastside Centre Ct, Suite 6, Mountain Home, AR 72653
800-980-OGRR (6477)
www.ogaugerr.com

×
×
×
×
Link copied to your clipboard.
×
×