Database Software for Lego Collection / Inventory (Microsoft Access?)

[scatttcat]

So I know there are other threads floating around about this, but I was curious to get a current pulse check.

My gf recently has been showing me the power of Microsoft Access and has built an internal database for her lab. The thing is incredibly powerful and I immediately wondered how well it would work with my lego inventory / collection.

Cons of Access for me are 1) having to purchase Office Business and 2) not having a PC so either a) running windows on my mac or b) finding  a cheap PC to start this on. My hesitancy is, that is a lot of financial and time investment though on a gamble with access.

I currently use Excel to track a lot. I have personal collection, selling inventory, bulk inventory etc. but more and more lines are piling up and it's slowing down. I was curious if anyone uses Access for their collection? Or if there is another piece of database software out there that people use and has a decent amount of customization?

Just curious what people use!

[bigboy61]

7 hours ago, scatttcat said:

So I know there are other threads floating around about this, but I was curious to get a current pulse check.

My gf recently has been showing me the power of Microsoft Access and has built an internal database for her lab. The thing is incredibly powerful and I immediately wondered how well it would work with my lego inventory / collection.

Cons of Access for me are 1) having to purchase Office Business and 2) not having a PC so either a) running windows on my mac or b) finding  a cheap PC to start this on. My hesitancy is, that is a lot of financial and time investment though on a gamble with access.

I currently use Excel to track a lot. I have personal collection, selling inventory, bulk inventory etc. but more and more lines are piling up and it's slowing down. I was curious if anyone uses Access for their collection? Or if there is another piece of database software out there that people use and has a decent amount of customization?

Just curious what people use!

Although I don't use MS Access for Lego tracking, I do use it for a front end for a SQL database I made. I prefer to use SQL/MySQL database and then use Microsoft Access as a front end to manipulate that database. Microsoft Access makes a terrific UI for a SQL/MySQL database. It's a terrific tool for auto-generated reports and data-entry forms. 

 

I "wrote" a database to keep track of some information for a kids program at my church. I have it automatically calculate the ammounts of "currency" that the child gets for doing various tasks and then updates the database accordingly. I added a form that you can fill out that makes it really easy to enter information in the database (select a child from a list, whether they completed the task, etc.). I also added a report that's gives the full attendance for a given week/month and then what each child completed. The report is generated automatically so no need to go through the database and look for names and if they were present or not for that week. 

It will take some time to get used to, but it can be a really awesome tool to use!

[bigboy61]

8 hours ago, scatttcat said:

Cons of Access for me are 1) having to purchase Office Business and 2) not having a PC so either a) running windows on my mac or b) finding  a cheap PC to start this on. My hesitancy is, that is a lot of financial and time investment though on a gamble with access.

Why not just dual-boot MacOS with Windows? Pretty simple to do and cost effective! Here's a link: https://www.laptopmag.com/articles/dual-boot-windows-macos . 

[Alpinemaps]

I use Google Sheets to hold my data. Same idea as using Excel, but there is no native database app to mimic Access on Google.

I’m also a Mac user, so I understand your frustration.

You could try App Sheet. It’s an app that will consume your Google Sheet data and display it. It’s not the most user friendly app. But once you start digging you get the hang of it.

I’ve used it for a couple of years and like it well enough to keep using it.

Another alternative could be getting a SharePoint Online account. If your a little tech savvy you could load your data to a SharePoint list and then design a form to allow input.

I’m a SharePoint developer tho, so I may be underplaying how easy it would be. I keep meaning to do it, but I haven’t yet.

Maybe it’s time to do it and charge $10/year to have your own database there.

[SpaceFan9]

1 hour ago, bigboy61 said:

1) having to purchase Office Business

 

Give LibreOffice a try.  I use it daily now for work, and have zero trouble exchanging Word or Excel files with people still using Office.  And it includes a database program.

 

[scatttcat]

Although I don't use MS Access for Lego tracking, I do use it for a front end for a SQL database I made. I prefer to use SQL/MySQL database and then use Microsoft Access as a front end to manipulate that database. Microsoft Access makes a terrific UI for a SQL/MySQL database. It's a terrific tool for auto-generated reports and data-entry forms. 
 
I "wrote" a database to keep track of some information for a kids program at my church. I have it automatically calculate the ammounts of "currency" that the child gets for doing various tasks and then updates the database accordingly. I added a form that you can fill out that makes it really easy to enter information in the database (select a child from a list, whether they completed the task, etc.). I also added a report that's gives the full attendance for a given week/month and then what each child completed. The report is generated automatically so no need to go through the database and look for names and if they were present or not for that week. 
It will take some time to get used to, but it can be a really awesome tool to use!

See this is what I mean! My gf ran me her through reports she’s built and it just phenomenal how it can shield certain data and really improve UI input.

