Defining Primary Key From Two Fields
Mar 23, 2008
I have this table:
intSalesID (Number, Indexed: (Yes No Duplicates)
intCarID (Number, Indexed: (Yes No Duplicates)
intQuantitySales (Number, Indexed: (No)
This table is a cross table from the two tables tblCars and tblSales where the intSalesID and intCarID are Primary Keys. In this table I didn't set the P.K. I saw databases that have P.K. in tables like this deined from the two fields (intSalesID and intCarID, would be in my case). I wanted to ask what is the difference if I leave this table like this and if I set this two fields to form a P.K.? What is the difference in these twi cases? What do I get and what I don't, if I do either way? Thnx.
View Replies
ADVERTISEMENT
Mar 30, 2007
When I run this query, the fields in the new table contains 253 characters. How do I specify in the SQL command that the field Carg2 and Carg3 will contain 3 charachters ?
SELECT dbo_allesc.AccReport, Left([AccReport],2) AS Carg2, Left([AccReport],3) AS Carg3,.........................
INTO regTabell
Thank you, Tor
View 2 Replies
View Related
Nov 2, 2007
Hello Everyone,
I am in need of your vast array of knowledge and experience. I have been fighting with this Access report for a few days now, and I've finally decided to ask for your help.
I am running a report which provides a look at a set of 12 survey results. The 12 items are numerical in nature and are decimals with 2 decimal places. What I am trying to do is have Access automatically highlight the top 3 scores in one color, and the bottom 3 scores in another color.
So what I need is an expression that allows Access to discern the top 3 and bottom 3 from the series of 12 scores. in Excel you can identify the top score with this with the formula "Max(A1:A13)", but seeing as in Access we don't have the cell identifiers, this approach will not work.
I know that I will need to use conditional formatting in order to get the cells to highlight. I also see that there is "Max" and "Min" functions in Access as well, and I attempted to create and expression like this "=Max([field 1] AND [field 2] AND [field 3] AND, etc...)" with no luck. I also tried changing the "AND" to "OR", again without luck.
One thing that I should mention is that the report does contain a lot of other data, so I need to be able to apply whatever the best fix is, to only those 12 fields.
Unfortunately I am not accustomed to using code, and as a result I am unsure of how to input it properly. I am certainly open to code solutions, but I would also need a explanation of how to put it in properly.
The easiest solution for me, if possible, would be an expression that I can enter into the conditional formatting wizard to tell it to highlight the values.
If anyone has any ideas, I would appreciate it. I'll buy you a virtual beer :-)
View 5 Replies
View Related
Oct 12, 2005
At the moment i have an exisiting database that was not created by me. It consits of about 7 tables and a main table. Each table has a Primary Key that is a joining field. The Main form is built with subforms and when i enter a number in the primary key filed of the main table it enters that number in all the other primary keys in the other subforms.
i have now tried to create another subform to put into the main form but i cannot get this "link" between the primary fields to work.
can someone please help.
View 1 Replies
View Related
Oct 24, 2012
How do I add multiple fields in the same table with the same primary key?
For example I have an employee with the employee ID 1 named john. He has two degrees. One in Math the other in science. I need to have both of these incorporated into my table.
View 3 Replies
View Related
Apr 25, 2006
Hi,
I seem to be stuck on the simplest of tasks with both google and forum searches coming up with information that’s either too simple or too complex for my needs (not time wasted though because I’m learning all kinds of related stuff).
How do I join my tables with one-to-one relationships?
[edit] Doh, got this one now - by indexing without duplicates then saying all in one have to link to one in another:D
How do I define both fields in a linking table as composite or two field primary key?
View 11 Replies
View Related
Aug 4, 2005
Hi all - I have a suite of reports which provide customer details. They all require the user to input the name of the customer.
The way it works is that I have a query which returns everything for the customer.
Then I have other queries based on the first query which bring back the specific information I want regarding the customer.
This works fine when the customer's name is entered using [Enter Customer Name] in the criteria of the relavant field in the first query.
However, because a user might want to run several different reports on the same customer it becomes a chore for them ot repeatedly have to enter the customer name.
My solution is to have an unbound field in the reports menu for the customer name and to use this as the criteria in the query, so it now reads [Forms]![FrmMenu]![Text42].
The first query runs fine, but the queries based on it are suddenly not returning any records.
There is undoubtedly a simple explanation for this but as I'm quite dim, I can't see it. Could somebody enlighten me and maybe even suggest a solution?
View 4 Replies
View Related
Jun 6, 2006
Hello all,
I was wondering what should I define a chechbox as if I want to make it a variable, so that when it is checked (= -1), I can check for the -1 and loop somewhere.
Thanks
View 1 Replies
View Related
Jun 6, 2006
Hello all,
I was wondering what should I define a chechbox as if I want to make it a variable, so that when it is checked (= -1), I can check for the -1 and loop somewhere.
Thanks
View 1 Replies
View Related
Jun 6, 2006
Hello all,
I was wondering what should I define a chechbox as if I want to make it a variable, so that when it is checked (= -1), I can check for the -1 and loop somewhere.
Thanks
View 5 Replies
View Related
Mar 7, 2005
Hi,
I am trying to auto-import data w/ a single-click from several Excel sheets. A sample line:
DoCmd.TransferSpreadsheet acImport, , "tblTS1", filename, True, Product!A1:H100
where
tblTS1 - tbl where I'm putting the data
filename - c: est.xls
Product!A1:H100 - range of data I want in sheet Product
I am having problems w/ the "range" portion of Transferspreadsheet (I know it's this portion, because it works when I pre-name the range in Excel). I have tried all sorts of variations on the syntax:
Product!A1:H100
"Product!A1:H100" or 'Product!A1:H100'
"Product" & ! & "A1" & : "H100"
"'Product'" & ! & "A1" & : "H100"
Product & "!" & A1 & ":" H100
I get the error msg:
The MS Jet database engine could not find the object '$:'. Make sure the object exists and that you spell its name and the path name correctly." or "Syntax error"
I wanted to mistake-proof the data transfer, so I did not want to:
- predefine the range in Excel (under InsertNameDefine)
- have the user enter values in text boxes
Any help w/ the syntax would be greatly appreciated!
Cheers,
dvs :confused:
View 1 Replies
View Related
Nov 28, 2005
I have a database about when computers have been installed
I have a form
It has a drop down list with Januray through to December
I want the user to select a month and a year and then click the command button and it will run a query displaying all the computers installed in the month of that year
any ideas people?
View 4 Replies
View Related
Mar 17, 2015
how shall i define my new Date or numbers.Now "yyyyXX" this is my date "y" is year 4 digits and "XX" is my problem 2 digits. I ll give an example,
(XX = 01....36) XX can be max 36 and min 01 of course. f. Exp. 201436 and next number shall be 201501.
the biggest problem is different of these number with basic math 201501-201436= 65 but for me it should be 01... The Question is; How can define these ??
View 8 Replies
View Related
Jun 6, 2006
We have a db of salmon survey data where catch data is recorded by date, site, etc.
Historically, we have used ‘sampling periods’ to group our results into blocks of time that can be compared from year to year, and within year.
Now, having frequented this forum I’ve read that we shouldn’t store information in tables that can be calculated from fields and I want to try to follow that advice in this instance but am having trouble working this out.
Here’s how we define a sampling period.
1.The start date of the first sampling period in a sampling year is the last Monday in the most recent November.
2.Each sampling period is 14 days long.
What I want to do is have a query use the date of a set to calculate what sampling period the set belongs to. I have an idea that I could use a datediff function to calculate this if I can figure out the ‘last Monday in the previous November’ part. I’ve searched this forum and googled but am having trouble finding anything that I can adapt to this as a query expression.
Anyone care to help a fish bio out? Much obliged :)
View 5 Replies
View Related
Mar 30, 2014
I have to create a control on a form that will be able to change to one of ten colors, some of them are subtle shades. I will be doing this from vba on the control. I know how to do all of this except defining the colors.
I keep seeing the 3 part RGB(xx, xx, xx) etc. but I can't find the values for it that will give me the exact colors that I need!
I also saw a possibility of using a the hex equivalent for the color but I couldn't make that work?
View 2 Replies
View Related
Sep 15, 2006
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.
Any ideas much appreciated.
View 5 Replies
View Related
Oct 31, 2006
Hey guys i would really appricate some help
I have made severa databses before but im trying to make this one perfect and im curious, when defining tables should you include atributes about an entity that are static?
Its kind of hard to explain what i mean but for example in a shoe shop a staff member gets commision on what they sell (ie 5%) so when producing the table should a coloum be reserved for commision or should this be left out as it will be calculated later on in a querie and if so should i state this in the design section of my database?
any opinions would be greatly received as i have been reading books on sql and ERD's for days and havent been able to obtain any answers
cheers guys
mike
View 1 Replies
View Related
Nov 12, 2007
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!
View 5 Replies
View Related
Feb 27, 2005
Hello
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 :)
Who has the info???
View 3 Replies
View Related
Nov 4, 2005
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?
View 2 Replies
View Related
May 30, 2006
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 ?
Thank You
View 6 Replies
View Related
Oct 15, 2007
Can a PK be a calculated field?
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).
View 11 Replies
View Related
Sep 10, 2006
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...
View 1 Replies
View Related
Nov 21, 2005
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 appreciate any kind of support
Thank you
Joe
View 13 Replies
View Related
Nov 28, 2005
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?
View 2 Replies
View Related
Jan 21, 2006
Hi all,
Ive written a program in .net and am currently using an access db as the backend.
In the db I had 200 test records which i deleted to get the db ready for deployment. When i add a new record now the primary key on my "booking" table carries on from the old test number so 201.
Can I resest this so it starts back at #1 as once the db gets updated, the program i wrote then takes the data and updates the accounting system and uses the primary key as the invoice number and so I would like to start at 1.
Thanks in advance
Scott
View 5 Replies
View Related
