Related Value
May 24, 2006
Hey guys, new to access and from what I used to use it was simple seems a little more hidden or done differently in access. Its basic, all I am trying to do is match field value from products and another field value from mounts table and if they match on a new record creation in the products table then it auto fills in data on a third field coming from the mounts table. Seems like a simple relationship I come from using filemaker so I would just relate the two tables and if they matched on a new record it would auto fill in the value from any field that was related was point and click specify get value from there.. Figured it would work something similar, maybe im missing something.. Anyone answer this, im sure its an easy one.
View Replies
ADVERTISEMENT
Aug 30, 2006
I have a list of class sessions. To help with maintaining attendance, each session has its own roster. I just need to add a functionality so user can make sure the attendance roster is synchronized with the past sessions and future sessions in events of last minute additions/drop or updating names/contact infos. The sessions are related to themselves as below:
ClassID PreviousClassID
1 Null
2 1
3 2
4 Null
5 4
So therefore to propagate the changes into all sessions how would I instruct the query to look for each related ID then checks if it's also related...
I'm thinking I may have to use VBA to do what, but I hope that can be done with a append query.
Thanks!
View 14 Replies
View Related
Aug 28, 2007
I've been using an access d/b for ages, it's one of the template (Orders) databases included with access but modded to suit my own use. copy attached with customer details removed.
Last time I used it it worked fine but yesterday, when I tried entering order information, I get an error saying cant add or change a record because a related record is required in table orders.
Can anyone shed some light on the potential problem? Bear in mind I'm not an advanced user and have very limited knowledge on relationships in access.
Thanks in advance.
Marc
View 4 Replies
View Related
Oct 13, 2005
The "last" total in an Access query is unpredictiable. I have a Journal, with the main form/dataset being linked to the entries of that day. I would expect the 'last' total word to give me the last entry of that day, ie. what I ate for supper. But in the two qrys shown, it does the opposite. What is the most relable way to get the last entry of that day (ie. the one with the highest journalID)? :confused:
Sample Database attached.
View 7 Replies
View Related
Oct 29, 2005
Hi,
i need a query to return the first orderdetailsID and related productsID, so that all the other products that are the same as oldest order can be manufactured together.
In access help menu it shows it would be something like using the criteria under productname1 field:
(SELECT [productname1] FROM [the next one to cut query] WHERE min([orderdetailsID]))
But this returns an error cannot have aggregate function in where clause
min([oderdetailsID])).
I've also tried with 2 queries, 1 with the min function and the using inner join, but can't get it to work.
Thankyou
View 4 Replies
View Related
Jan 18, 2006
Before I spend an age trying to get this to work I thought I'd consult some experts.
I have a main data table for recording all possible insurance claims against a company. What I want to do is create say 2 incidents but link them in some way. i.e. One of our drivers had a car accident (1 claim against our motor policy), but then the driver of the other car sues us(1 claim against our public liability).
I can create 2 seperate records and maybe have a field to highlight parent/child reference numbers, but is there anyway I could allow the user to click a button and see the related records simultaneously?
Cheers
View 2 Replies
View Related
Sep 6, 2006
I have a form (data entry setting-yes) with several tab control-grouped forms. All the forms/underlying tables are linked by a field called ID. When I enter data in the main form, related records are only created in the tables for the subforms if data is actually typed into them as well. How do I force the tables to enter a record in the subforms' table, when only entering data in the main form even if the only field with data is the ID??
View 1 Replies
View Related
Apr 27, 2005
Hi
I'm have created a video database with a genre field. Is there a way that when i click a button a list is displayed of all the records which are the same genre?
Thanks
Aden
View 1 Replies
View Related
May 31, 2005
I have a date related problem. I have an IF statement, that verifyes if a certain date entered in a table is <=Now(). The problem is, that it's vorking great just for curent year, curent month. For example: today is 31.05.2005 and if the value in my table is 13.06.2005 than for the <=Now() testing, it's telling me that 13.06.2005<31.05.2005. Why? Please help!!!!!!!!!!!!
Thanks,
Attila
View 4 Replies
View Related
May 15, 2006
Hi everyone!
My names james, im 18 and im a data manager for a school.
My schools database stores student data such as names, classes and exam grades. I have a table containing each students personal details such as name and address. Each pupil is assigned a unique PupilID which is the primary key. I then have a number of other tables linked to it with one to many relationships. Each of these tables holds a different set of exam results. The tables are linked through PupilID. The primary keys for the other tables are auto numbers.
The problem is this: when i add a new student to the Students table i cannot get their PupilID to automatically be entered into the other tables containing grades as part of a new field. How can i make this happen?
Thanks.
View 9 Replies
View Related
Nov 21, 2006
Hello
Could you help me ?
My problem is :
I'm developing a ms database for the a "repair service" .
I created a table witch contains basic data from customer, including its unique ID number (key field), name and contacts ...
Now I need to store the "repair service" data from each costumer ( its time consumption, replaced materials...) in a separate table but, If I'm thinking well, I must have a table for each costumer so, I will need to link each individual "repair service" data table from each costumer to each costumer itself.
Do you know how to do this ?
Lots of thanks in advance,
Miguel
View 4 Replies
View Related
Dec 18, 2006
Hello!I've one issue with relationships.Ok, first of all I got two tables "Persons" & "Vehicles" and there's primary key on both of tables called "Person_ID" and "Vehicle_ID". There's also "Person_Vehicle_ID" field in "Persons" table and "Vehicle_Person_ID" in "Vehicles" table. Rowsourcetype is set to "Table/Query" and rowsource is set to "Person_Vehicle_ID" to "Vehicle_ID" and "Vehicle_Person_ID" to "Person_ID".Ok, here's so called issue what I've noticed. I need to create somekind of dynamic connection between these two fields on different tables. Let me explain, when I change "Person_Vehicle_ID" value to 2, value in other field "Vehicle_Person_ID" would change too to equal that persons ID. So, if "Person_ID" value is 1 and "Person_Vehicle_ID" is set to 2, then that record "Vehicle_Person_ID" would be 1, where "Vehicle_ID" value is 2.(Hope you did understand me and sorry about my crap english grammar.)How would you build this kind of feature?Feel free to ask further questions, also waiting replies asap. :)
View 10 Replies
View Related
Jan 22, 2007
Ok due to user error (and my own fault for not archiving an old database) we now have two databases which have had data entered into with the same table structure.
The table structure we have at the moment is with the related field in []:
Propertytbl[Propertycode] is related to Systemtbl[sysID] which is related to Assessmenttbl.
There is also another Temperaturetbl linked to Systemtbl by [sysID]
The problem i have is sysID is an autonumber. I can't simply paste in the difference in data as the assessmenttbl records and Temperaturetbl records will point to a different system (as the sysID will change in the Systemtbl data)
Can anyone think how i can do this either through using queries or programmatically so that i don't have to enter 5000 records manually.
I've tried using append queries but this produces the same affect as above.
Many thanks
Matt Collins
View 1 Replies
View Related
Apr 2, 2007
I have a combo box in my main form that gets its list of physician names from tblPhysician. When the physician is chosen and the record saved, it saves the PhysicianID to my main table, tblOncReg.
When I delete a physician, any record that had that name chosen continues to have that name in the record despite it being deleted from tblPhysician.
I thought that setting relationships would solve it, but when I select "Enforce Referential Integrity" and "Cascade Update Related Fields", I get an error saying "Invalid field definition 'ID' in definition of index or relationship.", and it doesn't set the relationship.
Would setting the relationship solve that problem?
View 4 Replies
View Related
Jun 26, 2007
I have two databases, one containing the tables and one containing the forms and queries. The forms/query database is built from linked tables from the tables database. I would like to expand one of the fields from 7 characters to 9 characters. I planned on doing the in the table database, then updating the linked table. However, the table I need to alter has relationships with about 10 other tables and I get the (you cannot make changes to a related table error message). Is there a way I can make this change without deleting all the relationships?
For future reference, should the relationships have been created in the form/query database?
Thanks, N
View 3 Replies
View Related
Sep 2, 2007
In a case where two tables are related, can the field names which relate them be the same? Or is it necessary to use different field names?
Robert
View 4 Replies
View Related
Mar 13, 2008
Greetings to everyone,
I'm building a database which will generate work orders, and those have a opening date and closing date, besides that it has a work order number that is of the following format:
YYYY/ NNN (being the NNN the work order number in that year)
to create the number of the work order I'm trying to fill the WOYear field
with the year of the WODate field...but I'm not being able to do it...
How should I do it?
Thanks.
View 5 Replies
View Related
Apr 4, 2008
I have a table that I'm storing Horsepower and Watt in. Is there anyway to set it up so that when I provide Horsepower it automatically populates Watts? (just a simple multiplication)
View 1 Replies
View Related
May 29, 2005
Hi :confused:
I am trying to set up a query. i have to work out the cost of a phone call with the use of three fields--
i have: lenght of call, country call code-e.g england is +44(these are fields in my query) and then the cost per minute values for each country call code(but these are in a table)
e.g.Country CodeRegion Cost per minute
+1 United States, Canada, Caribbean£0.20
+20Egypt £0.30
+212Morocco £0.30
How would i set up a query to solve this problem?#Can someone please help me???
View 1 Replies
View Related
Jan 26, 2006
whats the best way of appending tbl_orders and the related data in tbl_order details to the two identical archive tables when the "COMPLETE" column is "YES" in tbl_orders
View 1 Replies
View Related
Dec 1, 2006
Hi there,
I have a table which shows telemarketing that has been completed on a weekly basis where the caller places the date they called in one field and a followup date (if needed) into another field if a followup call needs to take place.
I want to create a query which will show me all calls that have got a followup date put inplace but the followup call has not been made.
E.g i call mum on 29/11/06 i put down followup on 30/11/06. I call on the 30/11/06 so the followup has been completed but need to follow up again on the 01/12/06 but i do not complete this one.
Any ideas???
Thanks
View 3 Replies
View Related
Jan 12, 2007
Hello, I apologize in advance if there have been lots of questions like this, but the search won't let me use AND as a search term!
I have two related 1 to many tables. The parent table contains clients, and the child table contains categories, each client can have many categories. (i originally intended to have the categories be columns in the client table, in which case what i want to do is easy, however, then it becomes a nightmare when the user wants to add a new category hence the related situation described.)
Is there an easy way to find all the clients who have both category 7 and category 10? I can do it writing a query to find all the 7s, then another to find all the 10s, and a third to find those which have both. I am hoping there is an easier way, as i need to give the user a way to search via categories in whatever combination they fancy. The OR's i can do easily it's the AND's that are causing the problem.
The only idea i have at the moment is to make a temp table with the the clientid, and a long field holding each of the category ids, seperated by commas, and then searching using like "*7*" and like "*10*".
Does anyone have any better ideas, i'm hoping i'm missing something really obvious......
Thanks, Anna
View 1 Replies
View Related
Jun 11, 2007
Dear all, i have the following codes:
enter_date = Format(txt_date, "dd/mm/yyyy")
Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from [entered_date] where Date = # " & enter_date & " # ")
If Not rs.RecordCount = 0 Then
DoCmd.Beep
Msgbox "Selected Date's report already been created", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
rs.Close
Set rs = Nothing
suppose,i have one record in the table [entered_date] which matches the user input - enter_date = "08/06/2007",but whne i run the above coding,i get the rs.recordcount = 0. it suppose to be rs.recordcount = 1.
can anybody pls help? thanks
View 3 Replies
View Related
Dec 9, 2007
I have a very simple database with two tables. One stores Schools that I work for and the other holds people that work at that school.
I have a form that displays information from the schools table and I have a button that I want have open a form that displays the people that are related to that school.
THe problem is I can't figure out how to limit the recordset to just the people from the school record I was just viewing.
For example. I am looking at a record with School ID 1. I want to run a query on the People table that will return just the people that work for school id 1. I just can't figure out the criteria part.
Any help is appreciated.
Thanks
View 3 Replies
View Related
Apr 14, 2005
Dear All
I am creating a form that is bound to the main table in my database.
However, I want to show one field from another table on the form that is linked to the main table (one-to-many relationship). I would do this using a sub-form but I also want the form to be a continuous form (as this has been asked form from on high) and Access won’t let me have a sub form if the main form is continuous.
Is there any other way that I can show the related field?
View 2 Replies
View Related
Aug 16, 2005
Hi,
I have a database with a couple of tables. All of which have a 1:1 relationship to another table, patient:
- patient (id (auto_increment), name, address, etc)
- nutrition (patient_id, some, stuff, etc)
- sleep (patient_id, foo, bar)
The patient_id in nutrition and sleep is a primary key and corresponds with the id of a patient (my 1:1 relationships)
I have a couple of forms. One for each table actually. In this form the user should be able to add/modify a patient's data. These forms are not to be used by themselves.
I have another form(the main form), which has a tab-control element on it. There are 3 tabs on this and on these tabs the 3 different forms are displayed.
Now for the problem:
- when I add a new record to the database using the main form, there is no problem when adding a new patient. Woohoo! When I switch to another tab, , however, the problems start: I can enter new information, but when I leave the tab I get an error: "Index or Primary key cannot contain a null value." Blech.
I want Access to realise that it should use the id that is given to the patient in the first tab, but it seems it cannot get it automatically. (I am not sure, but I think the problem didn't occur at first, so I think I may have done something which is causing this problem.)
Any help here is appreciated.
PS I was unable to find any posts on this forum that can help me with this. The closest I could find was: http://www.access-programmers.co.uk/forums/showthread.php?t=39933
View 4 Replies
View Related
