DragonTamer
New Member
- Joined
- Jun 13, 2018
- Messages
- 3
OK guys, be patient with me as I try my best to explain what I need in a way that doesn’t make me look like a raving lunatic. (I might be one, I just don’t want to look like one).
OK I am open to any solution that works in Excel or Access (including SQL statements) because I really don’t know the best way to accomplish this.
I have a spreadsheet that references 4 other sheets (let’s call it the Summary Sheet and the other four are Data Sheets). I have the Summary Sheet pulling over items from one of the data sheets and then cross referencing information from each of the Data Sheets (pretty straight forward right?). Here comes the problem, one of the sets of data (Let’s call it quantity discount) is a list of items that get a discount if the customer buys a certain quantity; so you might buy 10 for a 5% discount or 20 for a 10%. There is no limit to the number of discounts an individual product might get. I am not sure how to cross reference this in a way that creates a new line for the product if there are more than one quantity discount. So I would need it to resemble the following:
No discounts:
ProductA 1 $5.00 ea
One Discount:
ProductA 1 0% $5.00 ea
ProductA 10 5% $4.75 ea
Two Discounts:
ProductA 1 0% $5.00 ea
ProductA 10 5% $4.75 ea
ProductA 20 10% $4.50 ea
Etc.
Does anyone know of a way to accomplish this in either Excel or Access? I am drawing a blank, other than taking an action that I really don’t want to take and telling my boss that just maybe he shouldn’t have cheaped out on our ERP!
OK I am open to any solution that works in Excel or Access (including SQL statements) because I really don’t know the best way to accomplish this.
I have a spreadsheet that references 4 other sheets (let’s call it the Summary Sheet and the other four are Data Sheets). I have the Summary Sheet pulling over items from one of the data sheets and then cross referencing information from each of the Data Sheets (pretty straight forward right?). Here comes the problem, one of the sets of data (Let’s call it quantity discount) is a list of items that get a discount if the customer buys a certain quantity; so you might buy 10 for a 5% discount or 20 for a 10%. There is no limit to the number of discounts an individual product might get. I am not sure how to cross reference this in a way that creates a new line for the product if there are more than one quantity discount. So I would need it to resemble the following:
No discounts:
ProductA 1 $5.00 ea
One Discount:
ProductA 1 0% $5.00 ea
ProductA 10 5% $4.75 ea
Two Discounts:
ProductA 1 0% $5.00 ea
ProductA 10 5% $4.75 ea
ProductA 20 10% $4.50 ea
Etc.
Does anyone know of a way to accomplish this in either Excel or Access? I am drawing a blank, other than taking an action that I really don’t want to take and telling my boss that just maybe he shouldn’t have cheaped out on our ERP!
