Products/Services Table

I am using Access 2007. I have a database for my Products/Services industry. 
I have a table containing my products/services, as well as a Work Order Form 
that is linked to the Products/services table from which I can choose via 
drop down the products that I am selling to that customer. The 
Product/services table contains 3 fields; Product, Cost & Price. The Work 
Order Form also contains those three fields, and when I choose a product from 
the drop down, the corresponding Price field fills in as well. Now, sometimes 
(after bargaining of course!) I want to change the price for that specific 
work order without it affecting the main Products/services table, can anyone 
help?
0
Utf
4/8/2010 4:59:02 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

3 Replies
731 Views

Similar Articles

[PageSpeed] 52

You should have a separate table for Work Orders. Your work order form 
should be based on this table. This table design makes the price in the work 
order table independent of the price in the products/services table. You can 
leave the price auto fill in the work order form and if you need to change 
it just move your cursor to the price field and edit it. The end resuly will 
be no change to the product price in the product/services table and a 
different product price in the work order table than is in the 
product/services table.

Steve
santus@penn.com


"dvvog" <dvvog@discussions.microsoft.com> wrote in message 
news:F8BB2244-1995-4E47-89D3-E54A2AD58413@microsoft.com...
>I am using Access 2007. I have a database for my Products/Services 
>industry.
> I have a table containing my products/services, as well as a Work Order 
> Form
> that is linked to the Products/services table from which I can choose via
> drop down the products that I am selling to that customer. The
> Product/services table contains 3 fields; Product, Cost & Price. The Work
> Order Form also contains those three fields, and when I choose a product 
> from
> the drop down, the corresponding Price field fills in as well. Now, 
> sometimes
> (after bargaining of course!) I want to change the price for that specific
> work order without it affecting the main Products/services table, can 
> anyone
> help? 


0
Steve
4/8/2010 5:45:46 PM
You would need to have the appropriate fields in the "child" table to store 
the values. Typically the combo box for the products/services would contain 
columns for the price. The after update event of the combo box would have 
code to set the value of the price field like:

    Me.txtPrice = Me.cboProdService.Column(1)

Columns are numbered from 0.

-- 
Duane Hookom
Microsoft Access MVP


"dvvog" wrote:

> I am using Access 2007. I have a database for my Products/Services industry. 
> I have a table containing my products/services, as well as a Work Order Form 
> that is linked to the Products/services table from which I can choose via 
> drop down the products that I am selling to that customer. The 
> Product/services table contains 3 fields; Product, Cost & Price. The Work 
> Order Form also contains those three fields, and when I choose a product from 
> the drop down, the corresponding Price field fills in as well. Now, sometimes 
> (after bargaining of course!) I want to change the price for that specific 
> work order without it affecting the main Products/services table, can anyone 
> help?
0
Utf
4/8/2010 5:53:01 PM
Dvvog,

I just did this very same thing.  Both Duane, and Steve are correct.  I’m 
just wanted to add a little more detail.  Good luck.

I have a standard rate for my time, but it is negotiable especially if I 
realize I can bring something to the table that no else can.

I have two tables; tblInvoice and tblInvoiceTrans tables.  The tblInvoice 
table has the parent information for the invoice and the tblInvoiceTrans 
table has one record for each transaction on the invoice.

I also have an employee table  called tblEmployee.  It contains all of my 
employee’s name and hourly rate.  On my Time subform, I have a combo box that 
provides a drop down list of employee’s.  When the user chooses an employee, 
the software copies the employee rate to text box control called txtRate.  
This text box (txtRate) is bound to a the Rate field on the invoice detail 
table.  Since txtRate is a separate control bound to a field on the 
tblInvoiceTrans table, I can change it to anything I want on that particular 
invoice transaction without altering the base rate on the tblEmployee table.

Here is the SQL row source for the cboEmployeeId control:

SQL Beginning -----------------------------------------------

SELECT qrytblEmployeesD.EmployeeID, qrytblEmployeesD.FirstName & " " & 
[LastName] AS EmpNm, qrytblEmployeesD.CoName, qrytblEmployeesD.BillingRate

FROM qrytblEmployeesD

ORDER BY qrytblEmployeesD.CoName DESC , qrytblEmployeesD.FirstName & " " & 
[LastName];

SQL End -----------------------------------------------------------------

