Access Table (Resolved)

luvv2rock

New Member
Joined
Oct 27, 2006
Messages
12
I have a table with all kinds of fun stuff in it. What I need to do is take price fields 2-5 and have them be 5,10,15,20 % off of price field one, IF their qualifiers are SIL or RM otherwise I need price 2-5 to be the same value as 1. what would be the easiest way to do this. THank you
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
First off, I would discourage you from storing the 5,10,15,20 % fields in the table. These can all be calculated using a query at run time and will be easier to manage and ensure that data is accurate and in sync.

If you need help developing the query, please post your table structure, sample data, and sample query output.

HTH,
CT
 
Upvote 0
Here is a sample of my data, note price 6-10 will be left at 0. I don't really know what you mean by a sample query output...
<CENTER><TABLE cellSpacing=0 align=center cellpadding=2px width=50% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 23 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><font color="White">ARI_Info : Table</font></TD><TD CLASS="AccTBInner" align=right >Access 2003</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccHDRMain" align=left >Part Qualifier</TD><TD CLASS="AccHDRMain" align=left >Part Number</TD><TD CLASS="AccHDRMain" align=left >Description</TD><TD CLASS="AccHDRMain" align=left >List Price</TD><TD CLASS="AccHDRMain" align=left >Stock</TD><TD CLASS="AccHDRMain" align=left >Supercession or Alternative Part Number</TD><TD CLASS="AccHDRMain" align=left >Part Weight</TD><TD CLASS="AccHDRMain" align=left >Price 1</TD><TD CLASS="AccHDRMain" align=left >Price 2</TD><TD CLASS="AccHDRMain" align=left >Price 3</TD><TD CLASS="AccHDRMain" align=left >Price 4</TD><TD CLASS="AccHDRMain" align=left >Price 5</TD><TD CLASS="AccHDRMain" align=left >Price 6</TD><TD CLASS="AccHDRMain" align=left >Price 7</TD><TD CLASS="AccHDRMain" align=left >Price 8</TD><TD CLASS="AccHDRMain" align=left >Price 9</TD><TD CLASS="AccHDRMain" align=left >Price 10</TD><TD CLASS="AccHDRMain" align=left >Box Height</TD><TD CLASS="AccHDRMain" align=left >Box Width</TD><TD CLASS="AccHDRMain" align=left >Box Length</TD><TD CLASS="AccHDRMain" align=left >Handling Type</TD><TD CLASS="AccHDRMain" align=left >Handling Value</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >ZAM</TD><TD CLASS="AccDataElem" >0004001</TD><TD CLASS="AccDataElem" >STRAINER</TD><TD CLASS="AccDataElem" >0.37</TD><TD CLASS="AccDataElem" >10</TD><TD CLASS="AccDataElem" >
</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0.22</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >KL</TD><TD CLASS="AccDataElem" >001233</TD><TD CLASS="AccDataElem" >N L A</TD><TD CLASS="AccDataElem" >2.75</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >
</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >HOF</TD><TD CLASS="AccDataElem" >010557S</TD><TD CLASS="AccDataElem" >BLADE CUTTER 6IN EART</TD><TD CLASS="AccDataElem" >9.83</TD><TD CLASS="AccDataElem" >5</TD><TD CLASS="AccDataElem" >
</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >7.29</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >SIL</TD><TD CLASS="AccDataElem" >02-050-0</TD><TD CLASS="AccDataElem" >HANDLE BOLT, SNAPPER</TD><TD CLASS="AccDataElem" >1.14</TD><TD CLASS="AccDataElem" >20</TD><TD CLASS="AccDataElem" >
</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0.56</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >ROT</TD><TD CLASS="AccDataElem" >207</TD><TD CLASS="AccDataElem" >CONNECTOR SPADE 22 18</TD><TD CLASS="AccDataElem" >0.4</TD><TD CLASS="AccDataElem" >10</TD><TD CLASS="AccDataElem" >
</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0.16</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" >0</TD></TR><TR ><TD ID="IE" CLASS="AccIEOnlyMain" colSpan= 23 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccButton" >
</TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccButton" >
</TD><TD CLASS="AccIEOnlyInnerLeft" > of 5</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 23 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data291835964" CLASS="AccInfoBarInnerRight" >ARI_Info</TD><TD ID="Info291835964" CLASS="AccInfoBarData" > Record Count: 5</TD></TR></TABLE></TD></TR><TR ><TD CLASS="tpkrow" colSpan = 23 ><CENTER><font size = 1>Access HTML Add-in provided by theprimarykey.com</font></CENTER></TD></TR></TABLE></CENTER>
 
Upvote 0
Try this for your query:

Code:
SELECT tblData.[Part Qualifier], tblData.[Part Number], tblData.Description, tblData.[List Price], tblData.Stock, tblData.[Supercession or Alternative Part Number], tblData.[Part Weight], tblData.[Price 1], [Price 1]*0.95 AS [Price 2], [Price 1]*0.9 AS [Price 3], [Price 1]*0.85 AS [Price 4], [Price 1]*0.8 AS [Price 5], tblData.[Price 6], tblData.[Price 7], tblData.[Price 8], tblData.[Price 9], tblData.[Price 10], tblData.[Box Height], tblData.[Box Width], tblData.[Box Length], tblData.[Handling Type], tblData.[Handling Value]
FROM tblData
WHERE (((tblData.[Part Qualifier]) In ('SIL','RM')));

Change tblData to the name of your table.

HTH,
CT
 
Upvote 0
THank you for that, it did the computing for the two qualifiers, now how do i get that to write to a table or to turn it into a query where the rest of my products will be added and keep price 2-5 the same as price 1?
 
Upvote 0
well, n/m kinda I updated the query myself to work, but how do i get this query to exclude SIL and RM?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top