Relationship Probs On Access
Feb 7, 2006
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
View Replies
ADVERTISEMENT
Jun 23, 2005
Private Sub quicksearch_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID number] = " & Str(Me![quicksearch])
Me.Bookmark = rs.Bookmark
End Sub
this is the code that im using to help me with a listbox and a search text box that is embedded on my main form. it works ok. The problem is, i was trying to link an already filled out Table with data to this interface.
When i key in information from the form it works, but when i just enter stuff through the table (which was already completed and handed to me) When I click in the listbox on the main form the code window just pops up showing this section of my code. Help./
Help.
View 3 Replies
View Related
Feb 2, 2005
Hi,
I'm developing a database to handle various aspects of quality control reporting. I've got a working structure however, i think, i have gone off the track slightly... First I'll show you part of the structure:
(please see attached .jpg)
(hope that makes sence)... so effictively I have a many to many relationship.
I have forms for each of these entities (frm_parts, frm_Suppliers). The problem i have noticed is you can assign the part a supplier in frm_Parts but it does not appear in the transaction table as a record. Is this because i have based the field "Part Supplier" (a combo box) in frm_Parts on a query? If this is not the right way to go about it. how do i go about it?!
I have an idea... on this.. If i create a new combo box on the form I can use the wizard to select the supplier ID from the Supplier table (i believe) and "store" it in the transaction table... i think! But I want the user to select the Supplier by drop down list showing Supplier Number and Supplier Name.. not the ID.
Any help is much appriciated!
Next job...
I have a form where I would like the user to be able to enter, for example, a Supplier number and see if a record of the Supplier exists yet. Next I want the user to be able, assuming the Supplier record exists, to search for a record of a Part number from that supplier. Next I want them to be able to "select" that part and be able to open a form with a new record using that part number (this form holds details of a report rasied against that part).
See what I'm getting at?
I've had a go at this but it's been a couple of years since i did any detailed work on an Access database so somethings are a bit hazy!
I'm not asking you to do this for me, but I would much appriciate some pointers on how to create this sort of thing!
Many Thanks
Rob
View 6 Replies
View Related
Feb 21, 2008
Hi to all,
I'm using a Access 2003 form to access a query that'll be used to generate a report. The example I'm following is found in http://office.microsoft.com/en-us/ac...CL100570041033.
On my form, I have a combo box (Course ID), textbox (StartDate) and textbox (EndDate).
In my query, I'm using the values from the above 3 controls as criteria.
E.g.: In the Course_Date field in my query, I used Between [Forms]![Form_Name]![StartDate] And [Forms]![Form_Name]![EndDate] as the criteria.
If I leave the form controls blank,and click on the OK button, it'll generate a report with errors. What should I do if I want to generate a report that display all records in the report? E.g.: If I select a course in my combo box and leaves the start date & end date blank, it'll generate a report that displays all records for that particular course.
Thx!
View 1 Replies
View Related
Aug 8, 2005
Hi,
We've got a database that was on an old computer (Windows 98), and we wanted to transfer this database to our new computers (Windows XP). The database transfers over fine, but there seems to be a problem.
When we try to run a report, it doesn't semem to work properly. We are a training agency and the report in question is designed to show the courses booked in for each tutor between two set dates. The dates are typed into a form (start date and end date), and the tutor can also be selected from this form, and then a report is generated.
Without dates being inputted, all the courses are shown (so the report is pages and pages long). This is working ok. However, when dates are inputted the report stops working and error messages appear on the report. Hence it seems to be a problem with the dates, but I can't understand what since all the boxes are exactly the same as the version on the other computer, and when transferred back to the other computer it also works fine.
Any help with what could be going wrong here would be GREATLY appreciated.
Thanks in advance, Chris (FANE).
View 4 Replies
View Related
Apr 2, 2008
Hi guys!
I have a front-end (FE) linked to a back-end (BE) db.
I have added a password to the BE, but the knock on effect is that the FE won't work now. Just says 'Invalid Password'.
I'm using MS Access 2002.
Any help would be appreciatted.
(The password is to prevent unwanted users from deleting data on the BE on the server)
View 1 Replies
View Related
Mar 26, 2005
Hi there
I am trying to make a simple "booking" system.
This booking system is for cars.
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?
Thanks :)
View 6 Replies
View Related
Nov 16, 2006
Hi all,
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").
Any ideas? :)
View 9 Replies
View Related
Mar 15, 2007
Hello,
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.
How should i set up my tables and relationships?
Any help is greatly appreciated.
Thanks
View 1 Replies
View Related
Aug 22, 2004
Hi,
Hope someone out there can help me with this.
I have a CITY table. Each city can have 1, 2, 3, or 4 technicians.
The TECH table contains (up to 7) phone #s for each technician.
I need to produce a report that displays the phone #s in a matrix type arrangement per city, some thing like:
CITY1 TECH1 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
TECH2 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
TECH3 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
CITY2 TECH1 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
TECH2 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
TECH3 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
Any ideas?
Thanks
View 1 Replies
View Related
Sep 22, 2005
Hi,
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
Thanks in advanace...
Sass
:confused:
View 3 Replies
View Related
May 20, 2007
Hi
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.
Thanks!
View 3 Replies
View Related
Oct 22, 2012
I want to get a page like following through DW8+ASP
>Yageo
#Resistor
#Capacitor
>Vishay
#Diode
#Transistor
database like following
tb_company
ID name profile website
1 Yageo *** ****
2 Vishay *** *****
tb_product
ID name category
1 yageo resistor
2 yageo capacitor
3 vishay diode
4 vishay transistor
How can I do to get a page above?
View 4 Replies
View Related
Feb 12, 2005
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 appreciate any help …. Thanks.
View 3 Replies
View Related
Apr 12, 2008
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.
View 3 Replies
View Related
Jun 29, 2015
Why and where do we create a multiple field relationship in Microsoft access.
The relationship between CompanyContacts and CompanyProducts.(Adopted from Microsoft access 2013 inside out by John Viescas).
What was the main purpose of using such a relationship here and where are such relationships frequently created.
View 7 Replies
View Related
Jun 14, 2013
I have three tables.
Table 1: Group
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.
View 1 Replies
View Related
Nov 9, 2011
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.
View 1 Replies
View Related
Jan 14, 2006
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?
Many thanks,
Simon.
View 8 Replies
View Related
Feb 28, 2007
Hi i need help with one of my relationships
I need help with linking Member 1: N Reservations
the entites are
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)
The keys are underlined
Cheers
View 1 Replies
View Related
May 16, 2007
Hi there!
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.
Thanks
View 2 Replies
View Related
Jul 25, 2005
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
What is making this so difficult?
View 3 Replies
View Related
Aug 14, 2005
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
Barnesy
View 5 Replies
View Related
Aug 19, 2005
Trying to set up a fairly simple DB. Here is the layout:
tblRoom(roomID, roomNumber, rackOne, rackTwo, rackThree)
tblStudent(studentID, name)
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.
View 2 Replies
View Related
Aug 30, 2005
Hello,
I'm having some problems setting this up right.
I have 3 databases.
Volunteers
FIRST
LAST NAME both primary
Movie Data
MOVIE
DATEboth primary
Attendance
MOVIE
DATE
USHER 1 First
Usher 1 LAST
View 1 Replies
View Related
Aug 30, 2005
Hello,
I'm having some problems setting this up right.
I have 3 databases.
Volunteers
FIRST
LAST NAME both primary
Movie Data
MOVIE
DATEboth primary
Attendance
MOVIE
DATE
USHER 1 First
Usher 1 LAST
USHER 2 FIRST
USHER 2 LAST
AUTOKEY PRIMARY
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.
Any ideas?
Thanks,
Cody
View 11 Replies
View Related
