I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.
I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.
select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"
If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.
I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).
I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.
Table: Employee Field: Name Form: frmUpdateEmployees Queries: 16 that depend on the table and field above.
I have a database in which we are trying to have a field "Headcount" automatically fill with the correct value based on information in multiple fields. The "Headcount" field will be based on about 6 or 7 rules. Most of the rules i can figure out, but im having trouble figuring out what the best way would be to search for a single individual's projects when they are on more than one project at a time. I thought about using a counter of sorts to count how many projects someone was one, but how do I pick them out of the entire table once i have found out the number of projects? Would a for loop work best or just many if statements?
I don't have any database experience whatsoever so please go easy. I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device". The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
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.
how to Add/Update record including existence of record based on single unique field.Now I trying to learn how to check existence of record on multiple field before adding (at least two) and how to update the record with multiple field record
Below is my testing table fields
Year Month Working Days 2013 Jan 20 2013 Feb 17 2013 Mar 22
Now two situations adding a new record and updating the existing record Which means the unique fields are (Year + Month)
Hey guys- I have 2 identical tables. I want to update the data from Table1 to go into Table2. Each table has well over 70+ fields in them. Instead of handwriting out each [Table].[Field] in either SQL or the Designer- is there a shortcut to tell Access to grab all the fields from Table1 and update all the fields in Table2 (all the fields have the same name)? I just don't have the energy to type it all out- I figure there's got to be a way...
I know when you do an APPEND query in the designer- it will do this for you- but not the UPDATE query... Thanks!
I'm building a customer form using a list box of summary customer info on the left side of the form (CustId and Names) and customer details on the right hand side. I want the details shown on the right to be those for the customer selected in the list box on the left. I'm having problems getting the details to update when a different record in the list box is selected or clicked.
I originally set the data source for the detail records to the base customer table but couldn't work out how to update them when the list box was clicked!
I then tried creating a query with the select clause conditional on the list box (CustId = Me.qryContactDetails) and set the data control source for the detail record to the query (=[qryContactDetails]![FirstName]). The query works as expected but the field in the form just shows '#Name?'.
I tried adding a field requery on the List31_Click() event using Me.[FieldName].ReQuery but that didn't seem to change anything
I'm trying to build a query that uses the information pulled from controls on a form called "CharacterCreation" - 2 of the controls are "Race" and "Class", and the form stores this data in a "Characters" table.The query needs to refer to the Characters table, and another table called "Modifiers".In this second table I have several numerical fields such as hp, str, etc, and I have two other fields called "Type_Modifier" and "Type" Among the Type_Modifiers are Race and Class, where their corresponding "Type" could be for instance Demon and Magician..
So the idea is that when I mark on the form (and thus creating the record in the first table) a Race of "Demon", and a class of "Magician" I want the query to refer to both of these fields, and sum the values in the second table where the "Type" is either "Demon" or "Magician" or whatever I decided to choose. I feel like I should be able to figure this out but I'm having a hard time..Here's an example of the SQL I've tried that shows nothing.
Code: SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race) GROUP BY Characters.Char_Name, Modifiers.Type HAVING (((Modifiers.Type)=[Characters].[Race]));
I also tried this, which I thought would work but it displays nothing as well.
Code: SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race) GROUP BY Characters.Char_Name, [Type]=[Characters].[Race];
I am trying to setup a database for vehicle stock control. Im not sure if I have gone about this the right way as I am new to this but thus far it is working correctly except for one annoying problem. The database consists of so far
In the Vehicle Details table a stock number has to be manually added as this will be used for new stock as well as current stock (Number range from 100 - whatever) "IAWVehicleNo". This table contains all relevent data with reguards to make, model, bodytype, color etc. The sellers table contains the details of the seller Name, address, Phone, LicenceNo etc. The Buyers table contains employee names. The States table contains all Australian states. The Status table contains current vehicle status Retail, Wholesale, Wrecking etc. The Sold table contains the details of the person who purchased the vehicle if sold. The Safety details table contains a safety checklist for pre purchase inspections eg: Headlights yes/no checkbox, Headlight text field for any extra info. It also has a field for a safety Certificate No once the vehicle is checked and recieves a Safety cert. The forms are setup as frmIAWVehicleDetails frmIAWSellers Subform frmIAWVehicleSafetyDetails frmIAWVehicleSafetyDetails Subform
The Vehicle Details,Sellers,Sold & SafetyDetails tables all have the IAWVehicleID & IAWVehicleNo Fields but when the details are entered through the forms the IAWVehicleNo which is the manually entered number only updates to the tblIAWVehiclesDetails but the other IAWVehicleNo fields in the other tables remain blank. Any advice or help would be greatly appreciated.
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.
Access2007- Building a query to handle future input...
I run an update query that does what it is supposed to do and gives the proper values.
THEN I add a one more field to the source with the builder using + that has no data in it yet, the query fails and gives blanks, even though one of the fields in the source has data in it.
I would think that the "no data" field would be considered 0.00 value and be added together to the field that has values giving at least the value of the field with values as a result of the query.
Is there a setting somewhere that I need to change so that the no value and the value would be combined? I am using the builder to combine the values in the query.
how or what function (DLookup) should I use to prevent duplicate records based on multiple fields? I want to look at data in three fields that can't match existing data in those three fields. It's ok if one or two of the fields match but not all three.
I've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.
It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.
However I want to avoid this error of possible duplication of entry.
Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.
I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.
I want to create a parametric search for tools on the shop floor based on multiple fields [using VBA]
I've had a good search and turned up lots of useful things; but each example insofar has been fairly specific as opposed to a guide on how to implement it in a wider setting.
Let us say, for example, that my database has three fields each with a bound combo box: location; house-type; number of bedrooms
As I type in location, I want it to filter my results in the other boxes to that location (should be a simple case of applying a filter?) - which leaves me with all the house-types and bedroom-counts in that location.
As I type in the house-type, I want it to then narrow down the search again to filter the "location"+"house-type" to leave me with the available number of bedrooms.
What is the best way to implement this, given that I have about 20 fields*, some of which will inevitably be blank: as the user types in (or selects using combo boxes) the data required, it narrows down the search - and finally there's a "search all" button which returns all results matching the current criteria.
When I say "implement" I mean what underlying structure should I use. Is it best to create a query and update the query as items are entered, or would it be better to filter the form that's having the data entered?
The fields are organised sensibly into sub-forms for various characteristics (i.e manufacturing data, materials data, cutting data, etc, etc), don't worry - it's note a huge mess - and the code is all there to pull the data together as required!
I have a table that has been converted into a form for an input. Now suppose I have two columns. Say I enter value in first column. Now the second column is a lookup to a data from another table.
What I want is to see only those data in 2nd Column which have the value filled in Column 1. This is should be in run time mode. I fill in the value and then in the 2nd column I directly get to see only common values.
I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form
Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.
I know I am duplicating the data by having customer name in both tables which is bad database design!
I have a report which I would like to apply conditional formatting on multiple fields. I would like the conditional formatting to be based on two types of criteria
First criteria would be contracts that start with the year 2014. I used the following expression which worked - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Joseph Steinbok" or "Adam Godson")
To this expression I would also like to highlight fields which contracts start with 2014 AND have a particular Director assigned to it. For this I used the following expression - And [DirectorInCharge]="Name"
On their own, both expressions are working but I want to combine them. How do I do this? I've tried the following - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Name") but then nothing is highlighted. I also tried InStr(1,[ContractNr],"2014") AND [DirectorInCharge]= "Name" - in this instance EVERY record was highlighted.
I have a database that contains details of payments returned to a bank account without sufficient information to identify who the money belongs to.
The DB is used by a team of people to retrieve details of an individual entry and when they have figured out who the money should be sent to they can mark that the payment has been "located". We can only send the refund at certain times of the month, so there is another activity "applied" where we have entered details on internal systems for the refund to be processed. Finally, once a month we physically transfer the money out of the bank account and add a "completed" date to all entries that were "applied" before that date.
My manager would like to be able to look back at a certain date to see what the balance was so he can make charts and whatever else in Excel. The balance will be all payments that were not marked as "completed" on that particular date.
I'm having trouble making a query that will give him the balance for each day in the past.
The important tables/fields here are
TBL_MAIN - dateReceived (date) and amountRefunded (currency)
TBL_DATES - activityDate - just a list of dates, which I use for a similar query of the amount we located/applied each day
So I'm looking for a query that will list all dates in tbl_dates against the sum of amountReceived up until that date, with the sum of amountReceived where completedDate is less than the date listed subtracted.
