The idea is that ALL ACCOUNTS have the information in table 1, and specific SETS of accounts have the ADDITIONAL information in each supplemental table (at the same group level). A one-to-many relationship would not be correct here, and I've tried to use a 1-to-1 relationship.
PROBLEM: When I make a new item in tblAccounts or (for example) tblLoanAccountsSupplemental, I want it to autocreate the KEY and make it the same for both tables.
My intuition may be wrong with the 1-to-1 relationship. What should I do? :(
I have created a many-to-many relationship but am now having trouble figuring out how to add new data.
I have a table PEOPLE: ID First Name Last Name 1 Participant A 2 Participant B 3 Participant C
And I have a table BEHAVIORS: ID Behavior 1 Yelling 2 Grabbing 3 Smearing feces
And I linked these together in a many-to-many relationship in a table PEOPLE_BEHAVIORS: ID Participant Behavior 1 A Yelling 2 B Yelling 3 C Yelling 4 A Grabbing 5 B Grabbing 6 A Smearing feces 7 C Smearing feces
All that's good to go. But now I need a table so I can count how often these behaviors occur every day. I need to link PEOPLE_BEHAVIORS to a new data table that will count how often I see the behaviors linked to the people every day. So my table should look like:
ID Participant Behavior Date Frequency
But I should only document the frequency of the behaviors linked to that participant (so for Participant A, I should have a space for yelling, grabbing, and smearing feces whereas for Participant B, I,d only have a space for yelling and grabbing). Is it possible to create a table that will basically partially create records for me? So if I open the table to add data today, I'd see the following and fill in the frequency blanks?
ID Participant Behavior Date Frequency 1 A Yelling 6/16/14 2 B Yelling 6/16/14 3 C Yelling 6/16/14 4 A Grabbing 6/16/14 5 B Grabbing 6/16/14 6 A Smearing feces 6/16/14 7 C Smearing feces 6/16/14
I am trying to create a 2-table database where we can enter our survey data. I want them to be linked by "head"# so that the "Temp" "Date" "island" and "site" are autofilled into the "time dep"table from the data I entered in the first "perm"table.
I would also like if the lookups were filtered so that, for example, when I choose "Cat Island" my only two options are "fishbowl" and "Cuda" then from there depending on which of those I choose, The "head"# can be chosen from a dropdown as well. There are about 22 coral-heads per site, and two sites per island. I don't want to have a giant dropdown of 80+"head"#s, but at the same time, if we are typing them in, it increases chance of error. limiting the options of head number to be site specific would be ideal.
I feel like I may be going about this all wrong, and need a form? But I cannot figure out how to make the form data fill the datasheets that I have created.
I have about 600,000 records in an excel sheet and tried using the table analyzer but I run out of memory. Instead i have broken up my giant table into a few other tables with their own primary keys and have populated from a maketable query. I related the tables together in a 1 to many and many to many table and one of the subforms is working great. The problem i am having now is with the many to many table (WorkedJunctionTbl). I don't want to have to manually input thousands of lines of data, so is there a way i can populate the data from the original data correctly using the new autonumber primary keys?I can't seem to get an append query to work correctly.
First of all, I want to say Thank you to everyone in this forum I have been reading just about every question in each Topic and I have pick up a lot more than I tough I knew. [Thank you]! I not sure how to ask this question or better yet write it. Here is my Situation I work for the NAVY as an Enlisted Personnel station in (New Orleans, LA) I have created a few databases for my workcenter and were simple enough that I managed. Now, I been task to help create a Call Center DataBase to keep track of Phone Calls and Issues the Agents(Employer received Daily) As well run other reports.
I am Including a copy of what I done so far. Can someone advise me about my Database Design? Does it makes sense? Are the Relationship seen good? my frmCustomer is based on query (qSupport) I would like to know if the form and the SubForm are set up ok where I can have the Agents input data. And one more thing on the frmMenu I have a few Text Boxes where I would like to display the total Amouth of Phone Calls received - as well the ones pending and Close? Any Criticism or Suggestion are more than welcome.
I hope my Question / Request makes sense. Thank you ....
I'm currently using Access 2010 and I'm working on a database project. My question is related to table relationships. Within that project I do have a table that is related to other three tables where that table is the parent. The problem here is that once that table is updated or have a new value it never cascades it down to the other tables.
1- How to have multi relationship to the same field on the same table from different tables? 2- how to cascade the updates to the related fields? 3- Is there any way to force the data update to other tables?
Each client record is unique. A client can have 'several' routing records.
Key field in Client is 'Client ID'.
Routing table has foreign key of: Client IDFK
I created a relationship of one to many from Client table 'Client ID' to Routing table 'Client IDFK'.
I created a form for the Client table and works.
Where I am having issue is: Client data is not populating into the following Routing form.
* I want a 'Routing form' that you can lookup client info and place it into that form. * The bottom of the form will be all the routing table fields. The new routing info will be entered into it.
My client needs that form printed for the driver. Client will have many routing forms(records) but only one client record. The driver will have one completed form for each time he picks up client.
I created a table in MS Access using a Lookup & Relationship data-type. This means that my record cells call upon a particular table for values. It creates a LIST of values for that cell.When I query a particular value in this table I get every possible combination of the value. My query and code are below:
OR,
SELECT MainT.Content.Value, MainT.Source, MainT.[Entities Impacted].Value, MainT.Update, MainT.[Divisions Impacted].Value, MainT.Announced, MainT.Effective, MainT.Stakeholders.Value, MainT.[Ref#ID], MainT.[Issuing Agencies].Value FROM MainT WHERE (((MainT.Content.Value) Like "*" & [Forms]![SearchF]![ContentCB] & "*" Or (MainT.Content.Value) Is Null)
[Code] .....
This means that if I query STATES: "California", I'll get back every possible combination that exists with the name California in that particular field.The issue with this is that I'll get MULTIPLE primary key values rather than just 1. So, if one record, under STATES has California in it, but the other fields in that record have the Lookup & Relationship data-type, then every possible combination of that record will query instead of the multiple field-values form that you'll see in the table I am querying.
notice that my primary key (REF#ID) is repeated numerous times! This obviously causes problems with generating records and forms concerning information for 1 particular primary key. Is there a way around this problem other than specifying search criteria down as much as possible?! Perhaps there is some SQL code.
Hi All, I am new to access and this forum, so hello all.
I am trying to build a database to keep records of my bird sightings. As you might have surmised; I am a birder.
I have come to a holt on creating a complex relationship between the Bird Entry From and the table that contains a list of British birds.
In Bird Entry From two of the fields are for entry of the bird name, one field being the Common Name and the other being the Latin Name. They are both drop down lists that contain all the birds from the table, this being done by import data. The table that holds the bird list has two fields One is called Common Name and the other is called Latin Name, this being the same as the filed names in question in the Bird Entry From
This is where I am coming unstuck. What I would like to happen is that when I enter a bird in the Common Name field (either by typing until the bird is auto entered from the drop down or using the drop down to find the bird) it automatically enters the Latin name in the Latin Name field and visa-versa, but I have become very frustrated with trying to do this.
Am I wasting my time? Or is it possible? And if so would anyone be kind enough to tell me how?
Video (Vcode, VTitle, Date Made, Director, Genre) Copy (CopyNum, Vcode, Due Date) Loan (LoanMemNum, CopyNum, Ldate, In, Out) Member (MemNum, MName, MAddress) Reservation (VCode, resMemNum, Resdate, CollectDate. Collected Not Collected)
I've three tables in my database, tblInfo, tblSavedInfo & tblCountry. for tblInfo I have this fields - Ref: Cost: RefCountry: for tblSavedInfo I have this fields - Ref: Cost: Country: for tblcountry I have this fields - RefCountry: Country:
My problem is when I save from tblinfo to tblSavedInfo I want it from RefCountry on tblInfo check wich country is on tblCountry and save on tblSavedRef the country instead the refCountry.
As tblInfo has about 200.000 rows I need something to make it quick. Is there anyone that can help me please.
Any advice? I've tried some things and no success with linkage
Rooms Table RoomID Room No Type Desc
Project Table Project ID Project Desc Faculty Assignment Project Grant No Sponsor Project End Date
Student Table Student ID Students
Many rooms with many projects, many of those projects in many of those room, with many students working on those projects. Many rooms, many projects, many students. Argh... I've already tried two junctions between rooms and projects // and projects and rooms
As you all know Im not a expert on databases but work in a volunteer basis in a small community area where we have no money for people to do anything for us. Im proud because I did the other database and its up and working but this one has got me beat. Im setting up one for the volunteers, which has computer knowledge, any basic training they need, when they are available and what tasks have been set for them. I need to have a one form which has just their personal details but to be able to put in data on the other forms and it will link back to the volunteers name. Ive attached what Ive done and Im sure your all going to laugh - but any help would be appreciated
Each student will be assigned to one, and only one, rack. There will be 3 students to one room. One student can only have one room but a room can have many students... I store studentID in the rackOne, Two and Three fields. My problem is when I try and querry for the information so as to display actual names rather than ID's. I'm not sure If my querry is the problem or if I've set the tables up incorrectly. Any insight is much appreciated.
I have a one to many relationship between the movie and date between the attendance and movie data table. I have tried linking the name to the usher fields but I"m not getting the result I want. I want to be able to open up the volunteer table and have it show me the movies they have worked on. The movie data table will list who worked it, but the volunteer tables are not.
at one point in my database I have two tables and for everyone record in one table I may have either 1 or 0 records in the other. How do I express this in access design? Since it's an optional 1 to 1 relationship I guess?
I was wondering if anyone could help me with a query. Basically I am doing (or trying to do) a small database to track childrens progress in my mums primary school. I'm doing this for free as the budget is rubbish for this type of thing and i dont have enough knowledge to ask for payment anyway! I want to get it right from the start and I think if I get the relationships right initially then I can complete the rest of it on my own. I did this at university (normalisation etc) but ive not used it since i left in 2002 and so have practically forgotten everything i ever did.
Basically, the child comes into the school and is predicted a level in maths, reading and writing. Then in the october, feb and july of each of the 2 years they are there, they are given actual assessment levels. They are also given a prediction level at the beginning of year 2.
So far I have one table with student no, surname, first name, gender, ethnicity, year group and SEN(special educational needs) - with the primary key being student no.
This is where I get stuck - do I go for a maths, reading and writing tables and split it that way - or on an assessment basis, so prediction yr1, october yr1, feb yr 1 etc etc. Or is this completely wrong? Am sooooo confused, so any help would be greatly appreciated.
The outcome I want is to be able to query a childs progress, so for example: show children who achieved a 1a in yr1 maths, and then out of those children, who achieved a 2a in y2 maths (as this is the required shift in progress set by government). I really hope that makes sense.
I'm rebuilding my database from scratch now that I've learned a bit from the forums about developing a proper database and I want to investigate whether I have the relationships set up correctly, as I seem to be using lot of one-to-one relationship, which most articles say is very rare, and whether I am formulating many to many relationship correctly. Here is a screenshot of my relationship.
If I've understood this correctly, one-on-one is desired if you have a subset of data that is applicable to the main table only occasionally and is dependent on the main table's data. Have I used that correctly?
As for many to many relationship, articles say this is formed by having two one to many relationship linked together in a table. In my screenshot, you can see that tblAdvocacyVisit is the linking table. However, I've set GuestID as a one-one relationship whereas ClientID is one to many relationship, reasoning that since I do need to relate the Guestl Logbook data to Advocacy table for some queries I will be performing later, this will save me some work. I'm afraid that this may compromise RI or something like that. I can simply add a PK autonumber to tblAdvocacy to revert the GuestID as a one-many relationship, but can't see how that would not create more work in queries or so...
Oh, are the lookup tables supposed to be related at all?
I’m building a database of protein mutants we use in the lab. A lot of analysis is done on these proteins by various people and it’s becoming a bit of a nightmare keeping track of all the information per protein. What we need is a database. I’m getting a reasonable grip on it all I think with the excellent examples I have found on the forum but there are obviously areas that I have great difficulty with. The main problem for now is that many-to-many relationships are involved and I just cannot get them to work properly.
On each protein up to 8 different types of test are performed. To keep things easy I’ll call them A1 (for Analysis1), A2, A3 etc. These are done by different people from a group of 7. I’ve called them O1 (Operator1), O2, O3 etc. Each of the operators can do multiple analysis and the operators overlap. So e.g. O1 might be particularly good at A1, A4 and A7, O2 might be good at A2, A4 and A5, and O3 predominantly does A1, A4 and A6. What it comes down to is that each operator can in principle do each of the analysis so I have to give that option in the database. What does work (but is not the solution as it introduces 8x duplication of data) is if I have a separate table with operators for each analysis. The same tblOperators is linked 8 times. There must be a way to link each of the analyis to only 1 tblOperators, probably with the use of a linking but I just cannot work it out from the examples I've found here.
I am having trouble associating the relationships within a database to accomplish what I need. I don't know if I am over thinking or I just plain old don't get it. So, I ask if someone can draft this database up rather easily and post it for me or send me in the right direction to learn quickly.
This is what I am looking for: A table with people’s first name, last name, title, job position. That is it. I can do that and have done that. No problems.
Another table that has amplifying data on that person such as: date of birth, SSN, date employed, comments, etc.
What I envision is, creating a form to add the names, title, and job position of the people to the table.
Then I want to create another form that puts the other data associated with that person. This other form should have the first and last name from the first record to "view" only so the correct data can be added to the correct person.
That is it; I hope someone can help me out. I have been working on this for sometime and just can't seem to get it.
I have two tables, call the tblSales and tblCustomers.
tblSales looks like this:
CustID FName LName ... 1 John Doe 2 Jane Doe 1 John Doe 1 John Doe 2 Jane Doe 2 Jane Doe
tblCustomers looks like this:
CustID FName LName 1 John Doe 2 Jane Doe
with CustID being the primary field.
Suppose that no data exists in the FName and LName columns of tblSales. How could I automatically populate those fields with data based on the information in tblCustomers? I would like to be able to simply type in the CustID on tblSales and have the other fields populate automatically.
I'm sure Access can do this, but how do I set it up!?
I have following problem so please help if you can. I have three tables: Order Demands (from production), Order Demand Details and Orders (for placing by different suppliers).
ORDER DEMANDS Order Demand No.(ID) Date
ORDER DEMAND DETAILS ID Product Quantity Order Demand No. Finished (checkbox)
ORDERS Order No. (ID) Date Product Quantity
Relationship between Order Demands and Order Demand Details is one-to-many. I have no relationship between Order Demands and Orders but it should be actually many-to-many relationship.
Following must be possible: Order demands with specific details come from the production. It can be one item or several items (products) that are demanded. I must be able to place an order for one or more items (products) which are unfinished (not ordered till now) by selecting items. I must also be able to place an order independently from Order Demands for example because not all demands come from production department. After I have placed an order and the ordered products were supplied then I must be able to enter received products (maybe I do not get all ordered items) into Orders form for instance and this should check the field "Finished" in Order Demand Details table so that I know that I have received the products that are demanded from production department.
Do you have any suggestion how to organise relationships between tables. Thanks for your assistance.
Each product can have more then 1 buyer. In my table when I need to assign buyerID and Buyer2ID to the same person in order for the subtable to show the buyer is associated with it.
I have also tried to put 2 buyerTBLS on the relation ship screan and link one to BuyerID and one to Buyer2ID
I have created another table that has LinkedTBL LinkID ProductID BuyerID
and done a Many-Many relationship and this has worked however I was wondering if it may be done without this table.
I would like to just bring up the productID in a form and assign 2 buyers to it.
