Get A Field In Table To Be Populated By Field In Related Subtable
Jun 12, 2015
I'm sub-novice when it comes to Access I'm trying to get calculated data in a field in a sub table to autofill in a field in a related table. So all of the data in 'total hours' column is calculated by the variable inputs in the 'hours' fields. Once there, I want it to feed itself into the 'total hours' field on the main table. Is that possible? Also - the 'total hours' field in the sub table won't calculate anything unless a value is placed in EVERY 'hours' field in the same sub table (e.g. 0).
View Replies
ADVERTISEMENT
Nov 3, 2014
I can add to my make table query UDate: Date() that will add a populated field to the output table that has today's date in it.
I can also add something similar like this for example V_Num: [V_Number] and when ran, will prompt with a dialogue box to add some data, it will then create the table with that new field name and populated with the text from the dialogue box.
But how can I automate this to skip the dialogue box and just add it to the expression?
View 3 Replies
View Related
Dec 3, 2007
Hi,
This is my first post. Thank you for reading it. And thank you in advance for your assistance.
I have an invoice table and a payments table. One invoice can have many payments (a deposit, final payment etc). The relationship is created.
I have a form with tabs that hold subforms. One of the tabs contains fields from the Invoice Table (i.e. Invoice Number, Invoice Amount, Total Paid). One other tab has a subform that contacts fields from the Payments table (i.e. Invoice Number, Check Number, Check Amount). I would like to auto-populate the Total Paid field in the Invoice Tab with the SUM of the Check Amount from the Payments tab BY Invoice Number.
This doesnt seem to difficult but I'm having trouble with it. Any assistance would be much appreciated.
Thank you!
View 3 Replies
View Related
Nov 21, 2006
Hello
Could you help me ?
My problem is :
I'm developing a ms database for the a "repair service" .
I created a table witch contains basic data from customer, including its unique ID number (key field), name and contacts ...
Now I need to store the "repair service" data from each costumer ( its time consumption, replaced materials...) in a separate table but, If I'm thinking well, I must have a table for each costumer so, I will need to link each individual "repair service" data table from each costumer to each costumer itself.
Do you know how to do this ?
Lots of thanks in advance,
Miguel
View 4 Replies
View Related
Jun 16, 2005
Hi,
Need some help. Here's the problem:-
I've two fields in a table (A & B)
A B
1 Null
2 Null
3 1
4 2
5 3
6 5
7 Null
8 4
9 6
The fields are somehow related where values of B (unique) is taken from A(unique).
I trying to do an SQL such that if I input a single value A=9 or 6 or 5 or 3 or 1, the following should be the output:
A B
9 6
6 5
5 3
3 1
1 Null
Or in another case where I input a single value A=8 or 4 or 2
the following should be the output:
A B
8 4
4 2
2 Null
How can I do that? Many thanks in advance!! :D
View 3 Replies
View Related
Apr 26, 2012
I have a combobox on my form with fields related to it (ie. when an option is chosen in the drop-down, the next two fields are also populated with data based on the selection made - I know this is duplication of data, it's just the way the 'powers that be' wanted it. I'm changing it in my next version of the database!)
The two fields that are populated as a result of the selection in the combobox are migrating into the related table fine, but the actual selection in the combobox isn't - it's showing up as blank in each new record in the table.
View 4 Replies
View Related
Jun 30, 2015
I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.
How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.
For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.
View 5 Replies
View Related
Aug 7, 2014
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
View 2 Replies
View Related
Apr 19, 2007
Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:
Categories Table:
CategoryID (PK)
Description
Users Table:
UserID (PK)
Username
CategoryID (FK on Categories.CategoryID)
Jobs Table:
JobID (PK)
CategoryID (FK on Categories.CategoryID)
JobDetails Table:
JobID (FK on Jobs.JobID)
UserID (FK on Users.UserID)
Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:
SELECT Users.ID, Users.Username, Users.CategoryID
FROM Users, Jobs
WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));
All the tables are linked with relationships but my SQL isnt so hot!
Any ideas as to how i would do this and get it working?
Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).
Thanks in advance,
Chris
View 2 Replies
View Related
May 22, 2015
I have a number of tables in my database, and they're linked in with a form. I'd like to have a field in a form that's populated automatically based on what's inputted in a different field.For example, I've got one table with names and descriptions on widgets (name in column A, description in column B).
Widget 1 This widget is blue
Widget 2 This widget is green
Widget 3 This widget is yellow
When I'm in my form, I want when I select Widget 1 to have the corresponding text (This widget is blue) appear in a field below.
View 5 Replies
View Related
Aug 5, 2013
i would like to sum up 5 fields and save (or just show it in the form view is enough really) in a seperate field, i have managed to sum the 5 fields, but it summed up all 5 fields in all records, i'd like to have my form show the sum for the record, not the table.
it would be ideal that it would refresh when one of the 5 fields data changes, i.e from 1000 to 2000. again updating in the form view is what is needed.
View 5 Replies
View Related
Sep 4, 2013
I have an order form that auto-populates details from the customer table into adderss and contact controls. But I don't want it to auto-populate when the underlying order table already has data in the related fields. Here is the code I use on Load form event...
======
Private Sub Form_Load()
'Use this version if the ID is a number
Dim x As Variant
Dim strControl As String
Dim lngID As Long
'If parameters exist, use them
If Len(Me.OpenArgs) > 0 Then
[Code] ....
View 2 Replies
View Related
Apr 8, 2013
I am trying to create a query that returns records whether a field has data or not...
There are three fields in question, SSN, DOB (this is a date field), POB (this is a foreign key representing a state in the query shows the actual state). Now unless the criterion is different then I just need the answer for one I can reproduce.
I would like to do this in the criteria box in the query.
The query pulls from one table, some of the employees in this table have the three fields populated some don't. I would like the query to return all employees...
View 2 Replies
View Related
Sep 17, 2014
I am going round in circles here; I have a form which has certain text fields that need to be populated before a user can click a command button named 'Record Movement' that opens up another form. What I want to do is disable the 'Record Movement' button until these fields are populated. I am using Access 2010.
View 6 Replies
View Related
Jun 19, 2013
I have a form, combo box selection, when i make a selection, all the info is good on all my tabs, which holds the subform.My issue is, on one of the subform, it's a data entry form, where I want it to be on a new record page. instead it list all records. I want my users to see a blank page, but have one field populated. is this possible?
1) blank data entry page, but with a populated field?When a CU Name is selected,I want the (Enter Note) tab to go to a data entry or last page or new page.I have tried putting it to data entry form, but the CU Number will not populate.
View 1 Replies
View Related
Sep 2, 2007
In a case where two tables are related, can the field names which relate them be the same? Or is it necessary to use different field names?
Robert
View 4 Replies
View Related
Apr 4, 2008
I have a table that I'm storing Horsepower and Watt in. Is there anyway to set it up so that when I provide Horsepower it automatically populates Watts? (just a simple multiplication)
View 1 Replies
View Related
Apr 14, 2005
Dear All
I am creating a form that is bound to the main table in my database.
However, I want to show one field from another table on the form that is linked to the main table (one-to-many relationship). I would do this using a sub-form but I also want the form to be a continuous form (as this has been asked form from on high) and Access won’t let me have a sub form if the main form is continuous.
Is there any other way that I can show the related field?
View 2 Replies
View Related
Mar 6, 2008
Hi,
Can anyone tell me how I can do the following without using code: I think it's really easy, and am sure I've done it hundreds of times before, but my minds gone blank...
When I select an entry from a combo box in a form, I'd like a related entry to appear in another field on the same form. I have a table with the both fields stored in it, although theres only 3 records so this might not be necessary.
Many thanks,
Nicola
View 1 Replies
View Related
Apr 28, 2014
I have 2 related tables by ID field.
In a subform I have all fields of a one table, when I make click in a button, open other form with info related by these ID.
All is ok but I have some doubts:
If in the second form (second table) there is no registers, how could shows me the ID by default and that the ID field is not empty
If I create a new register inside second form with a button, I would like that the ID field was always filled because the user don't need to know the ID.
View 2 Replies
View Related
Apr 2, 2005
Hi i have a field which is a date/time field and its format is short date which is xx/xx/xxxx. I want to ask if there is any way i can add a validation rule for only the year to be larger than 1980???
View 2 Replies
View Related
Apr 16, 2015
In a form of mine, I have the user input other person's initials, but I don't want those initials showing up on the report. It's a set group of initials to work with so I have each set associated with a random 3 digit number (that I input into the table myself). I'd like to be able to have the person input the initials in the form, but have the numbers display on the report, I'm not sure how to go about doing that.
View 5 Replies
View Related
Jul 10, 2015
How can I include a field from a table in to a query - that has no relationship?
For example...
The 'Despatch' field is in a seperate table called 'Product', because we despatch multiple products for any ONE order at different times, so it needs to be against the Product and not the full job itself.However, when I include the 'Product' table into the query and insert the 'Despatch' field into my query, and run it comes up with an error...
"The SQL Statement could not be executed because it contains ambiguouse outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then iclude that query in your SQL statement". Here's the current SQL statement
Code:
SELECT [Materials Requested].[Job No], [Materials/Orders].Total, Jobs.[Project Name], Product.Despatch
FROM Product, Orders INNER JOIN (((Customer RIGHT JOIN Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]) ON Orders.[Orders ID] = [Materials/Orders].[Orders ID];
View 1 Replies
View Related
Jan 27, 2013
I have a table that lists classes available (tblCourses); one that lists class dates (tblDates) and another that lists employees (tblEmployees). I have a fourth table that combines that data into classes taken by the employee (tblTraining). I have a form to input the employee's training with combobox fields for the COURSETITLE and CLASSDATE. I want the CLASSDATE field to only display the dates available for the specific class that was selected. How do I do that?
View 1 Replies
View Related
Nov 27, 2013
I have a sub table that acts as a revision history for the items in my main table. I've just finished uploading all the records into the main table and now I want to insert one record into the subtable for each record in the main table to start the history with the record creation.
The sub table looks like this:
tblRevisions
- RevID (auto Number / primary Key)
- RevDate (date of revision)
- RevName (Who made the revision)
- RevDesc (What revision was made)
- RevAuthorized (Who authorized the revision)
- RevLabel (Foreign key to the primary table)
The info I want to insert is:
RevID - Auto Number
RevDate - 11/27/2013
RevName - 3
RevDesc - "Added Label to database"
RevAuthorized - 1
RevLabel - (One for each record ID in tblLabels)
I suspect that I want to start with an insert into statement, something along the lines of:
Code:
Insert into tblRevisions (Revdate, RevName, RevDesc, RevAuthorized, RevLabel)
Values ("11/27/2013",3,"Added label to database", 1, ?)
I'm not sure how to indicate that there should be one label for each Record in tblLabels or that revLabel value should match the ID from tblLabels though.
View 6 Replies
View Related
Feb 13, 2014
In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.
Currently my query looks like this;
As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.
View 14 Replies
View Related
