Currently, we have a table which contains a field for each type of training that can be taken (FUT1, FUT2, ...). Once an Instructor has taken one seminar, the table is populated with the date taken, via their employee number (the key) through an update form.
Now, the company wants the paper training reports to turn into a digital part of this database. As envisioned, the form and associated table will have combo boxes with pre-determined training types (FUT1, FUT2, ...). The date would be typed in.
The problem I see is getting the dates and training type from the future table to populate the existing table in the correct field for the correct Instructor.
In addition, there exists a report which prints out the training dates for each Instructor at each school. This is will still be in use.
Hello, I would like to know how to pass a value from one table to another within the same db based on a matching username. I need to update a column called 'store' in one table with the values of another column called 'value' from a different table. Thanks for any help in advance.
I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)
Tables: * Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player) * Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box) * Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box * Venue - similar to Competition table * Opponent - Similar to above two tables * Match - MatchID, Competition, Venue, etc (form corresponding to table attached)
Forms: * Player form * Match form
Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).
I got one database, consisting basically in two tables, one for "customers" (basic data, name, place and date of birth and so on) and other for "customers' orders". To avoid duplicated customers' "cards" i put on first table name's field indexed value yes (no duplicates).The problem is in the "customers' orders" table. I need to avoid anyone using the database inserting new "customers' orders" if the customer is not yet in the "customers" table (obvious) or by misspelling customer's name, to lose that order (the primary function of the database is to access to the list of all orders of a specific customer).
I thought in create a validation rule that deny any value in name field of "customers' orders" table if it is not already inserted in name field of "customers" table, but seems that validation rules doesnt work between tables, is that right?I also thought in create a query with names of "customers" table, and use it as row source in name field of "customers orders", and giving value yes to Limit to List property. This one seems to work, but i'm only testing it with few values, the database will grow to thousands of customers and i'm afraid that that rig will not work then. Also, is a bit annoying that with this solution, autocomplete is always present.
I have this database where I want to change the field size from 9 to 4 but I want to keep the last 4 digits not the first 4 digits. Is there a way to make that change?
I have "LossForm" to record loss of inventory items due to damage, theft, etc. It has "Loss Subform" for input of multiple items. The row has a calculated field "TotalLoss" (from qty * itemcost). The footer of subform has unbound text field =Sum(nz([TotalLoss])). This all works fine. The problem I have is that I need to pass the total to another form. I want to have a pop-up form to use some of the field values from the Loss form. I have been able to pass all of the field values except for the TotalLoss.
pop up form: Set frmPrevious = Screen.ActiveForm Me.TransactionID = frmPrevious.LossID Me.EntryType = "Loss" Me.Date = frmPrevious.LossDate { Me.Amount = frmPrevious.TotalLoss doesn't work } DoCmd.Save End Sub
I also tried to setup a global, class, and module variable but keep getting error message of undefined variable.
How to set up my tables as I'm just starting off with setting my database up.
I'm doing a simple database to track the purchase orders (PO) I am managing. Each PO has a PO Number and an Original Value. POs may have multiple amendments which would change the PO value. I would however like to keep the history of the PO original value and all different amendments.
So I created two tables:
tblPO: ID PO Number (Number) PO Original Value (Currency) PO Sum of Amendments (???????????) PO Current Value (Calculated = PO Original - PO Sum of Amendments)
tblPOAmendments: ID PO (Lookup from tblPO) PO Amended Value (Currency) Amendment Date (Date/Time) Amendment Desc (Text)
Now the two, million dollar questions are: 1.) Is this the right table structure to use. 2.) How do I go about calculating the Sum of Amendments field?
I have a table that stores information for multiple behavioral surveys (numerical values). My goal is to add the proper fields that compose total scores value for each respective survey (do a summation of scores). Now, under design view for my table, I see that I can add a calculated field. When I create this calculated field, I can use the Expression Builder to do a sum of the proper fields (the fields that compose a total score for a survey). The only problem that I'm encountering is that if a field that is part of a survey is missing information, the summation disregards the rest of the values for that survey.
How can I account for these missing values so that, if 1 out of my 9 fields have information, I will still get a summation score for the 9 fields? I want to be able to do this without having to change the value of the missing field to 0.
In SPSS I can easily do this by computing a variable and using a code like this: SUM.2(field1, field2, field3, etc.)
I have a button on a subform that becomes visible if there is no records in the source of the subform. When clicked I want to run a query that will insert a record on to the source of the subform. There is one field in the query that I need to get from the parent form.The first part works OK - the button is visible when the source file to the subform for this main form record, is empty.
If I run the query against the source file it inserts the new record after it has asked for the value of the variable field.My problem is that when I try to run the query when the button is clicked It can't find form![ClientFileFrm]![ClientId]..This is my code on the subform
Private Sub Form_Load() If Me.RecordSource <> "" Then If Me.Recordset.RecordCount = 0 Then Me.AddSettingsButton.Visible = True
[code]...
The ClientFileFrm is the main form.I can't seem to reference the clientId variable back to the main form.
Hi. Please do not lecture me on database normalization, as this truly is not in my control.
I have 2 dozen tables, each with 13 fields. All of the field names exist in at least 12 of the tables. And all tables share a ssn field where values are common.(Confused yet? Sorry, if you are...)
If I design a query showing all 2 dozen tables and their fields... Can I set up a query criteria where I enter the requested ssn and then the required field name and have the result show the values of all the fields with that name, among the ones of the 2 dozen tables where that field name exists? (for that specified ssn)
If I am only as clear as mud, please let me know, and I will try again. Or, by asking if I can search for field values querying by field name, am I any clearer?
I have a project that in Access. I'll be handling a survey file that will maintain answers per customer. The visit to customer is done monthly in a year, which means that a customer can have 12 visits in a year. My problem is that if I will maintain this project for the next five years or more, I have to maintain another field in my table that will block the duplicate entries.
I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.
Table A Field X Field Y 1 a 1 b 1 c 1 d 2 a 2 b 2 c
I have a bit of a dilemma in my related tables (screen shot of relationships attached).
Basically at the moment I am using a 1-1 relationship between my tables using the primary key of CYID. The CYID is populated using an auto number at the moment but the problem I have is if the tables become out of sync with the numbering.
What I would like to do is when I create a new record in tblClientDetails is create a new record in each corresponding table that is linked and then pass the CYID across to each table. Can anyone offer any assistance or give me any suggestions for better ways of doing this?
I have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?
Access 2010..One organization that we work with provides us with a block of numbers for each of the two types of contract products we order from them; we do order non-contract stuff from them also.The block of numbers are the same (i.e. 20000 to 30000 this year) for each of the two products. This means that each product can have the number 20000, for example. We call this the Tracking Number. If it is one of these products, we need to select the Contract Number.
For all other one off orders we have with them, we assign our own Tracking Number starting with 00001. This Tracking Number cannot duplicate unless it is one of the aforementioned two products.Both the Tracking Number and Contract Number are in the same table. The user selects the Contract Number from a form (connected to the Contract Number table that has all the details on the contract) and the Contract Number is populated in the same table that has the Tracking Number.Each order must have a Tracking Number (no null)..Not all orders need a Contract Number (null okay).The Tracking Number and Contract Number combination cannot duplicate.I tried setting the primary keys to more than one field in the table, but they cannot have null values.
If not... I have been working on Plan B.... an AfterUpdate on the form (either the form or a field... don't know yet) that looks at a query that only has results if there are duplicate values.
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
I am preparing a database application that will be used by one person in an organization. Frequently , (every year or two) the program will be given to another (usually a laptop) user in the organization as that person assumes the task of the program’s operation and responsibilities.
I would like the user to load the program from the installation CD (made with Wise Installation System and SageKey softwares) on each computer each time there is a brand new user and each time a subsequent user takes over. However, each successive user needs to have the data files passed from the last user.
At face value it seems that having a front end/back end arrangement might be the most desirable. Can anyone jump start me on the best way to accomplish this process to EZ data transfer?
i have 4 supplier tables with identical field names but different databases in sql.I want to have them all in one table and only want the information for reference i do not want to edit any of the data.I need to create a new primary key number for the complete table. The data will need to be refreshed as the data comes from MMS Sage looking at company PLsuppliers.
I have tried a linked SQL union view- but this has no primary key.I need to keep the Primary key consistent as the data saved in other tables will use this field.The overall goal is creating a Purchase order system and this list will be my complete supplier list.
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 what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
Is it possible to have the field names in a table be set by the values in another table? The desire being that for a database used in various locations, the local variations could be changed in one table which would then propagate that change throughout all the forms, reports, tables etc.
I inherited a database that has two tables that are structured identical; one called tblcurrentdata and the other called tblpriordata. The user wants a report or export query that only shows records that has differences between prior weeks data and the current weeks data. The tables have 12 fields of data in a record per quote number. The user wants a query or report to identify the QuoteNum and any changes between the prior and current records. The table structures are as follows:
Code:
tblCurrentData/tblPriorData: QuoteNum CoName State ZipcodeNo priorityColor
[code]...
QuoteNum 12345 field 5 (prioritycolor) changed from red in the prior week to green in the current week and field 7(POC) changed from Scott in the prior week to Jonson in the current week.QuoteNum 23451 did not have any changes therefore does not need to be listed in the query/report Unmatched query doesnt work because it does not compare multiple fields. I tried to structure a union query and use <> in each field but got too tedious and didnt give the expected results.
I'm trying to create a table for every record I have in a field. I have two tables, one titled "Experiments" and another titled "Students". I want the new tables generated from the field EXP_NAME from Experiments. I need it to have seven fields, one copied from Students, five labeled Trial1 thru Trial5, and a final for a grade.
i have one table in which ID is Primary ID with Different Values
Like
ID NAME PAN 1 A X 1 B Y 1 A X 2 C Z 2 C G 3 D U
it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.
I have what appears to be a simple puzzle. This is a simplified version of the real database. The purpose of the database is to act as a simple calculator - in this case to find the cost of a journey in a specified car to a specified city. There are 4 tables. Table1(input Data)with 2 fields: Car and City. Table2(City),with fields City and Distance where city is the primary key. Table3(mpg), with car and mpg where car is the primary key.
A query is written that when the user enters a "car" and "city" in Table 1 it looks up the distance to the city and the miles per gallon for the car. The Query is: SELECT [Table1(Input data)].City, [Table2(City)].Distance, [Table1(Input data)].Car, [Table3(mpg)].mpg FROM ([Table2(City)] INNER JOIN [Table1(Input data)] ON [Table2(City)].City = [Table1(Input data)].City) INNER JOIN [Table3(mpg)] ON [Table1(Input data)].Car = [Table3(mpg)].Car;
This works perfectly. Table 4(cost) : contains 3 fields Distance, mpg, and cost with both distance and mpg as primary keys. How can Write a query that when the user enters the city and car, the distance and mpg are looked in table 2 and 3 and the cost is looked up in table 4. I have tried the following without success.
SELECT [Table1 Query].City, [Table1 Query].Distance, [Table1 Query].mpg, [Table1 Query].Car, [Table4(Cost)].[Journey's cost] FROM [Table1 Query] INNER JOIN [Table4(Cost)] ON ([Table1 Query].Distance = [Table4(Cost)].Distance) AND ([Table1 Query].Car = [Table4(Cost)].MPG);
The problem always arises when information from 2 separate tables is needed to lookup a third table in a query. This is essentially the hub of the puzzle. It can occur so often in database design that I would be surprised if it is not amenable to very simple solution. I constructed the posted database as a simple example of the problem. The solution must encompass the user being able to enter the Car and City and using the lookup tables the query should return the Journey cost. The key might be in the way the tables are linked. Many thanks in advance for trying.
