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
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 am creating a database for the local arts centre here is my question
we have several venues each with a limited capacity i am trying to create a booking system so when a venue is selected it checks for capacity at the venue. This is a one off event with one event being held at each venue so it does not need to take into acount dates just free capacity at a venue. Help
Hey, I am getting a primary key violation when I try to run an update query.
My primary key is a combination of two fields, ScheduleID and SchedulePage. That way for each schedule I can only have one Page 1, one Page 2, etc.
When I want to insert a page (say a new Page 2), I need to update the table so that Page 2 becomes Page 3, Page 3 becomes Page 4, and so on.
The problem is, since it starts at the bottom, when I tell it to increase the page number by one, it's conflicting with the primary key of the next record.
Any ideas? BTW, if the solution has to do with sorting, I need to make sure it's something that always defaults back to the correct sort, since users may be able to change the sort and accidentally save it. Plus, I'll need to do the same thing in reverse (delete a page).
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.
I’m trying to do something which should be very simple, I’ve added a new field to a table, but for some reason it won’t update the field when using the form. It’s quite a complex form and my Access abilities are limited so I would rather not create the form from scratch.
I’m trying to create a query that displays only entry’s which contain my new field. However since I have the above problem it doesn’t seem possible. One way I see around this would be to create a rule in my query where only records were shown when the fields “price” divided by “number_of_players” = 12.5 Can someone please tell me how to write this validation into a query?
I'm making a database with 50 fields for the shops of my company. Among these fields, the data of 20 fields must be submitted to us (head office). The data of the remaining 30 fields (like customer address) is just for generating receipts.
At the begining, I set the validation rule of that 20 fields to be "Is not null" in the Form. If the required data (20 fields) is not entered, messages will appear and the record cannot be saved. Then I use the Query function to extract that 20 fields. However, our staff said they might not be able to obtain the information of the 20 fields in the first time. The customers may send them the info later on. So, it not good for me to set any validation rule in the Form.
I would like to ask how I can ensure they submit the data of the 20 fields if I release all the restrictions in the Form? Could I set any validation in the Query so that error message will appear if not all the required fields are filled?
Hi - I have an inventory management database allowing stock to be moved between various subinventories. The user choose a transaction type and then details which goods to move. However at the moment they could enter a Quantity that is greater than that available in that subinventory.
So I have a query that gives me: Part# Subinventory OnHandQty
The data entry form has a control source directly to the Inventory Table. I want to compare a text field [Qty] on a new inventory transaction (the person already having selected Part# / SubInventory from combo boxes) to the On Hand Qty.
I have tried Expression Builder in the Field Validation Property and I have tried some sql code on the AfterUpdate of the text box. I'm just not getting it.
Anyone... Please can you tell me how I run a make table query that makes one of the fields a primary key. Can it be done in the query or do I have to set it as part of a macro that runs the query. If so How do I achieve this.
I am running a make table query from a link table so i can use it with infopath. I run the make table query every time I update the spreadsheet that the link table runs off and it makes a proper table with it for infopath to run off (because I can't get infopath to run off a link table, No primary key means it won't submit.) When I run the macro that runs the query and creates the table, I need it to make oone of the fields a primary key.
I am trying to create a new Table using a MakeTable Query
using the following sql:
Code:SELECT qCPPlannedStopsOnTargetTotals.WeekNumber, qCPPlannedStopsOnTargetTotals.Line, qCPPlannedStopsOnTargetTotals.Description AS Above, qCPPlannedStopsOnTargetTotals.[%] INTO mkCPPlannedStopsAboveFROM qCPPlannedStopsOnTargetTotalsWHERE (((qCPPlannedStopsOnTargetTotals.Description)="Above"))ORDER BY qCPPlannedStopsOnTargetTotals.WeekNumber DESC;
However i want the new table to have a Primary Key, (Week Number) can i set this as the table is made?
just woundering wheather you can enter a rule in textbox properties to check the data entered is equal to any of the data in a field in a select table.
Is this possible? If so can you give me an example how?
the table i will be check is "deliveries" and the field will be "driver code no"
How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
I have a make table query that gets its info from another table that is updated weekly. I would like that when we run the Query Type (Make Table), we would like to add a primary key to a field that exists already starting at 1 every time the query is run.
I have a textbox on a subform to allow a delivery qty to be entered (to allow me to cater for part deliveries). I have a query which totals up the values of this field (so that I have a total of all the deliveries for this record) and compares that value against another field (to check that the total deliveries I have entered do not total more than the ordered qty). I am using this query within the "Validation Rule" of the textbox. My problem is that when I enter the values for the first time this works fine because the figures are not yet part of the query and therefore the check works fine, however if I try to modify a figure I've already entered it triggers the validation each time because it tries to add the new value to the query total.
I'm new to access so I thought this would be easier than it is. I have a list of cities in a simple table . I want use this list (or a query of it) to create a validation rule for a field in a second table "City". How, either with VBA or some other method do I use the Cities database to validate entry. These tables are both in the same project.
I'm trying to create an Append Query in Microsoft Access to add some data from one table into another table. I checked all of the properties and fields to make sure there are no validations. I have allowed zero length, and have tested the validation rules under the tools section of the design tab on both of the tables. When I run the Append Query, I still get an error that says 0 records added due to validation rule violations.
I have created a very simple Access database with a CompaniesTbl, ContactsTbl and CallsTbl. The database is used to record telesales contact with customers. The tables are set up like this:
The CompaniesTbl has an AutoNumber Primary Key field called CompanyID.
The ContactsTbl has an AutoNumber Primary Key field called ContactID and a foreign key called CompanyID.
The CallsTbl has an AutoNumber Primary Key field called CallID and foreign key called ContactID.
The relationships are set up in the relationships window and referential integrity imposed.
Twice now, while editing a contact record, an error has occured. Task Manager has had to be used to get out of it. The error is either a 'run-time error with a message that does not mean anything' or it says 'unrecognized database format' (!?).
If you open the database again the ContactID field in the Contacts Table is no longer a Primary Key field. If you look in the relationships window - the relationship between the Companies and Contacts tables no longer exists.
Hi, May I know how do I go about getting the last value of the primary key that I get inserted? I need this PK to link the parent and child tables together. Thanks for any valuable help!
I have a problem with a primary key. A table called relaties has a column relatienumber with date type: number, which is also the primary key. When a button is pressed, a function searches info from the table relaties. The problem is the following: I figured out that when i remove the primary key constraint from the column relatienumber and save the table. the function doesn't work anymore, also when i remake the primary key for this column. How is this possible, because i changed nothing futher.
This is important because i want to change to access db to mssql db and in mssql the function also doesn't work.
It has something to do with this primary key constraint, i think :)
Is it important that primary key would be a number for performance purposes? How do I set 2 different fields as one primary key? How do I set 2 different fields as two separate primary keys?
when defining tables, i normally set an autonumber as a primary key. However, i am working on a particular table, and i would like that no record will have identical values, in three particular fields
aaa bbb 123 aaa ccc 234 ddd fff 555 aaa bbb 123 <- i want to prevent this since a record with those three values already exists
Shall i set these three fields together as a primary key, or can i leave the autonumber as primary key and prevent such duplication in any manner ?
I want to be able create a PK that consists of three strings being added together. The first two would be collected from cascading combo boxes. The third field is a simple text field.
I would then run DLookup to make sure the final string is unique.
I have reasons for not using an autonumber field (there are compatibility issues with existing tables from a much older dbase database).
I have a database that I got up and running but one problem. I have as the primary key a social security number, but I can't seem to be able to add additional information to the same person. All I can seem to do is just replace or edit the one record. Is there a way that I can have more then one order for an individual instead of taking the primary key off of social security numbers?
Do I need a seperate form to enter information because I am currently workin with one form where I can enter and edit the information but no duplicates so lets say if my social was 099-999-9999 I can't add an additional order it seem unl.ess I make a whole new record and make a dummy number...
I have a tblState, every state can have multiple Counties, and every County can have multiple Municipalities This should be a One to Many to Many, right????
However, several states might have the same County name
In order to resolve this, should I add 2 primary Keys in the tblCounty????
If so, I already tried and couldn’t configure it.
I have attached a sample DB You can open the relationship view for a better explanation
I was wondering if anyone would be able to help me? I have two tables which can be linked together as they both contain a field called Company Name. However, in one of the tables I want Company Name to become my primary key but this field contains duplicates. How can Isolve this so that I can still link my two tables together?
