Excel or Access?

[Spotniq]

Good evening,

I'm wanting to get some input from others in seeing if I should use Excel (my personal favorite) or Access for the following scenario.

I recently inherited an excel workbook with a total of 6 sheets that houses hundreds of item numbers for different segments of the business. Sheet 1, 2, and 3 are assigned accordingly business segment A, B, and C. These first 3 sheets all contain active item numbers with various descriptions such as start date, end date, requested by, item description, etc. Sheets 4, 5 and 6 are again assigned accordingly business Segment A_Exp, B_Exp, and C_Exp. These last three sheets contain the same descriptive cells as the first 3 sheets for each item number... difference being is that they contain only expired item numbers. The item numbers that we use are all recycled. . For example if an active item number from sheet 1 (A) reaches it's last day of being active it is moved to sheet 4 (A_Exp). If a request comes in from business segment A for an item number it can be used and placed back to sheet 1 (A). So needless to say the workbook is a mess and the person before me did a lot of cut and paste and things got out of order. My question being.. can I keep this in excel and use a type of userform to transfer expired numbers (the whole row which includes the descriptive cells) from the expired sheets to the active sheet for the business segment that requests it? It would be ideal to have the userform have a search box that could search for item numbers in the expired sheets and display their current attributes (again start date, end date, requested by, etc), be able to change the attributes (not the item number as that will always stay the same), and once done have it move over to one of the active sheets? I would love to do this in Excel and stay away from Access as I'm more familiar with Excel. ANY input will be greatly appreciated. Thanks!

 

[RickXL]

My vote would be to keep it in Excel.

The way your system is currently working is not a good match for a database. If you use a database you don't move data about. You enter it into the correct place and leave it there. You could argue that your process is a bit like archiving but that tends to be a one-off type activity not a regular occurrence.

I don't understand why you reuse the item numbers. Numbers are cheap and it always causes confusion if you reuse them.

I don't see why you can't move the rows about using a form. The thing to watch for is that the row is actually moved and not either duplicated or lost! That, of course, is why databases don't tend to move records. You just flag them as being current or archived (say). You could do that in Excel as well and just filter out the ones you don't want to see. You would do the same with business segment. That would just be an attribute of a database row. All the data would be in one table and the individual rows would be flagged as current or archived and have a business segment attached.

If you do use a database it makes you realise that the solution has three basic parts. Data entry, data storage and data presentation. With Excel you tend to blur those distinctions. All processes happen on the storage sheet and that leads to compromises.

I hope this helps.

 

[Spotniq]

Thanks a lot Rick for taking the time to read and respond! Your response makes sense I will try it. Thanks again!!

 

[biocentrism]

Yeah I agree with Rick. Access is good if you have lots of data and you don't really have that much. And unless you know Access well it will only cause you to pull your hair out. Moreover, most people out there do not know Access but know Excel so Excel makes your operation more scalable.

What you are looking to do is not complicated at all by Excel/VBA standards. There are some SUPER amazing VBA programmers out there that can do utterly amazing things with Excel if you dont want to figure it out yourself. If you need some help finding them send me a PM.

Good luck!