Tech Support Guy banner
Status
Not open for further replies.

Access Database Idea

9K views 60 replies 7 participants last post by  cristobal03 
#1 ·
A Grocery Database that allows you to compare prices for various grocers in youe area including the impact of 1. Coupons and 2 coupon doubling and tripling. Last would be historical data of pricing. From this you couls create a shopping listfor each store each week and predict when prices will be at their lowest and coupons have the reatest impact.
I'm a newbie to Access and had a VB class over 16 yrs ago. I've done a lot of input to the main table just trying to get an idea of how to manage it with queries & reports. Any ideas? I know it's a lot so i'll understand if it's 2 much.
 
#2 ·
I think it's an interesting application. It's a bit much for a beginner, but since there are no time constraints, I say go for it. After all, the best way to learn is to keep plugging at a design that interests you.

If you're just starting out, I recommend learning some good database design principles. Start with database normalization and normal forms (Google those to learn more). You might also look into identifying and defining business rules prior to creating your tables.

I'd say jump in, and post back if you have specific questions about your design or how to use Access.

Good luck,

chris.
 
#3 ·
Thanks Cris! I know I'm in way over my head but my advantage is luxury of time. I want to share the end result with as many cheapskates as possible so we can beat the grocers.
Ok, here is my 1st specific question: Once I have all my items listed as a record in a table as well as in a separate table for varieties of.. apples, for example. Can I somehow Generate a list of the things I wish to purchase for the week, apply coupon rules to say if less than or equal 2 39 cents double and f between 40 and 50 cents double otherwise straight discount. I see no if/then capability. Thanks for looking at my thread.
 
#4 ·
I would want to see all the variables - too late at night for me to think this through. But to generally answer your question, yes, you could have price fields tied to a maximum amount, and a yes/no field for if there were coupons and a lot of other things - after you built it, you could just enter your data and then it would generate a reports telling you what to buy and at what price, with coupon, etc.
But, as Cris said, this is NOT an easy plan. My advice is to start messing with it and come back here for advice (regularly) on it.
 
#5 ·
I do not think that it is too large a project for a beginner as long as you take it in very small bites.
It will be the fact that it "interests" you that will keep you going when the going gets tough, plus you can get plenty of help from these guys for the "tricky" bits.
 
#6 ·
Thanks everyone. I was knee deep b4 I thought to bother u guys. I'm using several sources for my meager edumacation. I'll consult back when I hit the next sticking point that I can't even fathom. Thanks!
 
#7 ·
I like the idea, but how will you grab information from weekly fliers? Also do take into consideration the coupon expiration date as well when setting up the database.
 
#8 ·
Not that this part would be a project for a beginner, but I especially like the idea of a distributed system. If you had a Web server you could create online forms so many different (anonymous or otherwise) users could generate data; statistically, the wider distribution will allow you to evaluate more useful information. Access isn't really the right environment for that, but if it's a small distribution (50-100 users, say) there shouldn't be too many problems.

But that's down the line, down the line.

The extensibility of the application is extreme. Not only expiration dates, but price matching, quality comparison, relative proximity, divergent industries--this could be a really substantial project.

What a great idea though.

chris.
 
#10 ·
Thanks again everyone. In my area coupons are in the Sunday paper (or Saturday's "Early Sunday" edition) Store promotions are out Wed. and good to the following Tues.
We mainly shop on saturday. My current idea is to look at this on a weekly basis initially. I'll archive each week's report and use it later to predict when prices on items i will need will be at their lowest.
Coupon expiration is critical because I learned from a book titled "America's Cheapest family" that coupons come out for items when the price is high in it's cycle. If you hold on to the coupon for a few weeks it can be matched with the lowest price for maximum savings.
Web sites like http://www.mygrocerydeals.com and http://www.couponmom.com do some of this for you up front. What none of them do is tell you the best deal across major grocers in your area. You have to scan their site and evaluate the situation. Also they do not look at coupon doubling/tripling.
Yes I'm aware of how monstrous this can become. Just creating tables for items that have several varieties is becoming a headache. But the possibilities are too tempting to pass up. Wish me luck and stand by for questions. :D
 
#11 ·
Ok back to my original question. Is ther an IF/THEN function in access? 3 of the 4 major grocers will tripple a coupon =< 39 cents and double a coupon of 41 to 50 cents This is a key component of comparison to a) Wal Marts prices with coupon at face value, 2) determining lowest price.
My thinking is to Query straight price comparison then run a query assesing th impact of coupon doubling/tripling. Am I getting into depths that require SQL function?
 
