Query Problem With Composite Primary Key
May 16, 2005
I am trying to run a query that links to tables, orders and invoice together. I am pulling fields from each table, with a relationship between Order# which is found in both tables. The only problem is, order# is a composite key in the order table and strictly a foreign key in the invoice table, referenced through a combo box drop down. When i attempt to create a query, it will not run. (Also will i find a similar problem with my combo box reference?) Any ideas on how to create a query would be appreciated.
View Replies
ADVERTISEMENT
Sep 9, 2005
I have a rather interesting situation involving creating a primary key. Basically I have two tables.
Table 1
Date, Customer, Part Number, Work Order, Qty tested, qty failed, tech name
Table 2
Defect, reference, quantity(defect), defect cause, part number, work order
The ideal relationship I want to create is that for every Table 1 there are multiple table 2. Regular one to many relationship. The problem is that in the first table it is possible and probable that there will be duplicate records because of the process. The test parts by individual carts, but often they're exactly the same parts with same work orders and same everything else. How should I go about doing this. I don't want to just create an autonumber for each record if possible. Any suggestions?
View 1 Replies
View Related
Feb 25, 2005
Is it possible that a composite primary from table A to be a foreign key in table B?
I have two tables: A and B. The A has some personal details such as first name, surname, phone number, etc.
Since no personal ID is given in my project, I am thinking to use first name + surname as the primary key of table A. But when I wanted to make the relationship between A and B (in B I need to use these 2 name fields as the composite primary keys, too) and ticked the option “enforce referential integrity”, an error message prompted out said it could not find the only primary key in table A.
What should I do with this? Or it is impossible to have a composite primary key to be foreign keys (but also primary) in another table?
It will be great if someone could answer this!
Thanks!
View 4 Replies
View Related
May 5, 2005
I've noticed that the great and the good all seem to recommend not using composite PKs. Does that even go for join tables in the middle of a many-to-many relationship? Not sure I see what's to be gained by adding an ID and making that the PK.
Any comments?
Dave
View 14 Replies
View Related
Jun 2, 2013
Composite Primary Key in Ms Access 2010. I shall try and explain my tables in detail. So my first table is "Client Database" where the field "Buyer" is primary key. This is connected to my next table "Project Database" which has fields such as Work Order #, PO #, PO Date , Buyer etc where Work Order # is the primary key. Finally "Projects Database" is connected to Order information. Now the problem is until now, I always had 1 PO # for each Work Order #. However now I am facing a situation where my client has clubbed multiple PO #'s for the same Work Order #. I need to trigger the Primary Key Violation only if both the Work Order # and the PO # are the same.
View 8 Replies
View Related
May 29, 2012
I am trying to set a primary key on two fields. the problem is that the one field is null for some records. They will eventually fill them in. However, because it is null now it will nowt let me set it as a primary key. I tried setting the field, in the properties, with a default value; but it would not take it.
View 2 Replies
View Related
May 17, 2007
I am creating a bridge table to get rid of redundant data. I am doing it by making a composite of the primary keys from the two tables I am bridging.
The error message when I get to a certain point is "You cannot add or change a record because a related record is required in table tblMachCent." Both the numbers I am using to create this composite key are in the tables necessary, so I am not sure why I am getting this problem. Any suggestions??
View 1 Replies
View Related
Sep 23, 2014
I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key.
Tbl_treatment:
ID=numeric field (also in tbl_pt and in tbl_tumor)
Tumornr=numeric field (also in tbl_tumor)
Treatmentnr=numeric field
[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:
1001 1 1
1001 1 2
1001 1 3
1001 2 1
1001 2 2
1001 3 1, etcetera.
However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:
1001 1 1
1001 1 2
1001 1 3
1001 2 2
1001 2 3
1001 3 3, etcetera.
If (DCount("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) = 0 Then Me.treatmentnr = fRowNum(False)
Else Me.treatmentnr = (DMax("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) + 1
View 3 Replies
View Related
Jan 23, 2006
hello
how can i create compostie keys in access???
also how would validation rule for a text box which would allow a user to enter a date which between a certain range in relation to today's date. eg the date entered cannot be more than one week greater than the current date. i knew who i could do it using VBA but i want to know how to do to using the regular access features.
thanks you :)
View 7 Replies
View Related
Mar 10, 2007
Hi, I am in a great hurry and I need someone to help me as soon as possible. Basically, I have a table called Column, which contains 4 fields:
Column (autonumber, primary key)
Column Description (text)
Rack (number)
Rack Description (text)
There are around 30 columns, and each column has 3 racks. There are different racks for different columns, so the rack description will be different. That is why Rack and Rack Description must be in the same table as column.
The problem is that I cannot make the table have one column with three different racks. I thought perhaps the solution would be to make a composite key, where with Column and Rack, we would be able to identify everything else in the table. But the problem is that I do not know how to create this composite key. Please help me with this. I really need to get this done as soon as possible.
Thanx in advance
View 2 Replies
View Related
Mar 5, 2007
16593
I have attached the above relationship diagram as I am having a few problems with a table that uses composite keys to link to 2 other tables. The table is called subsmag and contains the fields subid and magid to link to the subscriptions (subid) and magazine (magid) tables. Basically 1 subscription can have many magazines and I am using the subsmag table to link the 2 tables. When I try to enter data in a form it doesn't like the composite key. Can anyone help, I have inherited the database design and I'm thinking it needs a redesign.
thanks
View 2 Replies
View Related
Aug 10, 2007
Is it best to have one PK in a table and that as an autonumber?
If so, why do people create composite PK's. Examples please.
View 3 Replies
View Related
Dec 9, 2004
Before I start, I know there is much about "Cascading Combos" in the forums, but I'd rather not go that way.
I have a table of Servers (only field is Server ID) and a table of Domains (Server ID, Domain ID). This is one-to-many, as each server will have multiple domains within it.
I also have a table of Applications which has as part of its primary key Server ID and Domain ID (i.e. that's where the app is installed).
I have used the Lookup tab in table design to easily provide a drop-down list of Server IDs for any new Application record, coming straight from the Servers table.
I want to (simply) add a drop down to the Domain ID field to select Domain IDs that are defined *for the entered Server ID*.
I already have the referential integrity enforced, but would like this simple aid as there are heaps of Servers and lots of similarly named Domains. It's looking to me like this is not possible, as the nub of the issue is how to qualify the Server ID field in the new record i.e.
SELECT [Domain ID] FROM Domains WHERE Domains.[Server ID] = <current record>.[Server ID]
How to define <current record>?
I know some will say forms are the tool for this job, but I need to see lots of records at once as I am entering (or changing) data.
I may be hoping against hope that this lookup capability can cope with a simple composite key.
View 4 Replies
View Related
Dec 5, 2005
I have recently started a new project, on one of my tables i have a composite key for my primary key. However when i create a relationship using one of the fields in the primary key i can't "enforce referential integrity". Every time i try i get the message "No unique index found for the referenced field of the primary field"
can any one help
Mindy
View 2 Replies
View Related
Oct 6, 2007
Many to Many
I have a basic question, I know for a fact that all many to many relationships have to be broken into two one to many relationships using a junction table. However does that mean it is (a must )to have the two foreign key of the parent tables have to be the (primary composite key) of the junction table.
For example, let’s say I have 5 tables that I need to establish many to many relationships between them. Is it correct to establish a junction table with all 5 foreign keys of the parent tables as composite primary key in the junction table to!
Hope the above making sense!!! :confused:
Many thanks :)
View 3 Replies
View Related
May 13, 2005
Can Access deal with Composite Primary Keys? or will it only let you assign a single attribute key?
If it does allow you to create composite primary keys, how do you go about doing it?
View 1 Replies
View Related
Aug 29, 2006
Hi
I have a table Orders with fields InvoiceNo And Invoice Series.
I want if of course anyone can help me to Validate the next Invoice Number Not To Be Dublicate and to automatically Increment. Meaning that if Last Invoice number was 45 and Invoice Series is A the next to be A 46. If Last Invoice without Series is 40 the next to be 41, and so on. I can manage to increment the values in one field Invoice number but I can not incoporate the Invoice Series.
Any help would appreciated.
Note : I'm not good in VB so please be kind to me.
Kind regards
John
View 5 Replies
View Related
May 12, 2005
I need some help with developing a certain aspect of a db. I need to track the equipment list of clinics. The equipment list doesn't really change (it has 5 possible values though each clinic may only have one to all 5 of them). The db is also to track who the manufacturer is.
I was originally going to do a three piece composite key consistenting of the clinic id, the equipment id and the manufacturer id with quantity as the non pk field. Or the other possiblility that I can see is just using an autonumber as the pk with clinic id, equipment id, and manufacturer id as fk's with qty as the non-key field. So what do you think? If you can put in explanation why your suggestion would work, that would be appreciated as I'd like to increase my knowledge base on this stuff.
If I haven't made my problem clear, I'm quite willing to provide more info.
Thanks,
scratch
View 5 Replies
View Related
Nov 6, 2012
I have a monitoring database, the site_ID, Date and survey_type are indexed as this uniquely identifies each survey. I am not sure if how I really link the Site table to the other three, or if it would be better if I had a Survey_ID field that acted as the link. This then gives me a few problems in how to enter the data.(Note. I havent included PK's for the conditions, frog and bird survey as yet as I am not sure which is the best way to go at this point).Site
Site_ID
Date_SurvSurvey_Type(i.e. frog, bird)
Conditions
Temp
[code]....
View 4 Replies
View Related
Oct 3, 2007
Hi,
I am trying to join 2 tables with composite keys - each of them have the same 4 columns as part of its key - date/person/problem/date sent.
Table 1 has 5 columns (4 key columns + 1), Table 2 has the same 5 columns plus 1 additional column (Comments).
I am trying to match the Comments column in Table 2 to Table 1 and show the output in another table.
Currently, I've related the 4 columns in each table to each other, each relationship defined as including all records from Table 1 and only the records from Table 2 where the fields are equal.
In the fields, I have 5 Columns from Table 1 and the last column (Comments) from Table 2. When I run the query, I get all the records from Table 1, but not all of the Comments that match from Table 2 are shown.
Any Ideas on how to improve this link?
Thanks!!!
View 7 Replies
View Related
Aug 4, 2006
I will try to explain this in as detail as possible. I have One table having numerous columns.
Columns
RowNumber <primary key>
A
E
B
C
D
Now what I am trying to do is retrieve all records in this table where the combination of A and E is unique. e.g. if table looks like
RowAEBCD
111blahblahblah
221blahblahblah
322blahblahblah
421blahblahblah
532blahblahblah
633blahblahblah
When I Run a query I should get only the following records
RowAEBCD
111blahblahblah
322blahblahblah
532blahblahblah
633blahblahblah
I want all such records where combination of A and E is unique.
View 1 Replies
View Related
Oct 9, 2006
I will try to explain my problem.
I have a composite key in my table....(i.e. 2 primary keys), when I do an "update" querry through a form and try to update just one of the primary keys, it adds an entirely new record to the table with the updated primary key field.
What I want to do is to be able to update just one of the pirmary keys in the same record (i.e. without adding a new record)
Is this possible?? How?
View 2 Replies
View Related
Jun 17, 2015
I have an events table and a junction table that has a composite key with two fields; those are the only fields on the junction table and are FKs in my events table.
I have a form that fills in events to my events table and also fills in values to my junction table. The control source is a query of the two tables along with a couple of other reference tables for metadata associations. The form works fine... the first time. My problem is that, after I have entered an event that has my unique composite key for the first time (thereby entering a new record to my junction table), once I go to enter a second event that uses the same key, access tries to enter the record again and throwing a "3164: field cannot be updated" error instead of referencing the already created composite key.
I have the form set the composite values to the selected values on form load. Normally, I would just set the primary key to the primary key values and access would follow my logic, but in this case it always tries to make a new record.
View 5 Replies
View Related
Oct 12, 2006
Hi everyone,
Hopefully someone can help me.
In Table 1 - I have a set of contracts that have a unique number. However, although the primary key is the contract number - these numbers can be duplicated as long as their start and end dates don't overlap. So my question is, how do I make the Primary key the contract number plus the start and end date? Is this possible?
I know I can select multiple fields for the primary key, but this has a problem...
Here are 3 examples of how Table 1 could be constructed and the Table outline...
Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details
Records
Contract Number/Contract Start/Contract End/Contract Details
001/01-Apr-07/31-May-07/Items Cost £1
001/01-Jun-07/31-Jul-07/Items Cost £2
001/01-May-07/30-Jun-07/Items Cost £1
Now, if I select Contract Number/Contract Start/Contract End as the primary fields, the above would not create any conflicts. However, the third record overlaps the first two records date barriers - and I don't want that to be possible.
Any new record's start date MUST be after any existing record with the same Contract Number's End Date. Also, any new record's end date MUST be before any existing record with the same Contract Number's Start Date.
In Table 2 - I have a list of orders that relate to Table 1. i.e. Each order has a contract it relates to. How do I make sure that in a query, the order number picks up the correct contract for the corresponding date.
For example...
Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details
Table 2
Field 1: Order Number
Field 2: Order Date
Field 3: Contract Number
My query would pick up Order Number, Order Date, Contract Number, Contract Details.
I think what I'm looking for is validations in the fields. But I'm not entirely sure how to implement those.
I hope I'm making sense...
Thanks again for any assistance,
Ross
View 6 Replies
View Related
Oct 17, 2014
My table key looks like this. Its a table that keeps a master record of conferences that occur during a calendar year
ConfYear (Date/Time) primary key
ConfNo (Byte) primary key
As you see its a composite key. Now each time a new record is created i want the ConfNo to autoincrement within its ConfYear. I used a byte as its not usually more than 4. Autonumber does not work as part of a composite key i found out.
2014 1
2014 2
2014 3 etc
And revert to 1 when ConfYear input changes to 2015
Is there a simple way to do this with say, a macro, or is the only way with VBA and DAO?
View 12 Replies
View Related
Feb 26, 2015
I am creating a database that tracks the selling of products amongst other things.
The user will enter in an order and may delay invoicing until the customer approves the quote - at which time the order is turned into an invoice.
In the transactions table the OrderID, ProductID and CustomerID constitute a composite key.
I want to be able to view the order and change it by either deleting or adding ordered items (obviously prior to invoicing) but because the ProductID is part of the composite key I cannot delete a line item.
Perhaps the solution is to remove ProductID from the index or is there a better way?
View 8 Replies
View Related
