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
984 Views

Similar Articles

[PageSpeed] 41

"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:

Lost IE icon on desktop
Windows XP - Lost the internet explorer icon on the desktop. I know how to get a shortcut IE icon on the desktop but when I right click on a shortcut IE and go to properties, I don't get the options to delete browsing history and such. I don't get the general tab to delete this information from the shortcut icon. Anyway to bring the original icon back? It is not in the recycle bin and not sure how it was removed from the desktop. Hi, With Windows XP, this article will fix things for you. 945402- [Fix it] The Internet Explorer icon is missing from your desktop: http...

One to many relationship
Is there a way to create a one to many relationship in CRM 3.0? Example... I need to be able to track details of a peice of equipment we sell to you customers so I want to create a one to many relationship (one account with many records with details of equipment we sold to this customer). Yes, you would create a new entity and then you can create a relationship between them. -- Matt Parks MVP - Microsoft CRM "DBuggy" <DBuggy@discussions.microsoft.com> wrote in message news:EC2DC9FF-3F0D-4938-B18C-1172EFED5DF1@microsoft.com... Is there a way to create a one to many r...

Report writer Table security violation
I have a user that is getting a table security violation when they are running financial reports out of report writer. This seemed to start after upgrading to 10. ------=_NextPart_0001_8AA41D26 Content-Type: text/plain Content-Transfer-Encoding: 7bit Check that they have access to the tables they need. You can install the Support Debugging Tool for assistance with security issues using the Security Profiler and Security Information windows. http://blogs.msdn.com/developingfordynamicsgp/archive/2009/08/05/installing-the-support-debugging-tool-for-microsoft-dynamics-gp- faq.aspx David ...

Exchange 2003 Admin w/o ability to gain service acct access & read other users' mail?
I recently installed/configured an Exchange 2003 server for my organization. Upon completion they removed me from the Administrators group so that I would not be able to read other users' email. Although this right is disabled by default, MS has left a couple backdoors so admins can grant themselves Service Account access and read other users' mailboxes. See: http://support.microsoft.com/?id=821897 MS Reasoning appears to be, "Just don't do it" -- which is not enough to satisfy my company principals. They are uncomfortable with even the possibility that I *could* add se...

Opening Forms based on Combo Box Selection (Access 2007)
I am creating a database in which people in 10 different regions will enter information into forms for facilities in their respective regions. I'd like the users to open the database, select their region from a combo box and click an "open form" command button that will open all the records/forms in their region (right now, the forms only have the facility name and address information entered). How do I link the command button function to the combo box? I'd like to learn to do this using the embedded macro function because I am not very familiar with VBA coding....

Access to News groups disappears after first use and exiting.
Have used OE for a number of years for email and News Group access. Thought I might experiment and then switch to Outlook. Set it up, and, received reply on the test. I know it was working. Then tried to set up newsgroups. Followed directions as outlined in help. Last direction was to drag News to "Go". Finished ... went to go and selected News. All the News Groups I had earlier setup in OE came up. Played around looking at some posts. Then exited. Then when I went back to Go, News was no longer there. Completely disappeared. I am using Outlook 2003. Actually recopied a "Gh...

SQL database connections and access from multiple clients
How many concurrent connections can SQL Express 2008 support ? I have a situation where 10,000 clients need to access and update SQL Express database. Is it ok that all the clients directly connects to SQL express and update the database ? What issues can come up in the scenario ? OR is it advised that we write a database access layer(component) which takes care of updating the database and all clients access this components for reading or writing ? regards, Thomas Thomas I think there is no limit concurrent connections for SQL Express 2008 We have some applications that han...

information from one form to automatically be put into another
I have created one form where a sales consultant chooses customer selections from a drop-down list and in some cases a check box. When the selection is made, can this information go directly into a specific spot on the contract which is a different document? If so, how do I do this? See http://gregmaxey.mvps.org/Extract_Form_Data.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "KellyR" <KellyR@discussions...