Here is the AfterUpdate event for the cboEmployeeId control:  Note, the 
index in the column count starts at 0 (not 1).  So EmployeeId is column 0, 
FirstName & Last Name is column(1), CoName is column(2), and BillingRate is 
column(3).

Code Begin -----------------------------------------------------------------

Private Sub cboEmployeeId_AfterUpdate()
On Error GoTo Err_cboEmployeeId_AfterUpdate
    
    Me.txtRate = Me.cboEmployeeId.Column(3)
    
Exit_cboEmployeeId_AfterUpdate:
    Exit Sub

Err_cboEmployeeId_AfterUpdate:
    Call BaseUtil.Dsp_Err_Msg(Err.Number, Err.Description, cpstrFormName & " 
- cboEmployeeId_AfterUpdate")
    Resume Exit_cboEmployeeId_AfterUpdate
End Sub

Code End ------------------------------------------------------------------


Me.txtRate is  my control on the Invoice Detail form that is bound to the 
Price field on the tblInvoiceDet table.


Good luck.

Dennis
0
Utf
4/9/2010 3:25:01 AM
Reply:

Similar Artilces:

Product Key
Is there anyway to retrieve the product key from a SS2005 Standard Edition installation? Can I use Developer Edition media and convert to Standard Edition using the installed product key after testing? I only have one licensed production server. No media no key for Standard Edition. OS on both servers is Windows 2003 sp2. Thanks, Kevin ...

how do i delete cells or duplicate cells in a table ?
I went to make a table with 8 colums and 3 rows, which turned out fine..but now i need to make 3 more rows added on but half the size long with only 2 colums.. so i figure i can just duplicate or copy from my table 3 of these rows and shirk it and delete 1 cell...but trying to get help on how to add or remove cells seems the hard part ...

Visual Basic
Hi, I trying to create a table using visual basic, when i try to run it. It prompt me "No fields defined, can't append Table or Index. Anyone, could highlight to me what is the problem. My code is as follows: Public Sub PCreateTable() Dim dbs As DAO.Database Dim tdf As DAO.TableDef Dim idx As DAO.Index Dim fldTradeDate As DAO.Field Dim fldStockName As DAO.Field Dim fldRemarks As DAO.Field Dim fldCurrency As DAO.Field Dim fldClose As DAO.Field Dim fldChange As DAO.Field Dim fldVolume As DAO.Field Dim fldHigh As DAO.Field Dim fldLow As DAO.Field Dim fldValue As DAO.Field Dim fldDay...

Refreshing a pivot table #2
I have posted this question before but didn't get any replies so am trying again in the hope that somebody knows the answer.....!! When using a pivot table, is it possible to refresh only certain columns rather than the whole table?? Any urgent help would be appreciated. Thank you. Louise You can't refresh only part of a pivot table. Perhaps you could make a copy of the source data, change only the data you want updated in the pivot table, and base the pivot table on that. Louise wrote: > I have posted this question before but didn't get any replies so am trying > ...

Keep Pivot Table custom chart type
I'm creating a pivot table custom chart type "Line - Column on 2 Axes". The chart looks great when I create it. The problem is when the user does anything to the chart (filters the data, adds a new flield of data, etc.), the chart changes to where both data series are displayed as bar charts and the second axis is gone. I can't find an event that fires when they modify the chart, so I don't know that I can programatically change it back. Is there a way to keep it from changing in the first place? I'm using Excel 2003. Thanks! Loss of formatting is a known prob...

CRM 1.2 Installation Error 800700B7 unable to Configure Web Services
Hi, I have followed the steps and pre-requisites in the Installation guide, however, every time I have tried to install the productl, I get a message stating "Setup was unable to install CRM Server. Setup was unable to configure the Web Services that are using ASP.NET. Cannot create a file when that file already exists (800700B7). Can somebody please help me with this? I am unable to use the product because of this error, as the installation does not complete all of the necessary steps. Thank you in advance. CM not sure if you are using windows 2000 server or not, but I had a sim...

Has the service pack and Moble connectivity update been released?
Has the service pack and Mobile connectivity update been released? There was talk at the last Microsoft seminar I attended back in June that there would be some patches and feature updates for CRM released this summer. Have they been released yet? Bill Walter yes they have been released. Go to support.microsoft.com then downloads and seach on microsoft crm. the mobile addon has been released as well as updates for Windows XP SP2 -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Bill Walter" <wqwalter@hotmai...

Smart list data not in tables
I have alot of data that for some reason is in the smart list but is not veiwed in the tables....any easy way to get the data back in the tables. MR Have you used some tool like Query Analyzer to view the suspected tables? If you will tell me the SmartList you are trying to view, I'll give you the tables. There is no way SL is showing nonexistent data; it's just a report writer. "Mike R" <MikeR@discussions.microsoft.com> wrote in message news:12CC49A0-FB4F-48F8-AAD8-7EBB2D973CCE@microsoft.com... >I have alot of data that for some reason is in the smart list b...

Is it normal when the sever's response is "service unavailable"
Just set up my new Outlook address for home use but when I sent a message I got an error message stating : No respones and server is unavailable try again later. Does that mean I did not set up well? When you say you set up for home use, are you trying to pull work email from home? -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ "may" wrote: > Just set up my new Outlook address for home use but when I sent a message I > got an error message stating : No respones and server is unavailable try > again later. Does that me...

trying to start a service with VBScript on Win7
Hello: I'm using Windows 7 Home Premium. I have a service which is set to manual -- I only need to run it when I'm doing development and thus not take up resources. At any rate, none of the previous scripts I have, nor the samples from the ScriptCenter will start this script. When I try to make a batch file, from command line I get: [SC] StartService: OpenService FAILED 5: Access is denied. From searching on Google, I guess this is a permission problem but since the home premium flavor of Windows 7 doesn't come with GPEdit, I'm at a loss what to do. Is...

Exchange 5.5 Service Account Domain Rights
Silly question... Does an Exchange 5.5 service account need to be a domain admin? It seems like it does not need to be domain admin, just local admin on the exchange server. If possible, please provide a M$ link. thanks, FastEddie On Tue, 13 Sep 2005 13:43:05 -0500, "FastEddie" <fasteddie@therockwells.net.no.spam> wrote: >Silly question... > >Does an Exchange 5.5 service account need to be a domain admin? It seems >like it does not need to be domain admin, just local admin on the exchange >server. Nope. In fact, it is recommended that it not be a d...

pivot table: create worksheetsheet using pivot table
While working in a pivot table, I accidentally created a new worksheet in my file with isolated data from the report e.g. pivot table shows all days of the month with totals for each deposit account... my 'accident' created a sheet with all the data for a single date. It turns out, this would be a useful tool for my manager, but we cannot reproduce my accident. My mouse was on the blitz and after a few solo selections the new worksheet appeared. Does anyone know how to do this (intentionally)? Please help. ...

subtotal
I am using Excel 2003, recently upgraded to service pack 2. I have a macro that opens a delimited text file, I then copy all, paste values and number formats and then run a macro that subtotals a balance by change in every consumer. The problem is that when, upgraded to service pack 2, the last record does not subtotal, all other records do. I have tried copying only the "live" data after the marco opens the text file. Still the same error. I have tried pasting different ways - values, normal paste, paste values and number formats. Same thing happens - the GRAND TOTAL - ends u...

Pivot Tables as Data Source
Does anyone know how to create a Pivot Table with multiple Pivot Tables as it's source? The source Pivot Tables retrieve data from different locations and are structured in the same way as each other, with the same dimensions - I would like to produce a summary Pivot Table. Thanks Stephen ...

Retreiving Data From CBM Tables
Hi, I'm facing some problem in retrieving CBM Tables in order to design a report for my customer. For my report I want to list out the Payments Posted Records from CBM and for that I have joined the two tables i.e. CB900035 & CB777001 BELOW IS MY QUERY: SELECT B.CHEKNMBR, A.CHEKBKID, B.CB_Trans_Amount, B.CB_Type_Description, A.CB_TRANS_TYPE FROM CB900035 A, CB777001 B WHERE A.DOCNUMBR=B.CHEKNMBR AND A.CBVOIDEDCANCELLED='' AND A.CB_TRANS_TYPE='P' AND A.CHEKBKID ='BatchNO' AND A.DATE1='12-31-2005' -------------------------------- In some cases the...

creating a table with multiple columns
for a High School science project my daughter created a survey with 39 possible responses (3 groups of 13 responses in each group) the people taking the survey are supposed to pick the top 4 responses in each group (numbered 1-4, 1 being the highest). Other information is Age, grade and gender of the person taking the survey. What would be the best way to create the table and eventually charts to diplay the results of the survey? I was thinking of a flat table with a number in the cells correspondig to their answers but I have no idea how to create the charts or compare the answers w...

Field Service & FSA: Printed Service Call Tickets
Printed Service Call Tickets should include the Item Description ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=8f4b09ea-b67f-425e-a364-bfa992904003&dg=microsoft.public...

Table Import #3
Hi there I am looking at using the Table iMport fuction to update and populate some added modules to our existing GP V10 install. although I am having a problem finding documentation on this tool. Say for example...I need to bring in new inventory Items...which I have used Integration Manager to do before...does table import insert record or just update current records??? Does someone have the linkls to the documentation?? Since adding the Manufacturing modules to our existing install...some functionality with IM has stopped working as in updating the Standard Costs on the Item card...

Case being changed in table
I have a Text field in a table. It has no Format or Input Mask specified. When I enter Two Capital letters in a row (and more than three characters), something is changing the second letter to lower case. If I enter Three Capital letters in a row, then the second letter is NOT changed to lower case. How do I prevent this from happening? Example: I enter ABCtest, and I get ABCtest I enter ABtest, and I get Abtest.....THIS IS WRONG I enter AB Test, and I get AB Test It's the AutoCorrect options. Not sure what version of Access you are running, but in 2003, Select Tools - ...

automatically update the pivot table
I am using excel 2003 How can I update the pivot table automatically when I open the workboolk ? Thanks -- eva cheng eva... you can try with this link http://www.vbaexpress.com/kb/getarticle.php?kb_id=80 "eva cheng" wrote: > I am using excel 2003 > > How can I update the pivot table automatically when I open the workboolk ? > > Thanks > -- > eva cheng eva cheng wrote: >I am using excel 2003 > >How can I update the pivot table automatically when I open the workboolk ? > >Thanks Pivot Table Options> Da...

Cells in a Word 2007 table do not grow in length; text disappears
Hello, In MS Word 2007, I am working with a document that someone else created. There is a table in the document, and when I add text to the cells in the table, the cells do not get longer to display the text when the text reaches the bottom of the cell. Instead, the text and cursor seem to disappear behind the next cell down in the column (it goes behind the cell that is immediately below the cell that I'm typing in). How do I fix this so that the length of the cell will change as text is added to it? I want the cells to be a fixed width, but get longer if they have to...

Using different version of SQL Server for Test and Production
We are upgrading our Production CRM environment from SQL Server 2000 to SQL Server 2K5. For an unknown amount of time, our Test environment will still be running SQL Server 2000. Is it possible to successfully import customizations into a SQL Server 2K5 evironment from a SQL Server 2000 environment? Has anyone ever tried this? Thank you, Terry M. Yes, Microsoft CRM customization in v3.0 can be import/export between environment running either SQL Server 2000/2005 (both are supported by v3.0). Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm "Terry M." wrote: >...

SMTP Service hangs on Ex2K3 on 2K Server
I've recently started having the smtp service hangin and mail piling up in the queue without any apparent reason. Box is 2K server with Ex 2003. There are no events in the event log at the times this happens. Any thoughts as to where to start looking? Any local AV clients with port filter rules enabled? Or local firewall software? Is there any pattern to the service hanging, eg time? Nue "Lew Berry" <LewBerry@discussions.microsoft.com> wrote in message news:3771C379-7B42-46BF-BF94-B3C819345195@microsoft.com... > I've recently started having the smtp service h...

Two-Input table #2
Allowence $au 2500 Exchange rate 75% Converted rate 1875 How do i do a two input table to calculate rates on a table A B C D E $1875 1000 1500 2000 2500 50% 55% 60% 70% so it calcualtes the rates for me? i have fiddles around with a few different ways but am getting no where fast. Please Help and thanks in advanced...I am a new user so be sensitive please.... In A1: 2500, in B1: 75%, then in C1: =A1*B1 C1 is the base formu...

Pivot Table
Hi, I am reusing my pivot tables by updating the data sheet that supports them. This data typically includes customers who may or may not be on the list during each update. The issue I am having is that customers who are no longer on the current list are still showing in the pivot fields (the drop down ones with the selection box's in the "row" area). Does anyone know how I can get rid of this? Big, BIG, thanks to anyone who can help. Minh Are you refreshing the table? Richard Choate "Minh Phan" <anminhphan@yahoo.com> wrote in message news:06f301c34a6...