Base one value on another (1 Viewer)

B

Banterman

New member

Local time

Today, 05:15

Joined

Aug 27, 2017

Messages

3

• Aug 27, 2017

• #1

Hi All,
I am new to access as excel is not powerful enough for me anymore.

My first hurdle is to base the cost price of one thing on anoter as we have many varients for each product and I do not want to change the price for each one.

Please see attachment.

I would like to keep all products in one table.

All help is appriciated, thanks in advance

Wesley:banghead:

 

Attachments

• 
  ACCESS.jpg

  90.6 KB · Views: 154

bob fitz

AWF VIP

Local time

Today, 05:15

Joined

May 23, 2011

Messages

4,726

• Aug 27, 2017

• #2

Access is not a spreadsheet program. I would suggest that you read up on table design and database normalization. Search this forum or on google for a mass of info.

 

NauticalGent

Ignore List Poster Boy

Local time

Today, 00:15

Joined

Apr 27, 2015

Messages

6,317

• Aug 27, 2017

• #3

Greetings Banterman,

It appears you are trying to do calculations at the table level which is not how things are done in Access. When it comes to products the only costs you should put in is your cost. Any variances should be done with forms and reports.

I would advise looking at the Norhwind DB example which shows an excellent method for inventory management and retail sales.

Classic mistake made by Excel users...

 

NauticalGent

Ignore List Poster Boy

Local time

Today, 00:15

Joined

Apr 27, 2015

Messages

6,317

• Aug 27, 2017

• #4

Sorry Bob, I hadn't noticed your input before I answered...

 

B

Banterman

New member

Local time

Today, 05:15

Joined

Aug 27, 2017

Messages

3

• Aug 27, 2017

• #5

Thanks guys,

Gonna be harder than I thought.

Where is best to get some professional help ?

Thanks again

 

NauticalGent

Ignore List Poster Boy

Local time

Today, 00:15

Joined

Apr 27, 2015

Messages

6,317

• Aug 27, 2017

• #6

Not hard at all, just takes an adjustment in thinking.

 

bob fitz

AWF VIP

Local time

Today, 05:15

Joined

May 23, 2011

Messages

4,726

• Aug 27, 2017

• #7

Banterman said:

Thanks guys,

Gonna be harder than I thought.

Where is best to get some professional help ?

Thanks again

Click to expand...

Wesley,
Did you see the message that I sent you?

 

B

Banterman

New member

Local time

Today, 05:15

Joined

Aug 27, 2017

Messages

3

• Aug 28, 2017

• #8

Hi Bob trying to figure it out. I don't have time to research and build myself. Added you to my contacts.
Please advise on moving forward.
Wesley

 

bob fitz

AWF VIP

Local time

Today, 05:15

Joined

May 23, 2011

Messages

4,726

• Aug 28, 2017

• #9

Hi Wesley

I have sent you another message with some contact details.
Let me know if you don't receive it otherwise I look forward to hearing from you.

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 00:15

Joined

Feb 19, 2002

Messages

43,196

• Sep 20, 2017

• #10

This is a very typical situation for a data entry application. To solve the problem, you need a table that defines products. At its simplest, this table contains the current UnitPrice. In your form when you choose a productID from the product combo, the AfterUpdate event copies the UnitPrice from the combo to the OrderDetails record. The price can be changed if for some reason this customer is not paying the standard price.