Query joining two tables with different dates
I'm sure there is a simple solution to this, but being fairly new to Access I would appreciate some assistance... I have a query that joins two tables. One table has call details for each day that an agent works. The other table has details for each call that is graded for each agent. However, calls can be graded on days that the agent does not work. When joining the tables, the query will only display calls graded on dates that the agent works. This is what I would like to do to correct this: If there was a call graded on a day that the agent did not work, this data be rolled...

Marking one occurrence of a recurring task as complete
Hi, I am new to Outlook, about 2 weeks now. I entered all my tasks, most of which repeat on a weekly/biweekly/monthly basis, making sure that I noted each went through EOY. I noticed the other day when I was in my calendar view in July, I didn't see any tasks listed. Sure enough, after mid-June, I didn't see any tasks. I thought I'd done something wrong, but when I looked into it more, the tasks are all marked completed. For example, I had a task to send out notices to update reports on the 15th of the month. Back on May 15th, I sent out the message and marked the t...

must have more than one check in que to print
Microsoft money will not show that just one check is to be printed. Is there a way to correct this? Thanks in advance!! Gejage@aol.com See other thread you also poked into. The answer posted in the other thread (twice now?) may work for you. "Gejage" <anonymous@discussions.microsoft.com> wrote in message news:082801c3ce23$56e26280$a301280a@phx.gbl... > Microsoft money will not show that just one check is to be > printed. Is there a way to correct this? ...

Outlook XP Cannot Access Exchange 5.5
Hi all, We are currently migrating our outlook clients from 2000 to XP. But with some users (1 out of 10) cannot access there mailbox, on our exchange 5.5 SP server, with Outlook XP. They get a dialog asking them for there username and password. When you type in nothing happens no error, you just have retype the password. If they use Outlook 2000 they can there mailbox just fine. I have found MS KB 297781 "You Repeatedly Receive a Prompt for Credentials When You Log On to the E-mail Server" But this seems to be only if access with a pop3 account. Is this a know problem?...

multiple owner for one account or for example contacts
Could i assign more than one owner for example entity account or contact ??? I want to all logged crm user to see My Activities the same for all owner (crm user). Thans for answer On Aug 11, 3:24=A0am, "Rafal" <rkoczen_1...@o2.pl> wrote: > Could i assign more than one owner for example entity account or contact = ??? > > I want to all logged crm user to see My Activities the same for all owner > (crm user). > > Thans for answer Hi Rafal, No it is not possible to have more than one owner for any records. What you can do is make the "Open Activies&q...

Inserting form values into a table
We have a form with values taken from an sql query that comes from two different tables. We would like to enter the information into a third table. Can some one direct me to code that will do the following: 1. Provide the Insert sql that shows us how to add the form values to the table 2. Show us how to loop while inserting the information into the table (there could be several lines on the form, each must be inserted one at a time). I have worked with Access before and have never had a problem inserting information. However, I cannot quite figure out how to insert informtion through an ...

To ensure the working of SSRS
How to ensure that the reporting service is working fine in a machine, which is installed with SQL server 2005? Submitted via EggHeadCafe - Software Developer Portal of Choice Dr. Dotnetsky's Cool .NET Tips & Tricks #1 http://www.eggheadcafe.com/tutorials/aspnet/9aade63c-be0f-4fe5-a562-9f2750db35bc/dr-dotnetskys-cool-net.aspx Go to http://yourservername/Reports/Pages/Folder.aspx Even before you have deployed reports you should see the empty portal. If you are in the local administrators group on the machine you will see a contents tab, new folder, new data source etc. ...

Merging Multiple columns into one
How do you reverse the Text to columns function in excel. I need to merge 8 columns into one with out losing any of the data in the columns. I separated and address into multiple columns to do a sort on my worksheet, now I want to merge the address data back together. The long-hand way is to create a 'helper' column and enter a formula to join each column back together. Example: =A1&","&B1&","&C1&","&D1 This would join columns A to D separated with a comma Then simply copy the formula down the column. >-----Original ...

MFC and MS-Access
I have a C++ project and want to be able to look up some data from an Access database. The user will have to do some navigation to find the record they want but rather than create dialogs and fill in list boxes in my project, is there a way to switch to Access using automation, use Access forms, select the record I need and then pass back an 'long' value to the C++ program so then I can use DAO to look up the data from the MDB. This will be a big help if anyone knows of a way to do this. Many thanks Phil MS Access has a rich type library. For example here is the first bit o...

ECHO OFF in Access Macro?
Hi: Is there a way to turn echo off and on in an access macro? I know you can do it with VBA, but in this applications it not desirable to complicate it with VBA since neophytes will be maintaining it. Thanks John Baker On Sun, 19 Aug 2007 15:28:24 -0000, JHB wrote: > Hi: > > Is there a way to turn echo off and on in an access macro? I know you > can do it with VBA, but in this applications it not desirable to > complicate it with VBA since neophytes will be maintaining it. > > Thanks > > John Baker I don't understand this post. It took me 2 seconds t...

Access 2003
When attempting to create a report (via the wizard), I get the msg "The wizard is unable to create your report". I understand that this may be due to printer setup issues, but am not sure how to resolve. Other relevant facts: - Access 2003/Vista - I have printers installed & print regularly from other applications - When I try to print. print preview, or page setup while looking at a table (or form), nothing happens (I'm new to access, but it seems like I should be able to print a table?!) - as in I select any of the above options and nothing happens. Other posts...

Using a function result in one cell to change a format in another cell
I am trying to use a function result (ie: if a result of one cell = whatever), then the font color in a different cell & a different sheet should change to another color. Hi Andrew one way: 1. Select the cell which you want to check (lets say cell A1 on sheet 1) 2. Define a name for this Cell (goto 'Insert - Name - Define'). e.g. define the name 'Conditional_Check' 3. Select the cell for which you want the color to change based on the first cell's result 4. Goto 'Format - Conditional Format' and enter the following formula: =Conditional_Check=value 5. Define y...

one note opens all folders
For a couple of weeks now onenote opens all the folders on my computer as the default. I just want it to do the 'explore' option as normal. It only opens folders in onenote, not the documents or other file types contained within them, they open as normal. I've tried to reset the defaults for folders through control panel with no success. I've read you can delete a key in the registry to fix this but I'm not sure about doing this. My computer runs vista and onenote 2007. I'm not sure what's caused the problem, my computer's aniti-virus Kaspe...

one sided posting entry in gp?
how would I create a one sided entry in GP to correct a double sided entry that posted twice ( due to a system failer a month ago)? There used to be a document on customer source; but i cant find it. Thanks for any information I'm unclear as to why you need a one sided entry instead of backing out one of the double sided entries that posted incorrectly. To backout one of those entries click "Correct" on the JE Transaction Entry Screen (Transactions->Financial->General) Mark (DynamicAccounting.net) On Sep 18, 12:54 pm, 2020 <2...@discussions.microsoft.com> wro...

Ensure Directory is writable for All Users
Hello All, I'm new to security issues, but my application has to make sure that a specific directory is writable for all users. How can I achieve this? Here is the pseudo code of what I need: if( directory is not writable for all users ) // No1 { if( I have admin rights ) // No2 { change permissions for directory // No3 } else { Prompt the user to start this program as administrator } } I don't know, how to do steps No1, No2 and No3. TIA -- Michael Reim Michael DOT Reim AT helmut-fischer DOT de For...

One to One relationship
I have an Events entity that is an offshoot from the Contacts entity. Basically, it tracks what events each contact has been invited to, and is used mainly by the Events Coordinator. I've set it up as a One (Contact) to Many (Events) relationship. However, I really only want one event form for each contact form. The reason I've set up a different entity rather than just adding a tab is that access to this entity needs to be tightly controlled. Obviously, if many events forms are created for the one contact, it would be very difficult to tell which one is accurate etc. Is it possibl...

Next Date given one date
At this time, I cannot use VBA. Here is my objective: For a given treasury note security: Cell A1, Date 6/17/2004 - today's date Cell A2, Date 7/15/1997 - first coupon date I want to identify the "previous coupon date" and "next coupon date" from today's date based on the frequency of the "first coupon date". Logically speaking: * Security pays 2 times each year * Based on first coupon date, pay dates are: 1/15 & 7/15 * Given today is 6/17/04, the next pay date is 7/15/04. * Given today is 6/17/04, the previous pay date is 1/15/04. Can this be...