Ensuring only one commission per product in Access Table

Good afternoon,

Please can someone help me.  

In my Access Database I have a table called tblCommission, with the fields:
Commission_ID;Client_account; Product_code; Commission; Exchange (£ or $)

I need a method to ensure that each client can have a commission set for 
every product - but that a client cant have to commission's for one product:

eg:  I need a method to prevent this:
Comm_ID   Client_account   Product_code   Commission   Exchange
1                   000001            Coke                3                $
2                   000001            Coke                2                $

Does anyone have any suggestions?

I am quite new to Access and completely stuck with this.

Further infor:

Client_Id is related to my Client_ID in a tblClients
Product_Code is related to my Product_Code in a tblProducts

Really appreciate any help. Thanks in advance

Paul
0
Utf
1/13/2010 2:06:01 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

2 Replies
1357 Views

Similar Articles

[PageSpeed] 43

"PVANS" <PVANS@discussions.microsoft.com> wrote in message 
news:65670E26-8EDD-41A9-AAD8-201CD71AF81C@microsoft.com...
> Good afternoon,
>
> Please can someone help me.
>
> In my Access Database I have a table called tblCommission, with the 
> fields:
> Commission_ID;Client_account; Product_code; Commission; Exchange (£ or $)
>
> I need a method to ensure that each client can have a commission set for
> every product - but that a client cant have to commission's for one 
> product:
>
> eg:  I need a method to prevent this:
> Comm_ID   Client_account   Product_code   Commission   Exchange
> 1                   000001            Coke                3 
> $
> 2                   000001            Coke                2 
> $
>
> Does anyone have any suggestions?
>
> I am quite new to Access and completely stuck with this.
>
> Further infor:
>
> Client_Id is related to my Client_ID in a tblClients
> Product_Code is related to my Product_Code in a tblProducts
>
>

Hi Paul.

One way would be to make a composite primary key from the Client_account and 
Product_code fields.

Keith.
www.keithwilby.co.uk 

0
Keith
1/13/2010 2:49:17 PM
It does not need to be the primary key field but just a composite index from 
the Client_account and Product_code fields and set to unique.

-- 
Build a little, test a little.


"Keith Wilby" wrote:

> "PVANS" <PVANS@discussions.microsoft.com> wrote in message 
> news:65670E26-8EDD-41A9-AAD8-201CD71AF81C@microsoft.com...
> > Good afternoon,
> >
> > Please can someone help me.
> >
> > In my Access Database I have a table called tblCommission, with the 
> > fields:
> > Commission_ID;Client_account; Product_code; Commission; Exchange (£ or $)
> >
> > I need a method to ensure that each client can have a commission set for
> > every product - but that a client cant have to commission's for one 
> > product:
> >
> > eg:  I need a method to prevent this:
> > Comm_ID   Client_account   Product_code   Commission   Exchange
> > 1                   000001            Coke                3 
> > $
> > 2                   000001            Coke                2 
> > $
> >
> > Does anyone have any suggestions?
> >
> > I am quite new to Access and completely stuck with this.
> >
> > Further infor:
> >
> > Client_Id is related to my Client_ID in a tblClients
> > Product_Code is related to my Product_Code in a tblProducts
> >
> >
> 
> Hi Paul.
> 
> One way would be to make a composite primary key from the Client_account and 
> Product_code fields.
> 
> Keith.
> www.keithwilby.co.uk 
> 
> .
> 
0
Utf
1/13/2010 8:59:04 PM
Reply:

Similar Artilces:

Export or Print multiple reports from one Access Report by Group
I have a sales report that is grouped by Salesperson. Each salesperson has multiple pages of the report and I need to either print it or export it to PDF as individual reports for each salesperson. Is there a way to export/print the report into seperate reports for each Salesperson? Thanks. I am pretty new to Access, but I have figured out how to get the report in the structure I want it. I just can't get the information out of the program in the format. I can print as one PDF document and then go and cut that up in Adobe, but I wanted to see if Access could do it for me and save ...

