ANY IDEAS?? Feeling Limited By Access....

[stephenmbell]

I have been given the task of creating an application in Access to track sales and competitor pricing to better make business decisions.

I pretty much have the data layed out the way that I need it, but I am having trouble deciding how to best display the data on the form.

Hopefully this is where all of you come in.

Basically we are selling widgets. Here is how the data needs to be displayed:

It will be a rolling 8 day display, so on Monday, from right to left we show Mon (current day - should be 0), Sun, Sat, Fri, Thu, Wed, Tue, Mon (of the previous week).

FOR EACH DAY:
-District Average of widgets sold (sum of all locations within district)
---Shop # widgets sold
---Difference of widgets sold district to shop
---Retail price of widgets (varies by location)

***HERE IS WHERE IT GETS TRICKY
The relationship of retail location to the locations competitors is one to many - not knowing how many competitors a location has ahead of time makes it difficult to have the proper number of form fields for each retail location.
***

I have this set up now in a continuous form (without the competitor information). There are combo boxes that allow me to select individual locations or all locations in a district (hence, the need for continuous).

I believe that by having a continuous form the one to many relationship of location to competitor is not possible?

I realize this is a lot to digest in a form, so any help or advice is appreciated.


thanks
sb

 

[lameid]

The first thing I would try is making a query that returns rows for all 8 days. You may need a table that lists days to ensure you get everything.

Then make an 8 column landscape report to show the data. Make sure it displays accross and then down.

Hopefully that is what you are after.

For more ideas check out the thread linked below.

thread703-1433467

 

[genomon]

Why not just use the form as a frontend, using data selected from the combo boxes as criteria to open a report?

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.

 

[ck1999]

Why use a form and not a report?

I would make a switchboard to allow the user to either select district summary or specific location (using a dropdown box for selecting which location). Then in the report header show location name, district average, etc. Then in the body show the information listed above then in the report footer list all the competitors


ck1999

 

[lameid]

To clarify I was suggesting a report solution.

If you are thinking data entry, I would forget about it. It would be a lot of work to do it.