Can Access drive down numbers?

[Jessicamb]

Hi There

This may be a total access newbie question but I cant find the answer in my Access books.

I have in an access database sales history. This is segmented in several ways - eg brand/season/product line.

I want to be able to apply something over the top of those numbers that Access will drive down by the sales history.

I think this needs an example:

I sell 300 reds, 30 green type a and 200 green type b.

I want to give my customer a 10% rebate on all sales except green type a.

I could figure this out so far - but I need to be able to analyse the rebate in the same detail as the sales (so I can cut the data by various segments) to generate a full P&L.

There are many different combinations of product type/season/brand/customer and type of term.

The only way I can find to do this is to put the data in excel, apply some formulas and copy the data back into access.

Is there an easier way of doing this? I was thinking of setting some sort of template up where I could enter all the parameters for the rebate that would pick up the sales and apply a set rebate rate to these sales - but I'm not sure how to do this.

In terms of access skill I can build queries in the builder but I've never used the VBA side of it (have done in Excel though).

Thanks in advance.

Jessica

 

[giacomo]

If I'm understanding you correctly, I think you need to capture your rebates in a separate table. This way you can preserve the history for later analysis.

I can provide more detail if you can provide some sample data.

hth,
Giacomo

 

[Jessicamb]

If I'm understanding you correctly, I think you need to capture your rebates in a separate table. This way you can preserve the history for later analysis.

I can provide more detail if you can provide some sample data.

hth,
Giacomo

Hi Giacomo

I dont have any data to hand but it looks like this with a few thousand more rows and permutations:

Chain Season Product Line Brand Jan Feb Mar Apr May
Anda ED Card Branded 10 10 15 20 25
Teco ED Bag Branded 5 5 1 10 3
Teco FD Card Unbranded 10 1 1 1 10

And for example

Teco get a 10% rebate on branded cards and 5% on unbranded.
Anda get a rebate of 5% on all ED products.

I would like to be able to produce something that will enable me to see the same information as above but instead of sales it shows the rebates, if possible.

Is this ok?

 

[giacomo]

Jessica,

if that is what your table looks like you have some other issues you're going to need to deal with first. Please read some of the links provided in my signature regarding database normalization.

You need to break out some of your data into different tables in order to eliminate duplication of data. For example: are chain, line and brand all product attributes? if so they should not be in this table. Likewise is Season an attribute of month (or vice versa)? if so then that should be in another table as well. Lastly, you should have rows for each month not columns. When your months are in columns if makes summing and counting extremely difficult.

please read the links below...

hth,
Giacomo