I'm just putting a database together, and I'm having a relationship "issue". It's fairly simple right now, but I'm not sure if you can do what I am trying to do..
Basically I have, so far, 3 tables. Each one has a primary key "Serial #".
There's a "Systems" table, a "Parts" table, and a "cash flow" table. I have a link from
Systems - Cash Flow
Parts - Cash flow
Both links are set to enforce referential integrity (with cascade update/delete enabled as well).
What I want it to do is work so that I can add new values to either the Systems or Parts table, with their own unique "Serial #" code, and then be able to add the value to the cash flow table.
Currently, I can add items to the "Systems" table, and then add the corrosponding entry into CashFlow, and that's fine. If I try and input an invalid entry, it will tell me that the appropriate entry does not exist in "Systems".
However, I cannot add anything to "Parts", since it gives the error that the appropriate entry does not exist in CashFlow. Obviously, I need to make it in Parts first, and even if I wanted to make it in CashFlow first, it wouldn't let me (entry does not exist in "Systems").
I have tried fiddling with the relationships, but I get other problems like not being able to add items to "Systems", etc. If I try and drag "Parts" to "CashFlow" the other way (in the relationship), it tells me that I cannot do that because it violates the integrity rules.
I can see WHAT the problem is, and I know WHY it's doing it, I just don't know how to fix it.
Basically, I want the field "Serial #" in the table "Cash Flow" to be able to draw from the field "Serial #" in either Parts or Systems, but reject anything else (in other words, incorrect serial numbers, since the item shouldn't be in the cash flow table until it has been registered as an item in either "Parts" or "Systems").
We have customers, cars availible and the date and time and location etc... of when to pick up the customer
So 4 tables i have so far:
cars customers Bookings Drivers
a field in Bookings known as customerID is joint to the customers table, field CustomerID. a field in the Bookings known as DriverID is joint to the Drivers table, field DriverID a field in Bookings known as CarID is joint to the cars table, field CarID
I am wondering, does this sound ok to you? i am not great with relationships but i am improving. does this sound ok?
Another Q is, i need to know what times of bookings are availible. What is the best way of going about this?
hi, im a a level it student and my current coursework is making a new program/system for access im making a new order form for the shop where i work and i want to link sundries on a diff order form to sundries on the main order form so it prints it all off on one sheet, basically i want to know how i can get the realtionship between ID number of the order, and the sundrie to link together, and if its possible at all, if its not ill have to change my project slightly which i dont mind, just wondered if any one could help me out a little thanks!:p
I am having a bit of troubles in my design conceptualization i believe. I am building a database to keep track of a companies liabilities....therefore i have several liabilities that all belong to one company. I have one table that is called 'client information' that lists the corporation name which i have identified as the primary key. My other table is called 'liabilites' and i also have a corporation field in that table but i can not define it as the primary key because i want to have several payments under one company.
I have built tables in MS Access for a very simple shopping cart.
It includes: Catergories, Item, Customer, Shipment types, and Basket (cart). (for site) There is also a User table, but that is only used for the Backened side for login.
This is what I want the User in the backened to do: -add/edit/del Categories. -add/edit/del Shipment type. -add/edit/del Items according to Categories. (in one category, it can have many items, yet one item can go into more than one category).
This is what I want a browserer to do he/she enters the Site: -he/she can add item(s)(which are under categories) to cart[basket](no login needed). -At the basket(1 page), the user can view all the item(s) it chose, be able to change quantity, display subtotal, VAT, Shipment price, total price and enter it's customer details (e.g. name, mobile, etc), then sends form. These details are then "added" to the database and sends notification to admin(user) email. Therefore, no payment via client/server.
My problem is the relationships in Access. Do I need to connect all tables? I tried connected my ItemID table to the BasketID table and it got a bit confusing from there. Also the Categories got all messed up on me.
See my screenshots: http://salis.aspfreeserver.com/sample/relationships.gif http://salis.aspfreeserver.com/sample/table_menu.gif
Im building a web application to control the stock of a small company.
I already made the database with all the items and descriptions in one table.
This table would be the main warehouse. so now i have to create 3 different departments in different tables where i have to move stuff from the main warehouse. So if i have 10 boxes in main, and i move 4 to department1. then i would get 6 boxes in main and 4 in dep1.
My web app would be constantly updating the stuff being sold in departments 1,2,3.... so here is my question.
how do i make the database to update the quantities by itself in the main warehouse table if department X sells anything...?
Like in Excel, when you work with different sheets that can update the other ones if you change a number. the rows can be linked... can i do this in access?
Otherwise i would have to write a lot of extra SQL code in the ASP scripts and im really out of shape cause i don't program in ASP sisnce very long time ago... years.
Hello everyone. I'm not sure should I post this question here or at SQL Forum? However, my problem is this. I have 2 Tables, Table1 has AutoNumber as primary key, my second Table2 has index key field. I have one-to-many relationship on those two fields, with enforce referential integrity, cascade update and delete
The access program works fine until I upsized to SQL server. [u]Then I was unable to perform cascade update or delete. I have check SQL table (which I have little knowledge about) and seems to be okay, the relationship exists. But at ms-access I was unable to make the cascade update and delete.
i have imported 5 excel files and linked to a query named stock.i have linked the code,description and quantity from each table (ms excel imported) to stock. the problem is not all the codes available in the tables r available in stock.there r more than 25000 codes but only 2614 r listed in stock.i dunno whether its the problem with the way i have linked.so if u can help me with the problem i will be really thankful. 5 excel sheets contains the code,description and qty of 5 shops.in the query stock contains 1st column-id,2nd column-code,3rd column-description and 4th,5th,6th,7th,8th column the qty from 5 excel sheets respectively.
Field 1: Group Text field ( Primary key) Field 2: Group Description Text field Field 3: Uidgroup( Autonumber)
Table 2: Subgroup Text field
Field 1: Group ( I want to bound this column to Table 1's Group field that is column 1) I have set bound column property to 1 and column count 1 and the Subgroup table is showing group fields as input perfectly no issues in that )
Field 2: Subgroup, Text field( Primary Key )
Field 3: uidsubg( Autonumber)
Table 3: Email
Field 1: Group ( Bound to Table1's Group ; showing values in combo box, setted bound column property to 1 and showing group field perfectly, no issue in that )
Field 2: Subgroup (I want to bound Table2's subgroup field, which is column number 2, so I wrote 2 in bound column property and row source is table subgroup ; Here is some error comes up, values from subgroup field of subgroup table not being shown up in Email Table's subgroup field as combo box. )
Field 3: Email Text field
Field 4: uideml (Autonumber ) primary key
I want to prepare a Data entry form should have all these fields from all the tables. That should work in following way, first user selects Group then User selects Sub Group and write Email and save the record.
What relationship should I set, or shall I change the table structure.
I have upsized from Access 2003 to SQL Server 2008 R2 using upsizing wizard. Everything works fine. But I don't see relationship in SQL SErver 2008 R2 if I go to database. But I set relationship in Access 2003 before upsizing it.
Amso I don't see relationship (diagram) in Access 2003 , which I was able to see before.
So do I need to again recreate the relationship amongst the table in Access 2003 Or SQL SErver 2008 R2 ? I thought, if you link tables, everything should be taken care but i don't see relationship structure any more.
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.
