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.
PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1
I have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.
When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.
Hi all,
I had table with following data
Table
f1 f2 f3
1 10 AB
1 11 WW
2 10 DD
2 11 DD
f3 is memo field
I had to retrieve data by grouping records based on f1 value
and i had to display f3 value as "-" if all f3 values for a f1 are distinct.
If all f3 values contains the same value then i should display that value.
I should get output like this
f1 f3
1 -
2 DD
I tried a lot by taking the distinct count of the records and displaying - when count is > 1
but i am not able to succeed.
please any one give the solution.
waiting for your help.
Thanks
Hi, Wish if some one could help me ASAP.
I have a table which contains name, tel, email
i need to import only records which have distinct email.
I do need need to import data of all three fields but only which has distinct email.
As there are number of record which are duplicate.
They have different names but same email.
So i need to condition only for distinct email but dump the data in a new table with all three records.
so same names can have different email.
but same email can't have duplicate email.
So need only records which have distinct email.
Please help .......
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.
After testing it, the database got corrupted. I had backed it up just prior to using this.
It was something like = Sum(Abs[AmountPaid], [Paid] = "X")
I have a continuous subform with an "AmountPaid" column. The total is displayed in the subform footer. I need it to display the total for only the fields with an X in them denoting that they were paid. This total should match the statement we receive.
After clicking the button to put the X in the Paid field, then I used the formula to update the AmountPaid field.
Joe..........10..... X
Al..............5.....X
Flo.......... 25
.....Total = 15
What query would I use to build a date in m/d/yyyy format using month, day and year values from another field?
View 2 Replies View RelatedI have 2 tables like this
TableA
FromDate
ToDate
BatchType
TotalBatches
TableB
Date
BatchType
NumBatches
Is their a way to update the TotalBatches in TableA with the sum of NumBatches from tableB that have the same BatchType and falls between the FromDate and ToDate of TableA?
I have a table period with three columns
ID, Period_Start_Year, Period_End_Year
I could have many periods in a future. What i want is to allow users to insert just first start year and lenght of period and table will be automatically updated based on these two values.
So user will insert Period_Start_Year and Length of Period, for example 2010 and 2 and table should be>
ID Period_Start_Year Period_End_Year
1 2010 2011
2 2012 2013
3 2014 2015
While i am writing this i realised that user have to specify also ending year, i do not want to have infinity.. I am thinking about form and update query but can't get it...
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.
View 5 Replies View RelatedI am currently trying to make a form which will generate a report for the users. Currently the form has 3 combo boxes where users select which fields they want displayed from the table in the report. They also have sorting options next to them (Asc or Desc). I am now trying to put in a multi select list box under each combo box to allow users to filter the report to their liking. Currently I am trying to pull distinct values from the specific field of the table and display them in the listbox for users to select.
I have tried using vba to set me.filter1.rowsource = SELECT DISTINCT me.combo1 FROM EVAP_Database, but this doesnt seem to work. I have also tried a few other codes and still no luck.
Table: EVAP Database
Combo: Combo1
Filter Listbox: Filter1
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.
Kevin S. Jones
I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.
I would like to update two fields [Category] and [ProdType] in tblAccum based on a reference table.
The reference table is tblReference and contains the fields [Code], [Category] and [ProdType].
tblReference example of field values:
Code Category ProdType
A Blank Accessory
BS Blank Blank Stock
O Printed Offset
So if the Code field in tblAccum has a value of O then based on the tblReference table the Category value would be Printed and the ProdType value would be Offset.
Any help is greatly appreciated.
Thanks,
I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.
I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.
I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.
I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"
I'd attach my version ....
I have a field in a table that is to be concatenated from two other fields, PolPrefix and PolNum. On a form I got it to show the full field 'PolicyNumber' by making the control source =[PolPrefix] & [PolNum].
But this doesn't update the field PolicyNumber in the respective table, and only shows it on the form. How do I get a concatenated field defined by the user into a table so that I can call that field other places in the database?
Ok. I've got a table named SIPATable and some of the fields contain summed values. Let's say for instance I have a field named "GaDRaw", which represents a raw score. now...I need to update a field called "GaDPer", based upon the value in "GaDRaw". So...If "GaDRaw" <=16, then I would want "GaDPer" to be updated to = 35. Further, if "GaDRaw" = 17. then I want "GaDPer" to = 37. and so on....
I think it's an If Then Else statement, but not sure how or where to place it. Id like it to be as code under a command button, and update the table all at once.
Any help would be appreciated.
Thanks in advance,
Freud52
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
I have a table of data which includes a date field and also various other fields which may or may not be filled with data.I'm trying to populate a listbox with the months for which this data is missing (a separate macro will then loop through these months to fill the missing data) But I only want each distinct month to populate the listbox - not each individual date.
Code:
strSQL = "SELECT DISTINCT month([EntryDate]), year([EntryDate]) FROM [SampleTable] WHERE [ValueField] Is Null"
Me.lstSampleListBox.RowSource = strSQL
I want to return the month in <mmm yyyy> format.
Please see attachments.
POST.zip (384.0 KB)
database.zip (58.8 KB)
I have a simple MyAddress Project. It consists of one main table, a couple of queries, some forms and reports. Most fields in the table are typical text fields. The project allows me to select records from the table and print labels for those selected records. One field is a "yes/no" field. If the field value is true, the label prints; otherwise it does not. I have a form which allows me to scan the list of records and mark those I wish to print. So far so good. I am trying to add two buttons to the form to enable me to 1. Clear All and 2. Check All. I am trying to use an UPDATE statement to activate when I click the appropriate button. However, when I execute, I get the error message "Compile error: Sub or Function not defined".
The name of the table is tblMyAddresses.The firld I am trying to UPDATE is PrtLbl.The update value will be true or false depending on the button clicked. Since I want to change the values in all records, there is no WHERE required. The code I am trying is as follows:
Option Compare Database
Public Sub CheckAll_Click()
Update tblmyaddresses
Set PrtLbl = False
End Sub
I'm trying to update one table's field, via a Form, with certain data from another existing table in my DB when I enter key data in this first form. Example:
Table Equipment ... Some columns... Year, Make, Model, LIcPlate, etc.
Table Fuel ... Some columns... Year, Make, Model, LicPlate, Fuel Dispensed, Milage, etc.
Form for Fuel has Year, Make, Model, LicPlate, Fuel Dispensed, Milage, etc. BTW, it will take Year, Make and Model to fully qualify the search/lookup as there may be more than one occurrence of a Year and Make in the Equipment table, so Model is necessary to fully qualify. . Yes, something like VIN would be a simpler lookup but remembering a VIN is much harder than entering a Year, Make and Model.
In a Form over Table Fuel, I want to have the LicPlate field (possibly other fields as well) automatically updated from Equipment Table when I enter the Year, Make and Model in that form.
I'm assuming the solution involves creating VB code, of some such, via an Event (AfterUpdate) or some such built through the LicPlate field in the Fuel form. A mass Update via SQL is not appropriate.
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;
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.
Can this be done?!
Thanks in advance