Product search database question

[Gregory Peck]

Hello. I am trying to make a system in which I can search a data base of several thousand products to find shipping weights by quantity and I would like to know if Access would be good for this. I currently have an excel sheet with all the product info in it but I have to manually do a 'find' to find and do all the calculations manually. Trying to find a way to make this easier for my employees to use.

Example. A customer calls in and wants a shipping quote on 500 GFT122 (product number). I want to be able to search for the GFT122 and have a window open with the product info for that item number so if the customer has questions about the product all the info is right there on one window. Then I would like to be able to enter into a field that they want 500 units. The system would then use the item weight (which would be in the data base) multiply it by the 500 pieces number I would enter and it would tell me that 500 GFT122's weights 53 pounds. I could then input this info into a UPS shipping quote and give the customer their quote.

Is this something access would be good for and would it be hard to set up?



Thank you.
Greg

[CJ_London]

Is this something access would be good for

yes

and would it be hard to set up?

in principle, no, really depends on how much you are prepared to learn - suggest take a look at some of the access templates to get a feel for how they work - perhaps those on invoices, orders and/or sales. The Northwind is a template that cover many business processes

[Micron]

Not trying to sway anyone from using Access but spreadsheets "excel" at performing calculations. If there are too many products or lookups you reach a point where a db becomes the better choice. In your case, filtered columns can narrow down the row to one or even several products, showing the unit weight. Reserve a cell in the top row to calculate the product weight sum. The summary formula can also be written to sum only visible filtered rows.

Or recreate in Access what you have. Just be prepared to read up on it because you need to pretty much forget what you know about Excel when it comes to db design.

If you want opinions on the suitability of your spreadsheet, post a copy of it here with a clear narrative as to what you need from a tool and we should be able to advise if you need a hammer or a saw, so to speak.

[Gregory Peck]

Thank you for your input. I am new to access but not afraid to learn. I will post an excel sheet showing the data I need to use and a rough schetch of how I would like it to show up. The idea would be that customers call all the time asking for info on our products 90% of the time they want to know pricing and shipping cost. We had a website that had a quote feature on it so all you had to do is enter the product number and quantity and it would show the shipping cost. I realize that it will take a good bit of work to make any thing link up with UPS and give me shipping cost but I am hoping that I can make something with out too much hassle that will allow my employees to type in a item number and the access database will show the product info. Then they can enter the quantity a customer wants and it will tell them the total shipping weight. The employee can then enter the weight into USP quote website and it will give them a cost for shipping. I will also want the "system" to show other info from the spread sheet such as product size, Decoration method, set up charge etc. Basically an easy way to see the product info without looking at a spread sheet.

[ssanfu]

I agree with Micron. What you want to do could be done in Excel using a form and some VBA. A separate sheet would be used for the pricing.

Your spreadsheet has 123 columns. I would expect that to break up into 5 to 10 (related) tables unless a majority of columns were not needed.


It depends on how familiar you are with Excel vs how much studying you are willing to do about Access.

Are you planning on moving away from Excel??

[Micron]

That's some spreadsheet! A lot of columns in the domain, but a lot of them don't seem to apply to a product in some cases. If you add at least one row to the top and apply filtering on the data below that, you can enter a product quantity in a cell (say B1) and a formula in C1 that multiplies the subtotal of all weights for the applied filter. I think that would be: =SUBTOTAL(109,DS4)*B1

As Steve noted, a form might be the way to go, although my original thought was to work from the spreadsheet itself - until I saw it. Maybe, maybe not. If you are not fluent with Excel vba and forms, it might be best to forego learning that in favor of learning the same thing in Access. They are not exactly the same, so IMHO, once you start getting deep into Excel forms and vba, you're encroaching on what Access was meant for. Only they're not the same, so learning Excel methods will not help a whole lot with Access. You would have to decide whether or not you should invest the time to learn proper database design principles, or learn Excel forms, or just work off of the spreadsheet. I see those as your 3 basic choices.

[Gregory Peck]

Well I did it. Took most of the day but I figured it out and have a working data base type thing.....lol. Need to make it a little a bit nicer but it does what I need it to do. I even made it calculate the Net pricing. I now need to figure out how to make it so nothing can be edited so no one can accidentally screw up the pricing and whatnot. Thank you all for your pointers and info.

[ssanfu]

Excellent!

Would you care to share your dB? I would be interested in seeing it.


---------------------------------
Are you ready to mark this solved? https://www.accessforums.net/showthread.php?t=1828

[Gregory Peck]

Sure. I am sure it is as basic as it can be but here it is.Product Search1.zip