Code stops for one user, like it has a breakpoint
I have a user where the code stops at this line like it has a breakpoint, but it doesn't. There is no issue on my system. myUserName = Environ("USERNAME") When the user presses F5, it continues to run. Has anyone seen this and what do I need it to do to run without stopping? Thanks, Barb Reinhardt Barb The only thing that stikes me about this is the case. I run several files in here that are used by about 50 people every days. Environ("UserName") has never given us a problem (note the use of Proper Case rather than your UPPER). -- ...

Table of Content
I am trying to create a 3 level Table of Contend that looks like the following: 1.0 PURPOSE 1.1 Second Order Heading 1.2 Second Order Heading 1.3 Second Order Heading 1.3.1 Third Order Heading 1.3.2 Third Order Heading 1.4 Second Order Heading 2.0 PURPOSE The following macro gives me this: 1.0 PURPOSE 1.1 Second Order Heading 1.2 Second Order Heading 1.3 Second Order Heading 1.3.1 Third Order Heading 1.3.2 Third Order Heading 1.4 Second Order Heading 2.0 PURPOSE Here is my macro. How can I fix this? With ActiveDocument.Styles("...

create chart /table excel-save, close & reopen colors change? Why
When I create a chart/table in Excel - save, close & reopen to use again, the colors have changed for my formating. How do I set the formating so that the colors stay the same - I have tried styles with no luck. The colors need to be the company approved. Thank you, Anne, I am unable to reproduce yr problem. Excel should NOT override yr manual settings. However,you can set yr company colours as default under Tools/Options/[Color tab]. Here are the colours Excel uses by default for the workbook. (Under that thin line you see the defaults for fill and line colours.) Hope this...

Error in coding going from one computer to another.
I have a database that works great on my laptop where it was created. I placed it on our server but I am the only one who can access and work with it. Everyone else gets an error for the String. Any suggestions? This is the actual error: Private Sub Combo230_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Quality Plan ID] = " & Str(Nz(Me![Combo230], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub where ther error is at 'Str'. Thank you Dyper How about telling us t...

