I've got a problem between a form and report, hope the fellows can give me a big hand!
Here i have a PO form with a combo box where i choose a "staff name" from a list (rowsource select from "staff name" table, there are "key" & "staff name"). The combo box would be filled or left blank.
Then a PO report would be generated, certainly the staff name would appear on it.
Problem 1 is, i want to show the name on report, while only the key is entered in PO form.
Problem 2 is, i cannot simply add "staff name" table into "PO" query, otherwise "PO" without "staff name" won't be shown. what i mean is, so far I can't make both "staff name" key and name appear in PO form.
I have Table Products and Table Types. I've created another one ProductTypeRef where i save the references. For example:
Products Id Description 1 Shoes 2 Clothes 3 Hats
Types Id Description 1 Athletic 2 T-Shirts 3 Trousers
ProductTypeRef IdProducts IdTypes 1 1 2 2 2 3
I have two combo boxes, Products and Types. How can i relate these two combo boxes?I mean if the user choose Shoes from product combo box, he'll get (as a choice) only the Athletic (or all the available values for the Shoes product) from the types combo boxes.
Can anyone tell me how I can do the following without using code: I think it's really easy, and am sure I've done it hundreds of times before, but my minds gone blank...
When I select an entry from a combo box in a form, I'd like a related entry to appear in another field on the same form. I have a table with the both fields stored in it, although theres only 3 records so this might not be necessary.
I have a feeling this is a common and probably easy question, but I couldn't find anything on it in the forums.
I have a main customer form with multiple subforms on it to keep track of my clients insurance policies and claims. I enter their contact data in the main form, and the policy data on one of the subforms. Then when they have a claim, I enter the policy information on the claim subform as well.
How could I have the combo boxes in the claims subform (which is storing data in a claims table) only show the policy numbers (stored in a policies table) assigned to that particular person? I would assume I need to somehow filter the policies query so it only shows records for that particular person, but I'm not sure how to do that on the fly.
I have a combobox on my form with fields related to it (ie. when an option is chosen in the drop-down, the next two fields are also populated with data based on the selection made - I know this is duplication of data, it's just the way the 'powers that be' wanted it. I'm changing it in my next version of the database!)
The two fields that are populated as a result of the selection in the combobox are migrating into the related table fine, but the actual selection in the combobox isn't - it's showing up as blank in each new record in the table.
In my database I have a table that keeps track of a package of items. The package is assigned a package type (counter display, end cap, half pallet, full pallet for example). The record of the association of the package and it's type is held in the main table.
Each of these package types is either a case or a pallet (counter display and end cap are cases and half and full pallets are pallets) This relationship is kept in another table (we will call it description table).
Now, based on the type of package and therefore it being a case or pallet a UPC and a GTIN number are assigned. The GTIN number is different if it is a case or is a pallet. I have a table that stores all of the UPC and GTIN numbers available in 3 columns, one for UPC, one for GTIN Case and one for GTIN Pallet (the UPC is a standard 12 digit and the GTINs are 14 digit -with the first 2 different to designate pallet or case. and all are based on the check digit formula necessary)
The user assigns the UPC and correct GTIN number by clicking a button which applies the next available UPC code to the package and determines if the description of the type (case or pallet) and inserts correct GTIN number into that field. I actually have all of this functioning correctly.
Now the problem. If a user changes the package type, and therefore changes the description, I need to add code to the update event of the combo box that gives the choices for package type that does the following:
Check to see if the original package type was a case or pallet (it's description) and if by changing the package type it is now changed to the other, update the record in the main table to the correct GTIN number based on the existing UPC Code.
OR as I write this, maybe the code could simply update the main table with the correct GTIN code based on the new description and the existing UPC code. This was I would not need to check for a change just do the update every time.
Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:
Categories Table: CategoryID (PK) Description
Users Table: UserID (PK) Username CategoryID (FK on Categories.CategoryID)
Jobs Table: JobID (PK) CategoryID (FK on Categories.CategoryID)
JobDetails Table: JobID (FK on Jobs.JobID) UserID (FK on Users.UserID)
Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:
SELECT Users.ID, Users.Username, Users.CategoryID FROM Users, Jobs WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));
All the tables are linked with relationships but my SQL isnt so hot! Any ideas as to how i would do this and get it working?
Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).
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.
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.
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.
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:
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.
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?
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??
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
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!!!!!!!!!!!!
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?
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.
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. :)
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.
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?
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?
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?
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)
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???
