Seems like the more i get into reading the more confused I get in this basic
fundamental. I just need it explained really clear and basic, then hopefully it will enter the subconcious and I will be able to proceed :)
I am trying to build with Access XP in 2000 version a small program for a small medical office.
Patients are offerd various services and they have vists to take advnatage of them. They are charged for the services and run an account which is then paid, leaving a balance.
I want it all to be entered via forms for older non computer literate staff.
I have done the main table okay with all the info we will need and I have messed around with the other 3 tables I think I need.
1. To enter the Services offered a description and fee.
2. Enter when they visit and what services they have and fees with total
3. When they make a payment and balance owing.
They are one to many relationships, correct?
For a start am I on the right track ?
My confusion is setting up the relationships especially what field/s has to be linked to fields in the other tables to make it work.
Is there a rock solid rule where a certain field has to be in each table and has to be linked ?
These are the first fileds in my MAIN TABLE..
Patient ID Auto Number Primary
Medicare Number Can be blank
Last Name Text
First Name Text
Address Text
SERVICE TABLE (Primary?)
Service Type /Combo- Accuncture-Massage-Herbs-Vitamins-Other
Description of Service Text
Charge for Service Text
VISIT (Primary?)
Date and Time of Visit: In case 1+ visits in one day
Reason for Visit
ACCOUNT
Date
Service Charges
Payments
Balance
I know it is basic but I need to get over this block..
Hello all, I've been reading through threads and there is alott of very good information on here, and seems to be some very knowladgeable people. So my question will probably be easy to answer.
I have a database of part serial numbers. I have created a query and a form to pull out all serial numbers for an inputed part number.
What I need it to do now is highlight any repeated serial numbers. How do I go about this?
I am thinking it will be a conditional format in the form itself to change font color but I dont know how to make it compare the values.
I have this form which shows the contents of a table... however, this table is linked to another table. How do I show the results of this linked table in the form?
I am a complete novice with MS Access and am trying to find my way through our website database. ((URL address blocked: See forum rules)).
What I am trying to do is set up a search form to use offline so that it easier for us to trace animals that we have rescued.
I have the form all set up and it seems fine except that two of the fields I have on the form show numbers instead of the full details.
I think that what I need to do in those fields, is "point" them to another table in the DB so that they can "lookup" the relevant data in those tables and display the text instead of just the numbers.
I have tried searching through the MS Access Manual but to me, most of it may as well be written in a foreign language !
Hopefully t is a simple task that can be easily explained !
Heya, I know this is probably something simple as I know I have done sql problems like this before. However, I am wanting to show cars which have the same name but are of different colours (without duplicates), I figured I could just use a SELECT DISTINCT? However, when I do this it just brings up one car name and one colour when there are multiple colours.
SELECT DISTINCT car.carname ,car.colour FROM cars;
I am guessing its because the SELECT DISTINCT statement is applied to both the name and colour.
If somebody could just tell me what I am doing wrong because I am guessing its something simple yet I am too frustrated to see what it is now!
So I have a table "InventoryTracker", with one field lookup set to list (drop down) and the source is another table "inventory", field "Item". This works good and all the items that I enter in the "inventory" table are available from the "InventoryTracker" table. Both "inventory" and "InventoryTracker" have a field called price.
Heres what I want to have happen. When I select a item from the drop down list menue in "InventoryTracker" it automatically fills in the "InventoryTracker" price based on the price from the "inventory" table (for the item with the same name, the one I just selected). This has to be fairly straitforward, I can do it in excell, but not acess.
I have this form which shows an order.... linked to this order are the client, the principal company, and the company that carries out the order. The information for these three companies are all stored in one table (since the information is very similar).
Tables look like: tblOrder - OrderID - ClientID * linked to CompanyID - PrincipalID * linked to CompanyID - Contractor (company that carries out the order) * linked to CompanyID - Date - ...
Hi all. I really need help with this. Does anyone see a problem with my relationships , i have attached a copy. Any help will be greatly appreciated ClaireB
I am new to database design and curious about relationships. In a hypothetical situation, if I have four tables: tblProducts, tblOrders, tblEmployees, and tblCustomers, how could the relationship be set up?
The Products are stored to keep track of the quantity on hand and the employees will also need to be tracked as to how many products they sell. Customers are recorded and Orders wrap up all three tables with the fields: OrderID, ProductID, EmployeeID, and CustomerID.
Would it make sense to say that many products can be bought by many customers and/or many employees can place many orders.
I've selected a number of tables to the relationship window. When I drag the primary key on one table to the corresponding foreign key on another table, the link created is one-to-one. I want one-to-many. Why did it create one-to-one? How do I change this to one-to-many?
Hi all. I am new to the use of access and have been told that I need to create a one-to-many relationship to be able to have several records from a drop down list saved to one unique number (my primary key).
Is this correct and if so how do I do this? If it is not correct what is the best way to do what I require?
I have created two tables which are linked with a one-to-many relationship
Using a form the user enters a date. Then in VB I add a value to the date and store this in the second table.
I have simplified this to try to find out what was going wrong and found that when the database adds a value to the second table which only has two fields it first of all adds it to the begining of the record set and the id field does not update to that of the first table where the relationship is joined.
Do I just captutre the value of the id field and add this to the recordset. How to I get the database to look foir the end of record set before I appent the new generated record.
I have looked for this on the help section; however, it does not seem to be covered. How would you change a one-to-many relationship to one-to-one? Access seems to automatically guess that the many side is where the foreign key.
Can it be changed from Access or is entirely on how you design your tables?
Hi !! I am doing a school project on a school database. In my DB I have a table with all students records (eg. Student ID, Name, Surname, DOB, class etc) and I have another table where the student fees payment slips will be printed. The fee table will only have the ID, name, surname and class fields and fee will be input for every student for every term.
I want the fee table to get all the info except the fee from the first table automatically when I give a student ID.
I have created a one to many with only Student ID fields linked, NOTHING.
Then I tried to link many fields on each side (eg Name to Name, Surname to Surname etc) but I got a msg "No unique index found for the referenced field in the primary table"
would having a db with several tables and NOT setting any relationships up in the relationships window affect the db in any way?
i have such a db, which originally had all the relationships set etc. however, as i encountered errors and had to replace tables and delete entries, i must have at some point deleted all the relationships.
i just relaised that the db now has no relatioships set up (all the primary keys are still in the tables) so i'm wondering if this matters?
If I have a table "tblContinents" with just two fields ContinentID and Continent and I have another table called "tblCountries" with three fields CountryID, Country and Continent.
After reading several articles, i decided not to make the Continent field in the tblCountries as a lookup field but rather I will do that at form level via a combo box. In such situation, since i am not creating a lookup field at table level, do you think that it is necessary to create the relationship between the two tables in the "relationship window", or shall i just leave it as it is, and if the need arises i will create such "relationships" in queries?
hi, im new here and i dont really know much about databases with access. im currently doing an assignment at school requiring me to do a entity relationship diagram for all my tables, which i do not know how to do. Can anyone please assist me asap? thank you very much in advance
below is the link where i uploaded it because it was too big to be uploaded onto this post. :)
I want the one-to-many relationship to be based on my own generated quote reference rather than the PK/FK.
My quote reference looks something like this IN123/10
Does the character '/' cause a problem with creating the relationship as I get the error message "No unique index found" even though I know that they exist?
When I view the relationships window, not one of the relationship connector lines (whatever the technical term is) do not show up! I click view all relationships, view direct relationships. I restarted my computer, restarted Access. Nothing!
I know I have relationships because when I am table design view I try to change something that I know has a relationship and it doesn't let me because it says I have a relationship.
Here is what I have. The database is made up of three tables (see below and see attachment). I have it set as one event to many ethnicity's and many personnel with the event ID as the PK in all three. Did I do this correctly? I thought I did and I had a form with each one of these running correclty but now it is not working.
tblEvent EventID(PK)
tbleventEthnicity EventID (FK)
tblPersonnel EventID(FK)
See attachment for the other fields.
Can someone just check to see if I set this up correctly or what I should do differently?
I have already made a relationship 1 to many, i want to add another relationship. I put the name of the feilds i want to join up in the drop down box. I put "enforce in..." and "Casgade update related fields" on and join type 2. Then when i press ok, it comes up with "file title " to "film title" and comes up with "no unique index found with the referenced field of the primarytable".
I am writing a access database for work(fire dept). It is a database for the vehicle checks. Most of the fields are Boolean fields we just need to know if the inventory is on the unit. I am kind of new to this. My tables so far are as follows:
all list the inventory that is supposed to be in that table
Everything is dependant on the Vehicle it is on so my question is Do I relate everything to a look up field in TUnitNumbers(intUnitNumberID) or is there a better way to do this? I need to relate everything to the date it was checked
OK. I'm not real sure how close I am on this. I have a feeling I'm not too close so I was hoping I could get some help. I have a database with 11 tables. These can be seen in the screen capture attached. For the 'Action Taken' table there are 10 fields for 'Departments Involved', 15 fields for 'Memo Sent To', and 15 fields for 'Job Title'. The 'Departments' table is linked to 'Departments Involved1', the 'Memo Sent To' table is linked to 'Memo Sent To1', and the 'Job Title' table is linked to 'Job Title1'. All the other tables are showing the fields that are in that table. When I enter a record and try to go to a new record it gives me the following error message.
"You cannot add or change a record because a related record is required in table "Background Information'."
I'm not sure what to do about this. I'm sure there is a simple solution but I don't know what it is. I am pretty new to Access (this is my third week on the job) and a little confused on some things. I would appreciate any help you could provide.
Hello. I'm new to databases and anything I know I've read up on. I know that relationships are essential so I'm trying to sort a database in ms access that I have inherited at work. It contains health service staff and their record of training - various training that they do regularly. I've managed to put the data in 1nf but am having difficulty after that.
A couple of questions:
If I give each staff member a unique id do I need to link their job title to it? Job titles chang quite a lot.
Other information about courses, dates of training, trainers etc. - should they be linked to staff name via separate tables?
I'm using a spreadsheet example from microsoft that shows me how to go from raw data to 3nf but I'm not sure how to split the information, so if you have any suggestions or know any good guides I'd be grateful for your help.
