Noob With Access - I Need To Create A Relationship?
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.
I'm an access noob, but decently experienced with Filemaker. I learned quickly that they are two very different animals with such a differing vocabulary, I may be in over my head with even the simplest concepts.
My problem: I am creating a quicky book order database for some professors. I have a value list (drop down menu) that references another table's column of professor names, but would like to create another value list dependent on the professor's name that would list the classes he/she teaches.
Table 1 Columns: Professor, Class 1, Class 2, Class 3, etc
Form 1 a) Professor field drop-down menu/value list populated by values in the table 1's professor column b) (need) A drop-down list that displays which classes the selected professor teaches.
What I am trying to do is write either a 'yes' or 'no' to one of my access tables based on the criteria of a linked table. When I build my query, I input the following into the field:
Expr1: IIf(([5WellInformation]![LowPH]<5.5) Or ([5WellInformation]![HighPH]>10) Or ([5WellInformation]![Temperature]>75) Or ... , "Yes", "No")
My query type is 'Append Query'
The problem I am encountering is that I require 39 different criteria to come up with either a Yes or No. If anyone of those 39 criteria fail, than a Yes is written.
When I place the entire expression into the query, half of it is cut off because the expression is too long.
Any Ideas? Am I doing this right... or is there a different way I should approach this.
Hi, I am trying to put together a mailing list to send out invitations to local schools.
I have exceed documents with several school addresses and I need to add to each school, a listing for all of the homeroom teachers (i.e. 1st grade homeroom teacher, 5th grade homeroom teacher, etc.) as well as Art teacher and counselor for each grade
How do I create a querie that adds the title (art teacher, principal etc) to the list of addresses?
I am not very Access savvy so, you may need to dumb it down a bit. Feel free to ask any questions.
For my own database which i created in Access i have something called a mutation date. so when i changed something i typed the date of that day. Now i want that to go automaticly. How do i do that??? It would be great if someone could help me with that. Once there's something changed in the record i need to make the date of that record changed. Hope u can Help me SilverBlood
relationships div and subdiv (1 to many) subdiv and issues (1 to many) issues and subIssue1 (1 to many) subIssue1 and subIssue2 (1 to many)
Now resolution table could have a one to many relationship with either issue table, subIssue1 table, or subIssue2 table. When I go to create any of those relationships, Access won't allow me. Tells me it can't create this relationship and enforce referential integrity.
Possibly I need to restructure my database to accomodate? I am not sure, any help would be appreciated.
I am a newbie to access and i am working on a project that has two tables the primary table has for example the following fields:
firstnameID(primary key) data type auto number Firstname data type text
the second table has for example : LastnameID(primary key) data type auto number Lastname data type text FirstnameID (foreign key)data type number
I created a relationship between the FirstnameID in the primary table and the FirstnameID in the second table .. My problem is this whenever i try to view the relationship when i open the primary table it don't show the relationship , The Field FirstnameID in the secondary field is always Empty the only time the relationship is created is when i manually insert the autonumber that is generated in the FirstnameID field , but i thought that since a link was created then that field would have automatically be inseted with data , am i assuming wrong or am i doing somethingn wrong? how do i get the autonumber to be inserted in the field automatically to create the relationship? Please Help
Alright I've been picking my brain for the past hour trying to figure this one out...
I have 3 tables... 1) Retailers-RetailerID, RetailerName, address etc. (500 entries) 2) Distributors- DistributorID, DistName (10 entries) 3) Uses- RetailerID, DistributorID
Sample data is as follows for 'Uses' table: RetailerID DistributorID 1 1 1 3 2 1 2 6 3 4 3 5 4 8
I am trying to create a search form based on a query which will return retailer information for those retailer who use multiple distributors.
For Example...There will be 2 text boxes and a user can enter a distributorID in these text boxes. If only 1 distributorID is entered, it will display records for that one.
I have tried the following but I have been unsuccessful:
SELECT Uses.RetailerID, Retailers.Address, Count(Uses.RetailerID) AS CountOfDistributorID FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID WHERE Uses.DistributorID = 1 GROUP BY Uses.RetailerID, Retailers.Address HAVING Count(Uses.RetailerID) > 1
Is this possible to accomplish with the way I designed this database? If so, can I get a little advice? I know this is a long question that can't be answered in full but I would just need to basics in order to be on my way. Thanks in advance!
I have a table that holds UK Postcodes and a customer table that holds customers.
I am trying to create a relationship between the 2 so when I enter a postcode in the customer table this is then related to the postcode table.
The main problem I have is that there is a lot of duplicate postcodes in postcode table so the primary key is simply a number as you will see in the picture.
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
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?
I've only just started using Access 2007 at my new job. I've been asked to create a database that will show appointments for all 10 of the employees. I have created a table for the main schedule (where ill put all the data) then one for each of the employees. I've managed to link the tables no problem but it wont let me create and updating relationship. It keeps saying "no unique index found for the referenced field of the primary table". How do I fix this?
I want it to automatically update the date, time, location, customer name and description, if its changed on the main schedule for a certain appointment on the corresponding employees schedule.
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'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").
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 currently have an Excel Spreadsheet w/ 20 columns and 800+ rows. The columns are such things as Product, Product Code, Description, etc. It has clearly grown to big for an Excel file so I created an Access table from the spreadsheet called All.
Goal - have employees open a form and select a product from a dropdown box and then place check marks next to the info they would like to see for the product they chose.
I created a seperate table called Product, and a query for table All, and a report for the query. I created a form w/ a combo box that lists table Product and has a submit button. I added a criteria in the query under the column product that looks at this combobox. When you run the form, select a product, and click submit it opens a report that shows the results of the query for that product.
The part I cannot get is the check boxes that determine what columns are show in the report. I added a checkbox to my form and then went to my query and unchecked show and added a criteria to look at the checkbox in my form. All that did was erase the column from the query permanently.
I'm sure I'm going about this is the wrong way, but this is my first hack at Access and I'm trying to learn as I go.
I'm a complete newbie to MS Access, and I'm working on my 2nd database.
Just a small and probably retarded issue: I have a table which includes a field, which contains four numbers. In a query I want my users to be able to enter a number, like 6, and the three numbers after that will have to be wildcards.
Example: The field has these entries: 8243 8184 6423
When the user enters "8", it should show the first two records. When the user enters "6", it should show the third record.
i have created a table and am trying to run a qry that counts specific data, i have been searching but cannot find the answer. I have a standard qry that has the criteria of "staff name" and dates between ## And ##. Now when that information is returned, how do i get a count figure for the results. So for example if there were 15 entries for september it would say september = 15 (ish). I have been told to assign a recordset but no other information was forth coming. Can anybody help with code or qrys for creating a recordset.
Please remember the noob status when it comes to access - lol
Recently our system crashed and so I fixed compacted and repaired, and all seemed to be good to go. However, next day a bunch of random fields that our reps were once able to add information to are no longer able to add info to. They seemed to be locked out, however, I go into the various forms that were set up and enable is set to Yes, and Locked = No.
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