Table structure 04-07-10
I am having trouble getting my tables organized for their intended purpose. I am wondering if I am breaking them down too much. My first table is called: tblPMList and contains the following fields: MaintencanceID (This is an auto number and also the primary key) txtStructureNumber (4 unique numbers) txtStructureName txtPMCode (280 PM activities assigned to the structures, some unique, some alike) txtPMDesription (describes the work for the PM Code) txtPMType (Either mechanical or electrical) txtEquipmentNeeded (what equipment is needed to preform the PM) txtPMFrequency (1, 4, ...

Can't see existing table in Design view for a QBE
Hi Guys, I can't see a table in QBE even when I have the scroll bar as far left as I possibly can. I have joineed another table to the query and I can see the relationship link but am unable to scroll left to view the table. Does anyone know how to fix this?? Many thanks in advance, Cheers, On Tue, 18 May 2010 17:35:01 -0700, DontKnow <DontKnow@discussions.microsoft.com> wrote: >Hi Guys, > >I can't see a table in QBE even when I have the scroll bar as far left as I >possibly can. I have joineed another table to the query and I can see the ...

Support for more than one Sch. C?
Does Money Small Business 2004 Support more than one Schedule C? I have 2 sole proprietorships and my wife is a Self Employed Realtor sole proprietor. We file Jointly and need to keep track of income and expenses for at least 2 and preferably 3 or more Schedule C's plus all our personal expenses. If Money does not support multiple schedule C's directly, does any one know how we can do this better than the following? We are presently using Quicken Premier Home and Business 2004 with a different number series for each business but they are all connected to the same Sch C cat...

Money 2004 oddity with downloaded Schwab Access transactions
I've been using Money 99-2003 with a Schwab Access (checking) account since 1999 with no problems online, download only. After installing 2004, when I finish accepting online transactions and Money goes back to the Download Report screen, the Local Balance column for the account starts with this massive negative number then starts counting down (well, up actually) to *close* to the actual amount, then the MOney 2004 screen goes totally white for 15 seconds or so. The download report reappears and all is ok. I've run both level 1 and level 2 salvages. Anyone else seeing this? -- _____...

OWA Access through Different Domain password
I have two domians lets call them DOMAIN1 and EXCHANGE All the user logon to their XP clients authenticating from DOMAIN1. Exchnage Server 03 SP2 is running on Windows 03 in a different domain EXCHANGE Both the domains have a trust relationship. The way this is setup is I have created similar users in both the Active Directories and given Full Mailbox Access to user DUMMY in DOMAIN1 on DUMMY in EXCHANGE. This way when the user login to their XP they don't have to authenticate again for Exchange, just like the way its suppose to be. Now I have this issues with this setup. In order t...

MS Office Outlook Web Access for Exchange server 2003
We have just updated exchange and since this changes the way Outlook Web Access appears, I am trying to find on the Microsoft web page any documenation that is written for the client side of this application that would explain what the screens look like, what each button does, what each folder is, etc etc. Basically, we are going to provide OWA training soon and a non-technical (baby step) document would be helpful if it exists rather than having to create it from scratch. Does anyone know where I can find this? ...

Table of Contents Automatic Update
Is there a way that I can set the table of contents to automatically update like when I save or close a document? If so, how? Thanks! The following pair of macros should work: Sub FileSave() Dim t As TableOfContents For Each t In ActiveDocument.TablesOfContents t.Update Next t If ActiveDocument.Path = "" Then Dialogs(wdDialogFileSaveAs).Show Else ActiveDocument.Save End If End Sub Sub FileSaveAs() Dim t As TableOfContents For Each t In ActiveDocument.TablesOfContents t.Update Next t Dialogs(wdDialogFileSaveAs).Show End Sub Place the macros in the Normal ...

Move one cell to another
I need to copy the content of one cell to another when a different cell is greater than 239. Example: if N20 is greater than R20(value 239) then copy the content of B20 to S20. OR A method to delete all records that have a value less than 240 in column N. Any help would be appreciated. Thanks, Mike -- Mike Formula in S20: =IF(N20>R20,B20,"") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Mike" <Mike@discussions.microsoft.com> wrote in message news:219BB2BA-6CEF-4E74-AB00-DCFCEB84BAED@micro...

Income / expense / net comparison over 4 years in one chart
How do I create a chart that compares the Income, Expense and Net Income across multiple years (4-5 years)? By "Income" do you really mean net revenue? Due to the potential changes in magnitude of gross revenue, net revenue, total expense, and net operating income, I would suggest creating three seperate graphs. The first graph would show net revenue, the second total expense, and the last net operating income. This approach would be much simpler for you to create as well as for the viewer to understand than trying to combine all three components. I would use a simple ba...

Excel query Access DB
I am using Excel to write invoices. The columns in the excel invoice sheet are product ID, Name, Price, Quantity and Total price. All product information is stored in a separate Access DB, but because I am not a wiz I currently enter all data manually. To minimize typing and errors I would like the spread sheet to automatically retrieve specific values (name and price) from the product table in access using the product ID as criteria. How can that be done? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages direc...

PowerPoint 2003 to accessible Word 2003 via VBA?
I'd like to extract all content from a Windows PowerPoint 2003 document and place it in a Word 2003 document, as Word to PDF conversion seems to provide better-quality tagging than a PowerPoint to PDF conversion. I know I can save the outline as RTF and open that in Word, but the outline does not include any of the following: - text added on a blank slide layout - text in a text box - tables. - images. Is there any way to copy all content from a PowerPoint presentation to a Word document, such that all text remains text and all tables remain as tables? Charles Belo...

Password recovery product for v2003
Does anyone know of a product for password recovery that works with Money 2003 version? >Does anyone know of a product for password recovery that >works with Money 2003 version? Have you tried the all purpose password recovery tools at - http://www.password-software.com/password-recovery.htm ? Thanks for the tip. I've searched on the web a bunch. All the products are for versions prior to 2003 or 2004. I recently separated some accounts from our personal ones that were for a probate that I'm doing. I changed the password and don't remember it now. Bummer. This is ...

Error accessing POP-3 account
In addition to the Exchange Server I have a POP-3 account defined. It always works when I first open Outlook, but it fails 2/3 of the time afterwards. The error message is Task 'david POP - Sending and Receiving' reported error (0x8004210A): 'The operation timed out waiting for a response from the receiving (POP) server. If you continue to receive this message, contact your server adminstrator or Internet service provider(ISP).' I never have trouble with this POP-3 account from home or via the web. If it can't be fixed, is there any way to shorten how long it ...

Is there any way to get outlook web access FREE!
I am a singer/computer tech person I need a web access account becouse i find it to be one of the most best emails on the planet. My friend uses it and it ROCKS Well if you cna help me please EMAIL ME AT michaelrobertgoetz@msn.com Outlook Web Access is a feature of Exchange Server. The only way to get it is to use a hosted Exchange service. It is not free. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 "Michael Goetz" <Michael...

when import EXCEL to ACCESS, the date format excel column title changed...
when linking EXCEL to ACCESS, the date format excel column tilt changed in ACCESS table. For example: Excel column title : May-04,Jun-04,Jul-04.... changed to F09,F10,F11..... In ACCESS linked table Why and how can I keep the date tile as linked table fields name? Thank you.. Attachment filename: try.zip Download attachment: http://www.excelforum.com/attachment.php?postid=57659 -- Message posted from http://www.ExcelForum.com Hi are your Excel columns real date values (just formated as 'MMM-YY')? -- Regards Frank Kabel Frankfurt, Germany ...

CRM
Does anyone have a SQL report writer report (rdl) (CRM3) that list all users, their roles and the privalage settings. Needed for auditing purposes. Thanks On Jun 2, 10:53=A0am, Steve <St...@discussions.microsoft.com> wrote: > Does anyone have a SQL report writer report (rdl) (CRM3) that list all us= ers, > their roles and the privalage settings. Needed for auditing purposes. > Thanks There is the default user summary report. Leon Tribe Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ Thanks Leon, Unfortunately I need to drill thro...

transferring data in rows of one table to columns of another table
Hi All, We are working with FCC station data that puts some simple numeric data in one file, arranged in a number of consecutive rows for each station. The next station's data follows consecutively. Each group of rows that are common to a station share an index number, while the next group uses its own separate number. A separate file contains the main information fields in a table of separate rows, or records, along with a matching index number. We'd like to move the numeric data in a group of rows that share the index # for a station, to a series of new fields add...

Cond Format -- on set of columns, then one column
I currently have two conditional formats, and I'm only able to have one work or the other. Specifically for example, I have columns A through F populated, and when C18 is populated with "Complete" I want two things to happen: 1) A18:F18 are shaded Gray. 2) C18's font format changes to Bold Red Currently I can do them individually via the following: 1) Highlight columns A:F, then put Formula in Cond Format this: =$C1="Completed" 2) Highlight Column C, and put Cell Value in Cond Format: equal to "Completed" But, I can't do both at the same tim...

how to move from our current exchange server to a new one???
We are moving from an exchange active passive cluster to a single exchange server (brand new compaq). My plan (in theory) is to disconnect the current exchange server, build the new exchange server with the same name, install exhange in disaster recovery mode, and then restore the databases from tape. Does this sound correct to you guys? Are there any documents that would apply to this situation? thanks, Matt Why keep the same name? Build your new server alongside the old and move the mailboxes across. If you're worried about the new server name meaning that you'll need to v...

Help! I'm very new at this, working with Access 2000
I am creating a database for complaints. I have a main table for Complaints, and a joined table for Illness Details. Part of Illness details are symptoms, and a person can have many symptoms. I created a separate table for Symptoms, linking it with the Illness Detail ID. When I try to print a report with onesection for symptoms,(all symptoms in same box) I can not get the multiple symptoms to print in that box. If I have more than one symptom, my report will only print one symptom for each line, repeating all the illness detail information for each complaint . -- Nancy B. Please...