Curious how come you haven’t ventured into your lego collection with it when you seem to have some serious experience with the program? Haha

These are all great though!


Sent from my iPhone using Brickpicker Forum mobile app

Why not just dual-boot MacOS with Windows? Pretty simple to do and cost effective! Here's a link: https://www.laptopmag.com/articles/dual-boot-windows-macos . 

Another option I considered. I just didn’t know how people who dual boot dealt with any performance issues (if any?) with Microsoft access. I’ll have to research more on that!


Sent from my iPhone using Brickpicker Forum mobile app

[LegoMan1212]

I use excel, don't see any issues with it as I know Excel very very well.  

To me, the data is the same.  In Excel for my set inventory, it is probably 1,000-2,500 rows long but each year I clear out anything that sold to start with my beginning inventory for the year again.  Then add anything newly acquired afterwards so I have past year and current years separated on the same tab, but then once again, will merge the data, sort it, and repeat.

I also put every bricklink sale on there too. I know BL has a downloadable spreadsheet to give you this but I have my own which also calculates all fees automatically and all I need to type in is actual shipping cost.  Since I deduct a portion of inventory with every piece that is sold, it works great for that too to track my inventory deduction from my parting out side of things too.  

Another tab for all direct expenses and another tab for mileage.

Then a final tab in the front that summarizes everything for me in terms of all revenue broken out nicely, all expenses, and all inventory purchased and sold.

To the OP, if you excel is "slowing" down, I would suggest an upgrade of your computer parts.  Excel shouldn't slow much down unless the file is absolutely huge.

[bigboy61]

2 hours ago, scatttcat said:

Curious how come you haven’t ventured into your lego collection with it when you seem to have some serious experience with the program? Haha

Because my Lego collection is extremely small compared to everyone else on here. I'm talking like 4 NISB sets (not including my 8 brickheadz). I'm a college student and I took a class on databases where we used Access as a front end as part of one of the assignments .

It's just a really great tool. There are so many ways you can use it. There is a way to integrate a barcode scanner into the database system too although I haven't tried it. That would be pretty awesome for keeping inventory. 

 

Excel is a great tool too, but as my professor said in the class I took, there are some things that you just shouldn't use Excel for that people use Excel for. Databases are much more efficient for certain tasks like keeping inventory.

[scatttcat]

5 hours ago, LegoMan1212 said:

I use excel, don't see any issues with it as I know Excel very very well.  

To me, the data is the same.  In Excel for my set inventory, it is probably 1,000-2,500 rows long but each year I clear out anything that sold to start with my beginning inventory for the year again.  Then add anything newly acquired afterwards so I have past year and current years separated on the same tab, but then once again, will merge the data, sort it, and repeat.

I also put every bricklink sale on there too. I know BL has a downloadable spreadsheet to give you this but I have my own which also calculates all fees automatically and all I need to type in is actual shipping cost.  Since I deduct a portion of inventory with every piece that is sold, it works great for that too to track my inventory deduction from my parting out side of things too.  

Another tab for all direct expenses and another tab for mileage.

Then a final tab in the front that summarizes everything for me in terms of all revenue broken out nicely, all expenses, and all inventory purchased and sold.

To the OP, if you excel is "slowing" down, I would suggest an upgrade of your computer parts.  Excel shouldn't slow much down unless the file is absolutely huge.

Yeah, the part that peaks my curiosity the most is how you handle parting out a set. For example I buy set #76131 and part out the helicopter, jeep and compound all separately (keep minis for myself). Well now I have 1 cost, but 3 "parts" that when sold need to add up and then compare to the original singular cost.

I did find a solution for this in my excel sheet, but it uses conditional formatting for visual indicators and THAT is the reason I am starting to suspect my excel sheet is getting slow. I know I need to cut that all out and keep to a minimum. I also could simply formulas or just use better practices with excel in general for "version 2". 

Thanks for sharing though, I am jealous you've worked a successful way for parting out! Sounds well thought out!

 

[bigboy61]

18 hours ago, scatttcat said:

Cons of Access for me are 1) having to purchase Office Business

That's is definitely a hang up. I don't recommend 365 because it costs money every month/year instead of a one-time fee. You could try buying office 2016. Its not the newest, but you could probably find it cheaper. 

Edited August 9, 2019 by bigboy61

[JereUsa1]

 

 

[grackleflint]

One alternative database program to consider is FileMaker Pro. It is cross-platform (MacOS & Windows & phone/tablet apps) and the company is actually a subsidiary of Apple.

It's been around for a long time and is a very capable database to create custom apps. It's appeal is to individuals/SMBs who want custom apps but don't have dedicated database developers. 

https://www.filemaker.com/

I've used it to create databases for my LEGO collection and to keep track of purchases and sales.

I've attached some screenshots to give you an idea of what it can do.

 

Edited August 15, 2019 by grackleflint