#12 ·
There are basically 4 ways to a achieve If/Then type decisions.
1. Just manipulate 2 or more values (subtract one from the other) in a Query (SQL) and use that to "Order" the data thus putting the best deals first.
2. Use the Query's or Reports IIF function which works the same way as the Excel IF/Then Function.
3. Use a Query that calls a VBA Module that uses actual If/Then functions.
4. Do it all in VBA using If/Then Functions
 
#13 ·
OK, thanks OBP! I had a feeling I would have to dive into VBA and am awaiting a few books from the library to learn it. I'm biding my time by input to the many tables. Do you think this is something you would use acros the pond?
 
#14 ·
What about nested if functions as well? It would be setup something like this.

=if(test 1 to pass,if(test 2 to pass,what happens when you pass,what happens when you fail), test 1 failure item)

If then Elses are much more easier to manage, but if you are looking for immediate gratification until you get the book you could use that. ;)
 
#15 ·
Unfortunately we do not get many "Discount vouchers" in the UK so that part of it would not make much sense, what we do have is "Discounted prices", "Buy one get One Free", "Buy 2 get 1 free" type offers etc.
Whether anyone would use it I am not sure.
Jim, nested ifs or iifs of course, I just didn't take it that far I just wanted to make it clear that Access has plenty of Decision capability.;)
 
#16 ·
Whoops I hate that I always forget the extra I. Like OBP was saying it would be IIF and not IF in Access. I was obviously testing you to make sure you would read my post OBP. ;)
 
#17 ·
(Bearing in mind IIFs only process seven levels deep, unless they changed it.)

I don't know if the 2-for-1 model fits in with the business rules, but I bet you could account for that as well. I mean, logically it would be a 100% discount pending purchase. So if, say, the application allowed users to enter a purchase list, and if the user required more than one of a given item, and if that item had a 2-for-1, there's money saved. But getting things to that point is. Boy. 9 months of dedicated development? Cross-comparative analysis of user input based on variable data sets to return optimal results given user-defined criteria. That might be a bit more than what you're trying to do. I'm just thinking big. To be honest, I've been thinking about the data entity model for a couple days now. It's really a fascinating project.

chris.
 
#18 ·
Chris is definitely right that Access and Excel will only take 7 nested functions also called functions within functions. If you are going to be getting the VBA books in soon then you might want to hold out for If>>Then>> Else statements.
 
#20 ·
Well I'm thinking of doing it in steps rather than going several levels deep. Actually I'm thinking run a query then run a query on the result. The issue I have at the moment is taking the price at 5 grocers and subtracting the value of the coupon in the following manner: @ face value if over .50 @ or if the grocer does not double/triple. @ double the value if between .41 and .50 including .50 and lastly triple the value if .39 or <.
Do I need to create fields for the discounted value in the source Db then select it when building the query?
Also I have a check box to select the items for the week's shopping list. How do I tell Access to output only those records to a report?
Project is appearing more monumental by the moment but intruiging as well.:confused:
 
#21 ·
What about nested if functions as well? It would be setup something like this.

=if(test 1 to pass,if(test 2 to pass,what happens when you pass,what happens when you fail), test 1 failure item)

If then Elses are much more easier to manage, but if you are looking for immediate gratification until you get the book you could use that. ;)
Way over my head at the moment but I do appreciate it! :D
 
#22 ·
You could have some fields in your query that would take the value and do arithmetical work on them, the simplest showing what, given the value of the coupon is in field 1, what double the value is, another for triple the value, etc. Then you could base another query on that first one and calculate the difference between, say, double value and another discounting being offered...
 
#25 ·
So here's an ERD I put together, since there's been so much discussion of possible scope and such. Remember, this is just a logical model not a database design. I think it's a good skeleton to build on, but it doesn't include logic for users at all. That application layer is a bit too complex to model in an hour :p However, I do think these entities and relationships account for all the different requirements discussed thus far (minus distributed users, o' course).

I might actually keep playing with this and come up with some definitions. I'd be interested in input/feedback if anybody has some.

I don't really want to commandeer the project though, Zaney. I'm hoping my effort will be contributory to what you're working on :up:

chris.

[edit]
Gah, knew I forgot something. I was going to have an entity Industry to track product categories and retailers/businesses by industry. You can, erm, just doodle that part in if you look at the ERD.
[/edit]
 

Attachments

Status
Not open for further replies.
You have insufficient privileges to reply here.
Top