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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
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
 

luvv2rock

New Member
Joined
Oct 27, 2006
Messages
12
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>
 

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
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
 

luvv2rock

New Member
Joined
Oct 27, 2006
Messages
12

ADVERTISEMENT

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?
 

luvv2rock

New Member
Joined
Oct 27, 2006
Messages
12
well, n/m kinda I updated the query myself to work, but how do i get this query to exclude SIL and RM?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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
Top