I then have various support queries that group customers based on reason for visit, type of visit etc. I need to be able to link this to my main table so that each customer has a category against them. The issue I have is that some customers will have more than one visit ref, which can also mean they have multiple reason for visits. I need to make sure that when I link my queries back to the main table that the right category appears against the customer and their correct visit ref.
Hi Guys, Haven't done anything with Access since I finished uni and I can't remember much. I'm trying to set up a simple database with customer recording and order taking functionality. Just in the very early stages trying to get basic things working.
I have a form where you enter customer details..at the bottom there is a command button named "Place Order", which will be clicked to go to the order form for that customer. Attachement shows very simplified version of the order form. I need to enter a Product ID, and have the Product Name automatically extracted from the Products table.
How can I do this? I would have done a search...but not even sure what this process is called in Access. Thanks for any help.
I have a table called tbl Meeting Response. It records the data about churches where I have scheduled a representative. Two of the fields in the tbl Meeting Response are 1. Normal Attendance (how many the church normally runs) and 2. Tour Year (the year we were there). Reps have been scheduled at many of these churches more than once over the years, so there are records for different years for the same church in the Meeting Response table.
I want to create a query that will show a field with the most recent normal attendance from the Meeting Response table. This would require comparing the information between two different fields -- Normal Attendance and Tour Year. 1. If a rep was at that church in 2014, 2013 and 2012, and we have a Normal Attendance number for all 3 years, I want the field to show the attendance submitted in 2014 (the most recent). However, if the rep didn't call in a normal attendance in 2014, and the most recent attendance we have is 2012 (i.e., the Normal Attendance is blank for both 2014 and 2013), then I want that field to show the attendance for 2012 (the most recent).
Any way to query duplicate data from two different fields from two different tables in the same access 2010 project. I first quried the first part since it combines the first 3 columns to create another value (i.e. 52 & 60 = 5260).
So I created the concatenative value but now I have to compare to another field to display what results are found in both the concatenative and the other (APN in the file). I tried using query wizard but it is for only one field. I understand you have to use joins but the destination filed is what gets me.
I have a table in a database where the telephone numbers are in two separate fields, i.e. [123] (which is the areacode) and [456-7899]. Is there a way to take the two fields in this table and put combine these two numbers into one field so the new field will be in this format.... [123-456-7899]?
I have a query that pulls data from the following fields in 2 different tables:
Area1FloorPrep (tblFloorPrep) ex. remove ceramic tile Area1Size (tblInstallationAreas) ex. 20 s/f, or just 20 Area2FloorPrep (tblFloorPrep) Area2Size (tblInstallationAreas)....
All the way to Area20 (Floorprep and InstallationAreas) for both tables. I have created an installer invoicing form that pulls the data from the workorder that these fields are located in, but the problem I'm having is that I don't know what kind of query to create to concatenate the data in the 20 fields and concatenate the size of the areas next to the appropriate concatenated floor prep description. Is there a way to do this without coding?
I am trying to create a table for an assignment on normalization. It is similar to the problem here : http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=88
I am trying to make it so that if a name appears in the Spouse, Child 1,2 or 3 column it will automatically appear in a dependants database with the EmpID and the Name of the Dependants, as separate objects so that the EmpID could be repeated. I have scoured info and I can't seem to make it work. Any help would be appreciated.
I was wondering if there is a way to link one field from a table to another table?
I have a main table that all of the data goes to and then a second table that the completed data goes to. I have made a query to show info from the main table and one field from the second table and it will only pull the info that is on both tables...i want it to tell me the info that is not on the second table:confused:
Sorry if this is confusing, but any help would be appreciated...thank you Cori
I am fairly new to Access and i am trying to create a database that stores member's details and records the payments that they make monthly.
I have 2 tables:
Members:Id number Fname Sname etc
and
Fees: Id number Fname Sname Amt paid Jan Amt paid Feb etc
What i want to happen is that any member's details entered in the members table also appears in the Fees table so that only the amout paid needs to be entered.
All; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
Having a little problem, have searched the forums but have not been able to find an answer.
I have a single db with 3 tables, artist, gallery and arts.
The main form should show the work details for each artist, i.e. each piece of art they have done. The problem I have is I think I have a link or relationship problem as when I try to create a new record in the arts table, I have a combo box which populates the artist name and gallery name and it should then store these details in the arts table along with the rest of the details I enter but it doesn't.
Each table has an autonumber and there is a one-to-one relationship from arts to artist and arts to gallery.
I would like to link 2 fields in a form. If I select data within one field with drop down list it should automatically bring up a name within the 2nd field. How would I be able to do this?
I've managed to lind the subforms to this drop down list which works brilliantly but this is a field within a form that I don't know how to link.
Ok. I have used access before to hold data but have always accessed and used it via asp. I have never actually tried to use the access forms and queries! But for a current project i am having to use it.
So far its been fairly simple but im stuck on one point. I have one table that holds user info and has a foreign key from another table. On my form i want the user to be able to lookup the foreign keyes available. Only i want the lookup to show the description field in the other table not the unique id (primary key) but when they slect the relevant row its obviously need to record the primary key of the other table in the foreign key field in my user info table.
How can this be done? Im not sure if i explained this very well. If anyones unsure what i mean please ask my to clarify.
I have to date fields one called from and the other called to suppose to look like this frm 07/05/05 to 07/05/05 what i want to do is when i type the date in the from field the same date will automatically appear in the to field any ideas.
My question is this: I have a form called Family with a RecID field. When I create a new record on this form the RecID field auto assigns a #. Then the user is supposed to click the command button which brings them to another form called Child. This Child form has the RecID field and another called RecChildID. The RecID field is linked to the same RecID field as form the previous form. But when I enter the child form and start it's linked record the RecID field is not filling in/ copying over the same # as the previous form. How can I get it to update the RecID from the first form to the second form? Thanks!
I have two different tables. Table1 contains information such as the name of a patient, which is the primary key, date of birth etc (fields that will not change). Table2, linked by the subject id, contains information about tests that they may have gone through. A form has been made using these two tables, in which table1 is that main form, and table2 is a subform. I want to add a field in table2 called "CurrentAge", which will use the date of birth specified in table1, to calculate their current age, and would like it to be displayed in the subform.
I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:
PRODUCT table: ProductName Chemical ChemicalAbstract PhysicalState NFPAHealth NFPAFlammability NFPAReactivity
qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.
The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.
In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:
and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.
I have a search form with 12 fields. In my query I use
Code:
Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null
for each field on the search form.
I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.
But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).
The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.
Search Form with Criteria.PNG
Search Query.jpg
Search Results With Missing Entered Criteria(Dont Want These Records Included).jpg
I have a query that pull data from hours worked fields that have values with decimal places. Hours is multiplied to rates to create my amount to charge in my query which is then displayed on my invoice report.
The issue is that when I sum my amount, my subtotal is off by $.01 due to the decimals entered for the hours worked (e.g. hours worked is 1.5*32.75=49.125)
I need the rounding to work in the query and display on my report as $49.13 and the subtotal to reflect it. I have the properties in my table as double with decimal place at 2, and my query and report properties with decimal place at 2 as well. It still isn't working.
I need to create a form to enter data from a survey, and ideally some sections of the form would only be completed when the answer to a first question is "yes". How to do this enabling/disabling of fields for data entry?
I have a query that I made for about five fields, where the criteria is
Like "*" & [Forms]![DATA SEARCH]![Box] & "*"
Where box is the name of the field that I am searching for.
However, some of the fields in my form are left blank, which makes this refuse to give any results when I try to query it with a form. The other problem that I have is that the fields are bland in different parts of the 1,000 some-odd row table, which was imported from MS Excel.
I created a sys admin database and the big problem I am having is linking the hostname field in table 1 to the hostname field in table 2. I have tried making both fields primary keys, I have tried creating a relationship to the two fields between the two tables. I have tried adjusting the join types.
Yet, no matter what I try, the hostnames from table 1 will not populate in table 2. I have even tried adding a new hostname to see if it would populate and it won't.
[General Info] has 2 fields. Name(is a primary Key as well) & Organisation
[Details] has 4 fields. Primary Key, Name, No of products & Type
[Details] is a sub form for [General Info]
It is a one to many relationship. With Name from [General Info] linking to Name from [Details].
I can update via the table directly and [Details] links the name fields automatically. I created a form but the form just cannot register the field automatically and requires the user to type in the Name from [General Info].
I have attached the DB file. Pls help... your help is greatly appreciated!
At the moment i have an exisiting database that was not created by me. It consits of about 7 tables and a main table. Each table has a Primary Key that is a joining field. The Main form is built with subforms and when i enter a number in the primary key filed of the main table it enters that number in all the other primary keys in the other subforms. i have now tried to create another subform to put into the main form but i cannot get this "link" between the primary fields to work. can someone please help.
