Using Boolean 'and' To Update A Table Based On Values In A Link Table
Feb 24, 2008
The attached Access XP file demonstrates my problem. I've included a form to make testing easier.
Each record in the Projects table has one or more linked entries in the Keywordlink table, showing keywords that apply to that record. Each record in Projects has a Yes/No 'Utility' field.
A third table, Keywords, supplies the keywords that the user can apply to records in Projects, using the subform on the main form. The Keywords table also includes a True/False 'Utility' field. I have set this to True for for the first three keywords.
I need a query, a series of queries or some VB code that updates Projects_Utility for all records to True if and only if the record's linked entries in Keywordlink include all of the keywords for which Keywords_Utility is True (a boolean 'and', as opposed to an 'or'). Otherwise, Projects_Utility must be set to False.
In the attached file, with the current settings in the Keyword table, the 'GetSelectedProjects' query should then produce single-row listings for ClientA and ClientF.
I'd appreciate any help you can give me on this. I'm not a programmer, but I can manage a bit of VB code if I have to.
Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.
I have a table (Table 1 let's say) which contains a list of numbers that all have unique ID numbers in another table (Table 2). Is there a way to create a column in Table 1 that will link those unique ID numbers from Table 2 to their corresponding values in Table 1?
What happened was I imported a list from Excel and there are frame numbers which are unique (but I still gave them an ID) and I now realize that I have another table which lists all these products, which all contain this frame number. I would like to replace the frame number with the correct ID number.
I have a table (tblConversions) that I'm using as a base for replacing values in a different table. tblConversions is set up as such and is made up of nearly 100 records:
ID LABEL CODE 1 Ashaway ASH 2 Barrington BAR 3 Bristol BRI 4 Jesse Smith BUR ....
Another table (tblSysItemLoc) has nearly 1,000 records with a field for Location Names such as Ashaway New Books, Ashaway Fiction, Ashaway Non-Fiction, Barrington DVDs, Barrington Reference, etc. Other fields in this table are just statistics.
What I need to do is loop through tblConversions, and find in tblSysItemLoc where the Location field Starts With the value from tblConversion.LABEL and replace the Entire field with the CODE. For example, from tblSysItemLoc "Ashaway New Books" gets changed to "ASH", "Barrington Reference" gets changed to "BAR", etc.
I feel like I need a loop inside of a loop, but I'm not sure where to begin. Loops are not my specialty.
I'm developing a database for the hospital I work in. One purpose of this is to keep track of patients with temporary invasive devices (there are many types such as urinary catheters, ventilators, etc.), specifically how many days each patient has a device for. Each device is associated with one patient only, but one patient may have many devices. Here's how the associated tables are set up:
1. tblPatients - PatientID (PK), LastName, FirstName, DOB,... 2. tblLocations - LocationID (PK), LocationLabel,.... 3. tblDevices - DeviceID (PK), DeviceType, Device, DeviceDesc 4. tblDeviceUse - DeviceUseID (PK), DeviceID (FK), PatientID (FK), LocationID (FK) (where in the hospital was device inserted, e.g., operating room, bedside, etc.), DeviceStartDate, DeviceEndDate 5. tblDeviceDailyLog - DeviceLogID (PK), DeviceUseID (FK), DeviceDate, PatientLocID (FK) (area in the hospital that patient is in)
All primary keys except for PatientID & LocationID are Autonumbers; and the tables are linked appropriately.
Whenever an entry is made into tblDeviceUse, I want there to be new records to be automatically generated in tblDeviceDailyLog for each date between the Start and End Dates. For example, patient A123 had a urinary catheter from 1/1/2000 to 1/10/2000 that was inserted while the patient was in the ICU, but the patient was moved to the Medical Ward on 1/7/2000. So tblDeviceDailyLog should have 10 new records associated with this device, one for each calendar day, with the appropriate location for each day.
I'm Access 2010 newbie. I need to transfer Excel program into Access.I have a .csv file (data extracted from emails) and a Master Excel sheet. Master Excel fields are updated from the .csv - if the primary keys match, else the new records are added. Also, the .csv contains colour names, which must be translated into corresponding peoples' names.
I'm trying to use VBA to update a new column in a table with info I already have in another table.The table I want to update is an inventory details table, it has around 25,000 records. I added a column called "UnitCost", of course the column is empty for all 25,000 records so I would like to fill it easily using DoCmd.RunSQL "UPDATE" feature.
I use that through-out the program however I'm unable to connect the dots for this one.What it needs to do is update "UnitCost" in "InventoryDetails" from "Products" where "InventoryDetails.ProductNumber" = "Products.ProductNumber"
The "Products" table has all the different unit cost, it just need to be placed in the "InventoryDetails" table for every record. Of course product1 needs products1 unit cost and product2 needs products2 unit cost, etc.
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN) SET TableA.HCC = TableB.HCC WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL ORDER BY TableB.HCC, TableA.CDN;
First Second ---- -------- RollNo Number(PK) RollNo Number Appno Number Appno_1 Number
Now I want to update "Second" table's "Appno_1" with the "Appno" of the "First" table and the "RollNO" of "First" table should match with the "Second" table "RollNo" field.
How i will i do it : Plz Help-----------------------
I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.
I want to prepare a report from the projects table that lists projects IF they have any open tasks.
In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'
I have a table called Inventory (table1 - PK=INVID) that contains all inventory ID numbers and descriptions. I have another table called Inventory Transactions (table2 - FK=INVID) that gets updated through a form. When a new transaction is made in table2, I want the transaction amount to get added or subtracted to an OnHandQty field in table1. I tried having table2 (transaction table) as the main form and then table2 as the subform, but I couldn't get table2 to update.
Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!
I'm making a very basic sales system for my school project and I'm currently working on the stock management part, as I said it's very basic.
My task -When the user receives a stock delivery they need to update the stock numbers. the value they need to update is the StockNumber value in the Products table. to so this I want to make a form that displays all the product names in a table with a textbox or cell next to it in which the user can enter the amount of that item that has been delivered. And at the bottom of the form there is a button which will add all these new values to the existing values.
1. There is no set number of products, the user is able to add new ones, this is preventing me from just making a separate textbox for each existing product
2. I have attached a jpeg I made in paint to illustrate how I want to the form to work as well as my system as it currently stands, there are a few unused forms and queries.
What i need:I have the attached database which includes 2 tables (Table1 and Table2) which are related One-2-Many (Table1 is the One and Table2 the Many)There is one form also with the name "Form1" which includes 11 unbound (ctr1,2,3...11) and are calculated with the values of fields [MainDate] , [Days] and [EndDays]
The Issue: As you can see in the attached sample, on Table1 i can assign the "MainDate" value , the "Days" and on field "EndDays" i can have the value of the [MainDate]+[Days].
I need to update the field "RefNo" on Table1 on depending the date range of the field [EndDays] and the controls [ctr1-11]. Specifically i need : If the Date() is in the range of eg. [EndDate] and [ctr1] i need to have the value of the first record of the Table2 (RefNo) on field "RefNo" into Table1. If the Date() is the range of [EndDate] and [ctr2] i need the second record values of the field [RefNo] into Table2 and so over..
I have the following situation. Table 1: ------------------------ Primary Key Name Secondary key
Table 2: ------------------------ Primary Key Age Secondary Key
***************************** This is just an example. The first table is totally populated. The second table does not have the secondary key assigned, but otherwise is populated. In additon, some of the records in Table 2 do not have a matching entry in table 2. I would like to be able to update table2, populating the Secondary Key field in order to allow me to delete the Primary Key fields. The Primary key was assigned by somone else who created the db, and makes no logical sense, i'm trying to replace it with a key that can be used more easily. Any help would be greatly appreciated.
In my database I have 4 tables for a small painting and decorating business.
These are: tblCustomers, tblOrders, tblStock, tblPayments.
I have a form based on tblOrders to add new orders when they come in from customers.
In this form there are 3 important fields: "CustomerID" (Lookup to tblCustomers), "StockID" (Lookup to tblStock) and Quantity (Entered by user).
However, I want a calculated field that will be worked out automatically when the user is entering these details. The calculated field should go to tblStock and grab the "Cost" field.
However the main problem is specifying what price it will grab from the table. I want it to get the "Cost" where "StockID" = (the stock ID selected in the lookup). I then want to multiply the price by the quantity which the user has entered.
The control source should be worded something like: = ([tblStock]![Cost] where [StockID] = StockID)*Quantity
but that isn't valid.
Could anyone point me to a piece of VBA or some way to get around my problem? Many thanks for any help, Stephen
I am trying to write a query that gets all the telephone hours from a worker done under supervision in a town (qry_svhoursbytown). I have a table that has the workers details, including town (Crisis_support_workers), then another table that has what hours each worker has done (Supervision_Hours). The town is selected from a combo box on the form (frmSearch), under the heading �total hours by location�.
I try and select Bathurst then press recalculate results and in the text box next to supervision hours, it says 66, but if you look in the Supervision_Hours table, there is only 11 hours for the workers that are in Bathurst.
In the query, I have the sum total of the hours field in the Supervision_Hours table, the Town field from the Crisis_support_workers table with the total selected as Where and in that criteria I have [forms]![frmSearch]![ComboTown], then I just have the town field displayed.
What do I need to change to get it to sum correctly?
I have attached the database below so people can take a look.
I have a table with the fields Thermometer_ID (primary key) and CalibrationType (combo box list with the options of InHouse or SendOut)
I want the user to click on a button and have an input box to input the thermometer_id. Then if the CalibrationType is InHouse open form Verification and if it's SendOut open form Thermometers.
In my form's table (tblMain), I've got a lookup field (drop-down list) that lists the primary key field from a different table (tblDiff). tblDiff includes 3 more fields. In my form for tblMain, I want to include 3 more textboxes that get filled up with these 3 fields from tblDiff when the corresponding primary key is selected in the drop-down box.
I have an unbound form in which a user enters data relating to a credit dispute, and when they hit the Save button I have a sub that is intended to insert that data into a table. This is a bit out of my comfort zone, so I found some code from another forum and adjusted it to my needs. However, I'm getting errors.
The table name is tbl_complaints. The fields from the form are:
Private Sub cmdSave_Click() Dim StrSql As String On Error GoTo ErrorHandler StrSql = "INSERT INTO tbl_complaints " _ & "(ACCT_NUMBER, CLIENT_NAME, EOSCAR_TYPE, EOSCAR_CONTROL_NUMBER, METHOD_OF_RECEIPT,
[Code] ....
The error I get is error number 3075, and it says I'm missing an operator in the section that references cboResponseType, but I can't seem to figure out what I'm missing. My head is spinning from staring at these parentheses and apostrophes for so long.
This is quite a basic question and I'm sure that it has been posted here some time ago, although I couldn't find it on using the search.
I want to update a field in one table "FieldA" using an update query. I want the update to be based on matching Field B in the updated table, and Field C in a separate table (both tables have the same design structure).
That is, I'd like my query to search each record in the Destination Table to see if its Field B matches a Field C in a separate Table. If there is a match, I'd like to Update Field A to the corresponding field A of the reference table.
I have tried to do this with an Update Query based on both Tables in design view. Thus far, it hasn't yielded any results.
Can someone please post the solution asap. Thanks so much for your help.
