Summary Queries?? project due tomorrow

[sloppyjoe778]

Okay basically Im doing a access project on a supermarket. I already have to turn it in late because I ran into a problem with summary queries. I'll post picture of relationship diagram, and actual file too. But basically I have to do 3 interactive queries, 3 summary queries and 5 standard queries that will help or be useful to a business.



My standard queries aren't so bad but qry1specialty isn't that great. For interactive queries I plan to show all products of a specified category for 1. Also want something to do with promotions, maybe i can create a new field for old or new that finds if Date() is between start and end promotion date and marks it old or new and than user can type old or new and it shows that? For 3rd one I want find total cost of products, have user specify week, like last week or last 2 weeks.

So for those queries I just need help with one to do with promotions and how to specify the week.

For summary queries I need a LOT of help. At first I had some table with discount rates for promotions and I wanted to do the average discount rate but it didn't work. Watched a tutorial that says you need a group of data, like it doesn't work on a single column. Any ideas on how I should do this? All I have is to maybe average the cost of products of a certain category. I need more ideas though. Do I need a another table? Maybe something to do with food waste in different categories, can find the lowest product in each category? Idk.

Also need ideas for 3 reports to generate.

The biggies I need help with are the summary queries (using data I already have, or please suggest some new data to use), and 3 reports. Reports should be easy but just haven't decided which data is important enough.

So please any ideas? I'll take anything. Summary queries are killing me atm.


http://www.mediafire.com/?2pdsq10gdzcdn1h

[June7]

Too much too late. You do probably need to get familiar with DateDiff and DateAdd functions. Access Help has guidelines, also Google will get you some references.

[sloppyjoe778]

well I just have to turn it in sometime tomorrow. so theoretically I have a lot of time tomorrow to figure some things out. I'm confident I can do a passable job but just looking for some ideas for queries or new information to help to do queries.

[June7]

Some data doesn't seem normalized. Similar name fields as Supplier1, Supplier2 is an indication.

Not showing a link between tblSuppliers and tblWarehouseDeliveries.

Show SQL statements for attempted queries and/or provide project file for analysis.

[sloppyjoe778]

I did link to file in first post but I got a security token error so I used mediafire to upload in 1st post. Not sure what else to do. Btw I'll also attach the assignment sheet in 1st post in case anyone wants more info.

Okay here's roundup of current status of data/relationships needs and query/reports status and needs. So basically like a checklist on what I have to do. Please post any suggestions by 10-11pm est today so I can use them.

Order from Biggest Priority to Lowest priority

Big problems first
Examples of queries: Okay I just remembered I need 4 reports that are based on queries. 3 of them have to be based on 2 linked tables and 4th has to be based on 4 tables. I need an idea for a query that uses 4 tables. Also any suggestions of new data to add, only idea i have is waste spoilage and expiration dates. I mean it has to be based on costs or features of the product, like spoilage costs or something.

How to fix the warehousedeliveries where i have supplier 1 to supplier 5 as columns. Now I have it like that because access wouldn't let me add duplicate rows for suppliers and I wanted to have information on possible delays for each supplier for different products. So maybe can find the supplier that has the most delay for supplying bananas. Any suggestion on how to redo or rearrange warehousedeliveries? How to relate it? I'm going to try to connect it to suppliers and see if I can have "productid within suppliers" and include delays. Maybe I can use this as part of 4 tables. Can have a list of product delays. And also incorporate spoilage and if theres any discounts.

Says 3 tables have to have examples of an input mask, validation rule and lookup field. plz help with this.

Teacher says I need forms as a frontend to at least 3 tables. That just means you enter in new records right? Not sure if I can enter in new records though for anything other than products, unless I'm going to add a new store data or new supplier data. I guess that works.



Smaller Problems
qry1Quantity. have it return products that have had orders of > 2. So this is a way to gauge which products are selling in good quanities. So that data is useful to determine which products are good bulk sellers and maybe you can add some discounts for bulk buys (Status: needs to be in ascending order, but this one doesn't seem that great because it doesn't measure occurences of orders that were >2. Any way to improve this one?

?qry1Orders - for this one I'm unsure because I wanted to make an interactive query for this. The query would be to find total cost of orders in last week from start date. (Status: not sure how to this, really need help, although going to check textbook for info)

?Data - Have to enforce referential integrity. Also need to link suppliers to warehousedeliveries..

?qry1Age - find how many people who are under 25 who bought cleaning products (useful for demographic informtaion) (Status - add gender field and make it a OR calculation, of people who are under 25 or male). To make sure this uses 2 linked tables I'll probably make gender a separate field in customer table and not in orders table.



Things I'm Okay With
*qry1Phone - find users who there is no records of a phone number (have Is Null for phone field) (Status - it's simplistic but looks okay I guess)

*qry1Promotions It just gets all records from promotion table and adds a calculation field that discounts the price to a new price. I guess it gets data from 2 different tables, if I say products from products table and discounts and all that from promotions table. But maybe I can add one new field to product table like product weight. Or get category from category table.

qry2avgcost - this one I was thinking I could find the avg cost for products in a certain category.

qry2avgage - was going to find average age of customers, i guess I'd have to do it based by gender

?qry2discount - I wanted to find average discount of products in promotion list. Unfortunately I couldn't seem to average data in a column. Seems like it has to be grouped by other data. So I might have to add more records for category items like for Produce I might need a 2nd or 3rd discount record. I think instead of average I'll look for greater discount. (status: more records needed)

qry3category - was thinking here I could let a user type in a categoryID like Cleaning and it'll show all products from that category.

qry3productstatus - was thinking here I could have a OR query. Where user types in yes or on for discontinued, and yes or no for specialty and it'll show both or one of them. Seems good.

qry3 - for the last one I wanted to do a promotion query where user finds promotions that are currently active or not currently active. But that doesn't make use of wildcards so thinking I'm going to do the "Like [enter in a city name] & "*"" so they can enter in a few letters of a city and it'll return whatever matches. Or maybe i'll do that for zip codes

[sloppyjoe778]

Hey, I just editted the post where I listed areas I needed help with. Should be a lot cleaner and easier to read now.

If theres one thing I desperately need help with, its the idea for the report that uses 4 tables. Please help with that one. Do i need another table that links to the product? And maybe lists it as a spoilage product and it's typical expiration date? Maybe I can use product, spoilage status table, and promotion table, and category for the aisle number. I'm just going to do that for now but..

If anyone has an idea that seems better please let me know. Because even though that's an okay report, I'm not sure how easy it's going to be to explain how useful it is.

[sloppyjoe778]

Oh crap, I'm really having trouble with summary queries, they are not working. They are just showing all the records. I tried doing one for average cost of products and it's not working, maybe i need to do less fields or something.

Please help!!!!!!!!!

[alansidman]

Look here at the Group by Section

http://www.techonthenet.com/access/queries/index.php