Can Access drive down numbers?

Jessicamb

New Member
Joined
Sep 13, 2006
Messages
35
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

New Member
Joined
Sep 13, 2006
Messages
35
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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
 

Watch MrExcel Video

Forum statistics

Threads
1,111,491
Messages
5,541,034
Members
410,542
Latest member
GDiddy1984
Top