I am setting up an access frontend to an excel worksheet. I am creating linked tables using the excel input fields. I find when I have created the tables I can't create a primary key as access won't allow modifications to the linked table. Has anyone any suggestions as to how I can set a primary key on these tables. Thankyou
View RepliesHello,
I want to have a table linked with an excel doc (which is easy to do with the wizard) but I want this linked table to have a primary key (which the wizard does not allow) because this table will be in a one-to-many relationship
How else can I do ?
thanks
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
Hi there,
I am trying to model my database to create relationships between different tables in my database. During the process of doing so I ended up having one primary key in table A linked with to foreign keys in two different tables ( table B & table C) and both of those two table are junction tables to break out many to many between many other tables
Now for some reason!!! I feel that there is something wrong with my logic mapping and modeling of the relationships between tables due the fact of having one primary key linked to two foreign keys in two different tables :(
Is my intuition is right? should such case be considered as indicative of wrong modeling of relationships between tables in a single database
And if so what is the disadvantages of that link (talking down the road) when the database if fully populated? :confused:
By the way I am new member and new to database, so please take it easy on me :o
Many thanks
Currently we have a web based order taking/inventory system. Every night they export a .db to an FTP server and every morning I download it (I automated this part, it replaces the older file). I link to this database (using SQLlite3 to ODBC driver) from an Access database for specifically this purpose.
When I originally created it I had to go through every table and create a unique identifier. Unfortunately I chose poorly in some cases. I can't figure out how to get those primary keys changed to the correct ones. I've tried recreating the ODBC connection, tried a new database and new ODBC connection, tried killing the connection waiting a day (for the new .db to download) and recreating the connection and trying it in a new database.I know access doesn't allow you the change the primary key in a linked table.
I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in
(Dang that still sounds evil and definately NOT understandable even after edit... so)
Here's some basic info
Tables
EstimatesandParts - Table
EstimatesandParts_ID : Autonumber
Estimate_ID : Number
Part_ID : Number
Parts - Table
Part_ID : Autonumber
PartNumber : Text (not a number due to some part#s have letters in them)
PartName : Text
Unit Price : Currency
Description : Text
Estimates - Table
Estimate_ID : Autonumber
InvoiceNumber : Text (again can have letters in it)
EstimateDate : Date/Time
EstimateTime : Date/Time
Employee_ID : Number
Customer_ID : Number
ProblemDescription : Memo
Customers - Table
Customer_ID : Autonumber
FirstName : Text
LastName : Text
CompanyName : Text
Address : Text
City : Text
Province_State : Text
Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)
you can see the link table in the EstimatesandParts Table
Now I want to use that link to populate a subform in the F_Estimates form
Forms
SF_Customers - SubForm
(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)
FirstName
LastName
CompanyName
Address
City
Province_State
Postal_ZIPCode
SF_Parts - SubForm
Default View -Continuous Forms
(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)
Part_ID : Combo Box
Control Source - Part_ID
Row Source Type - Table/Query
Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;
(Pulls info from the table Parts for input into a list of parts to be used on that project)
PartName : Text Box
UnitPrice : Text Box
(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )
(have tried a couple things to complete this task)
Me.txtPartName = Me.Part_ID.Column(2)
Me.txtUnitPrice = Me.Part_ID.Column(3)
(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)
(tried to make control source for the txtPartName to)
=Forms!Parts!Partname
(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)
F_Estimates - Form
Estimate_ID
InvioceNumber
EstimateDate
EstimateTime
ProblemDescription
(all basic Text Boxes)
Employee_ID
Customer_ID
(Combo Boxes Select Customer and Employee from list of present ones of each)
SF_Customers
SF_Parts
(Both SubForms on the main form)
Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...
Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
Thanks in advance for ANY and ALL help that I get from here
Hi,
I have an Access 2000 database with some linked tables. When upsizing the database I selected 'Save password and user ID with attached tables'.
Therefore in the Linked Table manager after each table in brackets I have the name of the database to which the tables are linked.
I am now wondering if I want to link the tables to a different database how do I change the database name, which is in brackets after the table name.
If I select a table and select 'prompt for a new location each time' I am getting prompted to select a different DSN. At the moment I don't have a DSN and I don't want to have to set one up.
I imagine that the database name and the user id and password I entered in the upsizing wizard are stored in some configuration box but I don't know where I can access this.
Can anyone point me in the right direction??
Thanks
ok , i have a primary table that have a field of "job", then i create another table that have a field of "job" too , then how can i do this task?
Whatever i type a data into the primary table("Job" field) i want it to duplicate the data into the another table("Job" field) . is it possible to do this? can anyone guild me to do ?
Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns? What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?When I try creating the second relationship, access automatically creates a new Faculty table for the relationship!
View 6 Replies View RelatedI am accessing a SQL database using Access but its not letting me view the table in design so I can set a primary or letting me change the table names.
I am able to do this from my home PC but not at work. Is there something in options or ODBC settings on the work the PC that I need to change.
Any Help will be greatly apprectiated.
JC
I have searched Key in the forrum and did not find quite my situation:
I would like to have a table that has a Primary Key that is a combo of 2 fields,
Example: Don't Allow a record to be created for the Same [Name] & [Date]
In the above the primary key would be the [Name] + [Date] but primary key only allows one field for key.
I'm still new in ADP development and need some advices and helps from you who has already expert on it. Fyi, I used Ms. Access 2007 and SQL Express 2005. I found a problem when working with my project. Here is the problems :
I'm using form wizard to create master/detail form but why I can't insert record in the detail section (the navigation button also became disable) ? The detail table doesn't have primary key only have foreign key that related to the master table. Should a table must have a primary key if we use ADP ? As attached is my relationship diagram.
Many thanks in advance
I have about 500 tables that I'm upsizing to SQL Server, many do not have primary keys.
I can cycle through the tables container with VBA identifying each table and each table's respective fields.
How do I identify the primary key(s) of each table with VBA?
Thank you in advance for you assistance.
HI
I did some BAD planning before I started creating my database and I set a text field ("field1") of a table ("table1") as primary key, then I linked this field to a field in another table ("table2", one-to-many relationship). Now I wan't to change some values in table1 in "field1" defined as primary, but I can't (Access refuses to do so...).
Because I have a lot of data entered allready I would like to know if there is any way for me to change values in "table1"/"field1" and that all the linked fields in other tables ("table2") would change correspondingly??????
I hope my question is clear enough to understand.
thanx in advance
Hello I have another prob. I need to be able to take a new ID from the topics table and insert it into the messages table after a form has been posted. I haven't got a clue on how to do this.
Its for a message board where a new topic is inserted. Two tables need to have records inserted, the topics table and messages table. The Topic_ID from the topics table is an autonumber and needs to go into the TopicID field in the messages table. Here's my insert query:
sqlString = "Insert into Topics (Topic, BSection, Username, MesDate) values ('" &_
Topic & "', '" & BSection & "', '" & Username & "', '" & MesDate & "')"
Con.execute(sqlString)
sqlString2 = "Insert into Messages (Username, Member_ID, Topic, Message, MesDate, BSection) values ('" &_
Username & "', '" & UserID & "', '" & Topic & "', '" & Message & "', '" & MesDate & "', '" & BSection & "')"
Con.execute(sqlString2)
I've tried using the identifier command but that didn't work so I haven't got a clue on what to do. Can anyone help?
thanks
Grant
Hello,
I have attached a screenshot of the relationships in (a section of) a database I am working on. Would anyone please be willing to view it and offer any suggestions as to whether is fully normalized or not? Here is the description:
An account [table, Account] consists of some money. That money is split-up and invested into several different places.
The funds that are available as choices are in Fund; the funds that are chosen are in InvestmentInstance. Different, unaffiliated accounts can invest in the same fund. I would like to keep track of when an account begins to invest in a fund and when it stops investing there (“InceptionDate” and “CloseDate”).
The generic, account-independent performance of a fund is kept in Price, where the values come from Yahoo! Finance or something like that.
Similar to Price, MarketValue stores the dollar-amount of an investment, specific to an account.
I think I have most of it set OK, but my main concern is about the relationship between InvestmentInstance and MarketValue. As the picture shows, I use InvestmentInstance’s “Id”* as the “one” in the one-to-many relationship, but “Id” is not a primary key. To get it to work, it was necessary to set the Index option to, “Yes (No Duplicates),” so that it was a unique index. Is this a bad practice? Should I just include “Id” as part of the compound primary key? I didn’t think making it part of the compound key was a good idea because then it would defeat the purpose of having “FundId” and “AccountId” control the uniqueness of records. Am I missing something important?
In other words, how can I correctly create a one-to-many relationship that begins in a juntion table (InvestmentInstance) and connects to a new table that was not involved in the normalized many-to-many relationship (MarketValue)?
Thank you!
* I know many like to include the table name in the ID, like, “InvestmentInstanceId,” and it is only my preference to keep it just as “Id” for a local key for simplicity, but add the table name when it becomes a foreign key. I feel “Id” is good enough for my purposes and it’s table location is usually clear from the context.
My database tracks all of the staff development sessions provided for the past ten years, thus we have thousands of records. Our school district wants all departments to move away from using an employee's Social Security number to a number assigned by the district. Each employee has been assigned a unique six digit number.
In my staff development database, the employees data (Social Security number, campus, job description, etc.) are in a table, "EmployeesTbl", with the primary key being the Social Security Number. Classes data are in a table, "ClassesTbl" with some of the fields being ClassNum (an autonumber), class name, class description, etc., with the ClassNum being the primary key
in this table.
A third table, "AttendTbl", links the two and shows each class (staff development session) each employee has taken. This table has a a foreign key, "SocNum", that links it to the EmployeesTbl, and another foreign key, "ClassNum", that links it to the ClassesTbl.
I have added the EmpNum field to the EmployeesTbl, and all of the the district assigned employees' numbers have been entered. I have also added this field to the AttendTbl. Is there a means of using a query to fill in the employees number in this table. Is so, I'll then change the primary key in the EmployeesTbl to be the employee number and the foreign key in the AttendTbl to be the same. If not, we face the task of having to key in 18,000 records!
Thanks for any help,
R. Sanders
I need to build the following query:
I have 2 tables:
tblBusiness
tblInspection
tblShops have an ID, an adress, bussines name, owner name, etc
every Shop can be inspected many times. A inspection can be futile if the store is closed. If the store is open, the inspection is made, and as result with can have “with irregularities” “without irregularities”. Two or more inspections can be made the same day (if the n – 1 time the store was close).
So, here is the basic select:
SELECT tblBusiness.idBusiness, tblBusiness.BussinessName, tblInspection.InspectionDate, tblnspeccion.InspeccionTime, tblInspection.Open, tblInspection.Irregularity
FROM tblBusiness INNER JOIN tblInspection ON tblBusiness.idBusiness = tblInspection.idBusiness;
What I need is to get the LAST inspection where the bussines was open and with irregularies. One inspection per Business, but of all business. Ah, and if there ir a newer inspection but the business was close, it doesn´t count.
I tried “Group by MAX” with InspectionDate, but it just didn´t work. For example, it showed me two inspection of the same business, not “the last” of that bussiness (it was easy to filter the Open field and the Irregularity field).
I suspect that what I need to solve this is to use two or more SQL stament, two in one., something like that. But I´m in a hurry and I just can seem to get it right.
I hope you can help me with this. Thanks for reading anyway.
P.S.: the fact that there can be more than one inspection per day can be a bitch =(
but there is a "InspectionTime" that can save that". Mmm the problem is that one inspection can be made 1 july around 20:00, but the seccond on 2 july 01:00. But we store the two inspections in 1 july... so... that mens problem.
But that issue is not so important as the previous problem up there (well, in order to solve the seccond I need to solve the firts...)
PS2: I don´t know about the title...
Hi,
Anyone has the idea of creating primary key in a table using make table query?
Thanks.:confused:
In a many-to-many relation, one create joint table and use two columns of both ID's in a Composite way. This what the text books recommend.I had a Form/subform to view the relation between the two table. I tried to add to the subform a new relation Access did not allow me to do it. I can go to the joint table in datasheet view and ADD to my heart content observing the unique condition.
If I change the design of the joint table to auto as PK and the two ID's with no PK in either, I can update from the subform .WHY the Composite Keys do not allow me to update the joint table by adding new records in the subform ???
I have a table that has a primary key that does an autonumber in increments. Then I made some queries that will append records by date and then also another query that will delete what was appended. Then I created a reverse sequence of this process in case I change my mind and would like to put back the records I archived, however, when I do this, the primary key is now thrown off and will still revert to the last primary key that was appended before the archive ever took place. In other words it still thinks that the records I appended before I did the reverse, are still there. So now it will not let me save the record because it is a duplicate.
Is there a way to set the autonumber for the primary key to where I want it to begin?
I have an old table from a database with 2000+ rows deleted and it had no primary key (but it had a number column).
I am splitting another old table which relies on the number column of that old table but I am importing it into a new table with an autonumber primary key which the split table will rely on.
Thing is I need the number and the autonumber to match up accross all records
1 has to be 1
2 has to be 2 and so on
is there a way to "fill" in rows with the missing numbers? i.e. if I have 1,2,3 and 2 is missing the method adds a row "2" to the table.
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.
when i import i have records deleted due to key violation, how can i get these deleted records to an error table so i know that the right record was deleted?
View 8 Replies View RelatedI have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP