Im currently constructing a basic search form, which currently works. I am now trying to make the form more specified. Basically one button will give you the form that searches all records of a table. Im now working on a second button that will do the same thing, but will only show the records that the current user is present in.
The table i wish to search/sort is Changes, and there are 3 areas that a user can be assigned to a change, they can be the initiator, they can have one of the tasks of that change assigned to them, or one of the impacts assigned to them.
Initiator is part of the Change table, so thats ok. The problem is that Tasks and Impacts are subforms, and thus are stored on different tables.
Im trying to find a way to build a SQL query which will display the Change records where the current user (username stored in a global variable) is either the Initiator, has a Task assigned, or has an Impact assigned
This SQL string will then be set as the searchforms recordsource.
i've tried using DLookups but it returns an error (Syntax error (missing operator) in query expression '[Initiator] = Test User'
this is the code im trying atm
strSQL = "SELECT DISTINCT * FROM tbl_Change"
strSQL = strSQL & " WHERE tbl_Change.ChangeNumber = " & DLookup("ChangeNumber", "tbl_Change", "[Initiator] =" & UserName)
strSQL = strSQL & " OR WHERE tbl_Change.ChangeNumber =" & DLookup("ChangeNumber", "tbl_Tasks", "[Who] = '" & [UserName] & "'")
strSQL = strSQL & " OR WHERE tbl_Change.ChangeNumber =" & DLookup("ChangeNumber", "tbl_Secondary_Impacts", "[Who] = '" & [UserName] & "'") & ";"
*the inconsistency in the above code is due to my trying different things
I have a form which is based on a select query that brings together 2 related (one to many)tables. When I keyin data to the form it does not update the corresponing table.
Is it possible for a table to be updated in such a way, or do I have to create a form based solely on the table(s) without using a query?
Hi, I've spend ages reviewing the relationships between my tables and changing my query but cannot get this to work. I've read alot of the posts on the subject also but trying to apply the suggestions to my problem hasn't worked.
It is a database of engineering drawings. A drawing can have one or more revisions. I have written code to import the drawing and revision info into the datebase so when the drawings are actually received I need to be able to add the additional data in tblReceivedDrawings such as the tracking number, date received etc. These are my tables:
I have created a query called qSelDrawingsAndRevisions which combines tblDrawingRegister and tblDrawingRevisions.
The form is based on a query called qSelIncomingDrawings. This query joins qSelDrawingsAndRevisions and tblReceivedDrawings. It shows all the records from qSelDrawingsAndRevisions and related records from tblReceivedDrawings and is joined on the drawingNum and Rev.
Adding the extra info e.g. tracking num, date received etc about drawings that are in tblDrawingsRegister works fine. However if I want to add a completly new drawing I get an error message: "The Microsoft Jet database engine cannot find a record in the table 'tblDrawingsRegister' with key matching field(s) 'qSelDrawingsAndRevisions.DrawingNum'
How can I get it to update the drawingNum in tblDrawingRevisions and tblDrawingsRegister at the same time? Is it an issue with my query or relationships? Or do I need to write an OnUpdate procedure for the DrawingNum textbox so that it created a new record in the DrawingsRegister table to correspond with the one in the DrawingsRevisions table? I have a feeling that this might be overcomplicating it and that I just need to change the query?
Am very confused about how to approach this and would really appreciate a few pointers...
When I started building forms to fill my junction tables, the example that I looked at based them all off of queries. That's what I've done for the most part. However, this latest subform that I've made and embedded into a main form is based off of a table. Will that cause problems for me down the road? I'm not so far with it that I can't redo it off of a query, I just don't want to!
Hi, not sure if this is possible or the most economical approach but here goes:
I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.
I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.
I have a form that users can input data into and based on that data it runs a query and generates a report. These reports can be different based on user entered data on the form. My issue is within the report I would like to sum certain fields. The problem with trying to sum theses fields is that they show up on each row so I have hidden duplicates but when trying to sum the field it still trys to count the hidden duplicates thus giving a value that is of no use.
I have tried many methods to sum but one of the problems I continue to run into when I create a text box and build an equation and reference the field I would like to sum is when the report runs it is asking for a value to be entered for the field I am attempting to sum. I shouldn't need to enter a value as I am trying to obtain the value.
My report produces multiple copies of the same record. I know why, but don't know how to fix it.
EmployeeTable.
With a one to many relationship with TrainingTable (via employee PK as FK in trainingtable).
Training table has a one to many relationship with a table called Range.
Report is based on a query that picks up the Employee/Training/Range (range just describes the training unit).
However, If I have more than one range expressed organized a training unit, the report spits out several copies of the Employee record to display all the ranges.
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I have a parameter query for which the parameter is based on a form field entry (i wanted to avoid the dialog box popping up).
No problems with this....however, I want to create a chart based on the same query however, i get the following error message because it does not recognise the query parameter.
Error Message: The Microsoft Jet database engine does not recognise '[Forms]![frmChooseDTDate]!DateOccured' as a valid field name or expression.
I have two tables say Daily and Master, and now I want to update the Daily table and add two additional fields in this table based on the data from the Master table.
I'm creating an accounting database for my use at work. I order goods and services from a multitude of vendors, using varying payment methods, and at last count, 8 different accounts. The time I'm spending on repetitive entry into excel spreadsheets has forced me to sit down and make an access database to track everything.
Right now, the following: 1) Table named "Accounts" with two fields - Account # and Account Description. Account # is PK. 2) Table labeled "Orders", which I'm going to have the top-level information for each order (order #, Account #, Account description, + others that don't matter here). I have another table for the order details (product #, description, price...). 3) Relationship between "Accounts" and "Orders" is one-to-many, enforced referential integrity, cascaded update.
For my order entry form, I want to enter the account description, so I set up a combobox. But, for my paperwork, I am going to need the account # to print when I do a report, and for future flexibility I want that field to remain in the "Orders" table.
I know my accounts by name, not number, and I want it set up so that when I select, for instance, "Supplies Account" for the description on the form, the Account # field on the Orders table is automatically populated with the account # that correlates to that description.
Is there a way to tell the table to auto-fill this entry?
All; using Access 2010. I need to have some kind of way to track changes based on different field name.I have a client table with field data like Fname, Lname, SSN, Address, Zip, Phone, DateOfBirth, Current Rate, TotalRate, Status and Importdte. I have an import into the database every month and I update this table with data that is new based on the SSN with an append query. The import also has the names of those that exist in the client table until their status changes. In addition to appending new data every month; I would like to append any changes the existing client has. For ex; If an existing client has an address change, I want to append this data to the table but keep the existing address and have a Change date equal to the Importdte. In essence; I want to build a kind of history table that when I pull up a client; I can have something like a tab on a form with the changes and when it happened.
I am working on a dataBase and I need to have it check if a set of tables exist based on the data of another table and if any do not exist create the table(s) based on a template. the caviat is that the data in one table are both names of tables and field in another.
Table1 has fields named 'first name' and 'last name'; the data in that field is for example Peter Adams, John Smith. I need to check on start that the tables named PAdams, JSmith.and so on exist, if not create them from a table template with fields 'vacation', 'personal', 'sick, etc. also i need to check on startup that a table named Department exist; if not, create it with fields named PAdams, JSmith and so on.... is that possible?
I have a number of tables with different metrics. However, they all have a "Date" field in common, broken out at the daily level. Since some of my tables have different start and end dates, I'd like missing values to simply appear as "N/A". How I would do this with SQL?
I've attached a screenshot of the layout I'm going for. Each metric field (i.e. "Metric1", etc.) would be pulled in from a different table.
I have two tables: Focus and Comp..I want to join these two tables based on field: DwId...For this example, I made a Comp table that has 3 DwId's not present in the Focus table.When I run the below statement, I am not getting the 3 records only present in the Comp table in the result.
SELECT Focus.DwId, Focus.PRD_May2012, Focus.PRD_May2012 - Comp.PRD_May2012 AS PRD_DiffMay2012 FROM Focus RIGHT JOIN Comp ON Focus.DwId = Comp.DwId
I have attached a zipped excel workbook to best describe what I'm trying to do. I have table1 and table2 and I'm trying to write a query in access to get the output as shown in the workbook. I'm having hard time getting this right. I would appreciate your help if possible. Tks
I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.
The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.
The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.
Recordset.find seems to be only catering to 1 criteria, not 2.
Ok.. I have been racking my brain for hours trying to figure out how to do this. Maybe someone here can help me figure this out.
I have 2 tables. "AR94" & "RPIN" I need a query to look at the "Provider_PIN" field of both tables in order to find duplicates between that field in both separate tables at which point I need it to say If a duplicate provider_pin exists in the AR94 table & the RPIN table Then remove that provider_pin from the "RPIN" table.
The final result should be that the only records showing in the "RPIN" table would be those provider_pins' that were NOT found in both tables.
My db has several tables tb1, tb2 tb3 tb4 ..... I have link tb1 to tb2 (tb1Id to tb2)and linked tb2 to tb3 and tb4 (tb2Id to tb3 and tb4) I have created a form with several field from tb1 and tb2 and a single field for tb3 and tb4. All of the fields except one for tb1 are use to make a decision based on what they display. The only field that is updated on the form is a date conversion field from tb1. once you update this field it will auto populate a date field on tb1 with current date. you then have a choice of 4 commands to activate based on what is displayed form the rest of the form.
my problem none of the fields can be updated. can any one help
qry the form is based on
tb1 [Name] tb1 [ID] tb1 [date conversion] 'this is the only field that is manualy updated tb1 [date] 'updated base on date conversion field being updated tb2 [field] tb3 [Yes/no] tb4 [yes/no]
based on what is displayed on the form you have 4 choices of cmd buttons.
Ok. Here's the situation. I have query setup. This query has 2 columns: 1. Agent Name 2. Sale Value
I have 2 tables. Table1: Includes Sales for this pay period only Table2: Includes all Sales From past to present.
We are setting up our system on Tiers and Each sale value is going to change. So, Here's what I need: I need to run an update query that first Looks up all the records in Table1 based on Agent Name. The field that needs to be updated is called SaleValue. It needs to be updated with what's in my Query mentioned above. Then, all the values in Table1, need to be copied (or updated) into Table2. This can be done based on the Primary Key (SaleID), which exists in both tables. So I guess we will need to run 2 separate update queries. Any ideas on how this can be done?
I was wondering how to create a form which is using data from three tables, the data in the tables does not have any relationship setup as they are not related to each other. When I use the wizard and I select the three tables, Access starts complaining about the fact that no relationship has been setup...
I would like a query which would look at the difference between TBL_Sales and TBL_Finance and will then add rows to TBL_Sales to make them balance. In the example above I would want it to add the following (I've used C as a Product_Type to show that it's a manually entered value) -
I have a table with an empty column called ActionID. I need to generate a numerical number that begins with 5000 and goes up by 1 number on each saved record. I have a basic form that links all of my other field to the table except the one I need to generate (ActionID) but will need that number saved to the table once its created.
