Software

Ask something
Discussion Locked

What am I doing wrong????

0 helpful
9 replies

by joeplaisted

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello,
Okay boys, when he rushes again wait until he tries to bite Johnny, THEN shoot. Oh, sorry I have a major alligator in the making here and I can't figure what I am doing wrong. No don't worry just use your belt above the wound and the bleeding will stop. Anyway I have a database designed that tracks a sales rep's following information: total sales each month, the total margin he/she made that month, the total GMR (Gross Margin Rate), and the date it was entered into the database. Also a set budgetary figure for the year that each salesperson has to reach and hopefully exceed. I am using MSAccess 2000 as the database system. everyting on the setup is fine and all the info is in the database okay, but what the boss needs is be able to have the computer tell him what each salesman made each month. If the total sales for Smith for Feb were $2,000,000.00 and his Total sales for March were $3,500,000.00 then he made $1,500,000.00 for that time period. Problem is and this when the alligator started eating my personal body parts and causing a stroke, I can't get Access to subtract the margin dates from each other. On the report there is only one entry in the design view even though it lists every entry for the salesman. So I can see all his sales, but I can't figure out how to calculate the numbers. If anyone can help I would greatly appreciate it.
"Joe, bullets aint stoppin' this thing!!"
I gotta go, again any help would be very appreciated, because at this point I feel like the world's biggest idiot.

Thank you

9 replies

by sgt_shultz

whoa, bayou person! that alligator looks mean but jaws are weak! i will just duct tape them shut for you while you figure this out...
why you not just making query that adds up monthly sales. why the subtraction. why not make a really easy for you and not too tough for boss base report on parameter query that prompts for start date and end date then makes datasheet for all sales by that person during that time period. just to get it working while you make more red blood cells. then in query make change 'group by' property to Sum in query. bet that does it. then just make ordinary report with wizard. or you can make formula with Sum function in report footer. see Access help for parameter queries (they are easy) and for Example of Sum formula in report. works differently depending on where in report you put it, see? put it in Detail footer and sums details. put in page footer and sums page. and so forth. sorry no time for details. maybe one of these other he-men can make him into shoes for you and a purse for me.

by mjgtennis

So you are saying that a sales person only makes money if there sales continues to increase month by month? There are a couple of different ways of approaching that so please respond to that question first.

by it_cowboy

I can offer one bit of advice that may assist. Take a step back from the report and look at the way your data is structured.

Access has perhaps the best reporting tool, but it is only any good when the underlying data is in the right format. You may need to create several queries to return results in the right format (like all info for 1 salesman on 1 row) then link them together with another query.

Just from what you have posted, it seems to me you need a query for each time period with the salesman ID as the common field. when you have created all the time period queries totalled for each salesman, create another query linking the salesman table to all the time period queries. (if all salespeople have sales data for all months you can simply join them as normal however I would suggest that you define each link between the salesman table and the time period information as "all records from salesman and only those records that match from time period query").

by chorito

if u want a solution...email me a copy...let me take a look at everything and i WILL give u an answer... :-) email it to chorito1975@yahoo.com...if it's bigger than 3 MB let me know...and i'll give u another email address.... :-)

This discussion is closed to new replies.