Tables :: Auto Calculating Field In Table

Jul 29, 2015

Currently I have a form with these variables

- Assets
- Shifts
- Machine Offline Date
- Machine Offline Time
- Machine Online Date
- Machine Online Time

In the Asset Table I have recorded on each asset the number of shifts it operates for as well as starting / finishing time of those shift. e.g. 2 shift asset will start at 7:00AM finish at 11:00PM
or 3 shifts asset will start at 7:00AM finish at 6:59AM

Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.

What I have already done is allocate shifts per asset e.g.

when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.

If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11:PM

also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM

These are the scenarios that is entered through a "Form"

Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs

Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.

I would like to make this an automatic calculation, Is this possible?

Please note I am not after a "Query", I need this calculation in the main Database table named "Tbl_MaintenanceDATA".

I understand this is not the norm and many have suggested against this, however for the purpose of what I need, it needs to be in the main table with all the other data.

Here are some snip it
snipit2.png
snipit.png

View Replies


ADVERTISEMENT

Tables :: Auto-populate Table Field From Calculated Field In Another Table?

Jan 15, 2015

I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)

Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.

View 2 Replies View Related

Tables :: Auto Populate Field Based On Answer Of Another Field In Same Table

Feb 28, 2013

Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.

I am not quite sure how to lay this out. I am using Access 2010.

View 8 Replies View Related

Tables :: Auto Populate Field In Table

Feb 15, 2015

I have a table ([AllNames]) that a field ([Past_Employers]) is being auto-populated, and I don't want it to.

[Past_Employers] population is setup form a form, which references a separate table of employer names [Employers].

If I enter a new record directly in the [AllNames] table the [Past_Employers] field populates.

[Past_Employers] is a multi-value field (yes ugh - I know!!!) and is not required.

View 4 Replies View Related

Tables :: Auto Populating A Table Field

May 30, 2013

I have the following tables:
Inquiry (This is the main table I want to populate through the use of a form)
Programs

My question refers to two columns in the Inquiry Table
Program
Group

The program column is populated by a drop down menu that is linked from a programname column in the Programs table.

I I want is the Group column in the Inquiry table to autopopulate based on the selected programname (There is a group column associated in the Programs table) so the form field will be autopopulated.

What I have tried is autopopulating the form (form name is Inquiries) Field called Group by using =[Program].[Column](1) in the source code of the text box. This works well, but it then does not populate the main table.

I need to either
- Learn how to populate the main table column called Group based on the form autopopulating
or
- Learn how to autopopulate the column Group in in the table so the form will autopopulate

View 3 Replies View Related

Tables :: Auto-Calculate Field From Other Table Values

Aug 10, 2015

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?

View 4 Replies View Related

Tables :: Select Table Lookup And Auto Fill In Another Field

Apr 10, 2014

i have a user permission table.that consists of PermissionPK, UserFK, CompanyFK. I also want the username to be automatically filled in?So when a user ID is filled in on the table, it also fills in what that UserID's Username should be?As i need both the UserId and Username text for code that looks at the Environ username.

View 2 Replies View Related

Tables :: Auto Generate Hyperlink In Table Based On Field Name

Aug 29, 2014

I Have a table with 15000 entries. This table lists components we use. This table is used to generate queries/forms. I have been asked to add a hyperlink to this table to link to a drawing of the component. All the drawings are in PDF and in the same folder. I am looking for a way to automatically update the hyperlink fields all at once. The Hyperlink will be in the format of servershareddrawings12345.pdfwhere 12345 is the component name from the table.

But there are a few small problems with the component names.
1. If the component begins with a B- . The drawing name will be all the characters except when there is a second dash in the component. So if the component is B-12345-678 the drawing name will only be B-12345.
2. If The component begins with AB, The drawing will be the first 5 characters regardless of what follows, e.g. AB123.
3. Finally for all other components the full component name will be the drawing name.

Putting the naming to one side, how to go about auto generating the hyperlink. I have posted this in the table section, but maybe this should be in another section like queries or VBA.

View 2 Replies View Related

Tables :: Resetting Auto-numbering Field In A Linked Table

Sep 21, 2013

I have created quite a substantial and effective database for a small gliding club with all the major data tables being linked. I need to be able to re-index tables periodically so that running totals, which are needed to calculate statement balances, works correctly - all well and good. The only problem is that the process of re-indexing requires the data in the main table to be stored temporarily and the original data deleted. When the temporary data is appended to the main table the auto-numbering just keeps clocking up.how to reset the auto-numbering in a local table.

View 3 Replies View Related

Tables :: Auto-populate By Matching Data To Another Table And Selecting Corresponding Field

Sep 16, 2013

I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields

Purchase Currency
Exchange Rate

I also have another table called Exchange Rates 13/14 within which there are 2 fields

Currency
Exchange Rate

When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:

SQL Tried

1.
UPDATE Costs
SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency

2.
UPDATE
Costs
SET
ExchangeRate = [Exchange Rate]
FROM
[Exchange Rates 13/14]
INNER JOIN
[Exchange Rates 13/14]
ON
Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency

3.
SELECT [Exchange Rate]
FROM [Exchange Rates 13/14]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency

I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.

Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.

View 4 Replies View Related

Calculating Field Specific Averages From Records In Separate Tables

Apr 28, 2014

Let's say I have a table sort of like this one: [URL] .... (Table 1)

What I want to do is make another table that references the first table: [URL] ... (Table 2)

I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.

View 1 Replies View Related

Tables :: Auto Populating A Field Based On Selection Of Other Field

Jun 27, 2014

I (will have) a form which a user fills in to enter new data.

I have two fields,

"Branch" and "Branch ID" (Branch ID will not be on the form, just in the table)

Branch will be fed by a combo box with seven choices. I would like it so that when "Branch" is populated it autopopulates Branch ID with a code which relates to the branch, so for example

BRANCH BRANCH ID
Braintree BRA
Colchester COL

View 4 Replies View Related

Queries :: Calculating Membership Fee From A Field In Another Table?

May 8, 2013

I've made a membership database for an imaginary leisure centre as part of my A Level coursework - only after more or less finishing my project, I've realised that I haven't provided a way for the end users to calculate fees for members.

I suppose the calculation I would have to do is multiply the Length of Membership (days) field on the Membership Opportunities by Cost per Month on the MembershipTypes table.

These are the relevant tables and I've also attached my database (the password is "password" for any of the users) ...

View 1 Replies View Related

Tables :: How To Auto Populate Field 2 From Key Field

Oct 17, 2012

I need a continuity field...transaction number..that can be both a parent and child key. Would like to create this upon accessing a new record / row.

View 3 Replies View Related

Tables :: ID Field - Restart Auto Numbering

Mar 1, 2013

My ID field is an auto-assigned number. Is there any way to restart the auto-numbering at number 1?

View 14 Replies View Related

Tables :: Reset Auto-number Field

Apr 10, 2014

I have a table which will be completely emptied and refilled. The table has a field autonumbered it is also the primary key. When the table is refilled I want this field to start from 1. How can I do that. Yes some people will say this subject has been discussed search for it. But here the issue is somewhat different we have an existing field and I do not want to compact the database each time the procedure runs.

View 1 Replies View Related

Tables :: Format Auto-number Field

Oct 25, 2012

I would like to format the AutoNumber field so that it shows the current year - #...i.e. 2012-01.

View 4 Replies View Related

Tables :: How To Reset Auto-number Field

Dec 9, 2014

how to reset autonumber field.

View 6 Replies View Related

Forms :: Calculating Fields And Auto Update

Jun 15, 2013

In this table I have two fields Seats Available and Seats Remaining

When I register a student to a Course in the Courses table I would like the corresponding Seats Remaining field for that course to decrease automatically.

I can, of course, run a report or a query to see how many are enrolled in the course and then manually update the Seats Remaining field (which is what I currently do), but I would like this process to be automated each time I register a new student.

View 4 Replies View Related

Make Field Auto Populate Based On Value Of Field In Another Table

Mar 4, 2008

I'm creating a database that keeps a track of questions and scores.

The questions in the database need to be dynamic and are changed frequently.

I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.

I want the question field in my scorecard table to populate with the value in my question table.

I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:

SELECT tblQ1.Q1
FROM tblQ1;

This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?

View 1 Replies View Related

Tables :: Inventory Sheet - Auto Population Of Field

Mar 16, 2014

I have a sheet of inventory I am working on creating. The price for storage for an item is determined by 2 things. 1. the type of material and 2. the size of that material. I have created a table with the material types and sizes. Is there a way that I can have the price automatically populate when I select the type of material and then size?

View 3 Replies View Related

Tables :: Auto Populate Field With Now Date Stamp

Jan 25, 2013

I need to autopopulate a field with now datestamp when another field has been filled in?

View 10 Replies View Related

Forms :: 2 Tables - Auto Populate Field In A Form

Oct 7, 2014

I have 2 Tables with following fields

Table [APM]: apm_id, apm_owner, apm_values
Table [DATA]: apm_id, serv_name, serv_place, serv_choice, serv_role

Both tables are joined with 1 to 1 relations based on apm_id as both of the fields are same. The table [APM] is pre filled with data. The idea is to list data from [APM] and collect data from user to be saved in [DATA] based on the info from [APM].

I created a form which list all the fields from both tables. The fields from table [APM] are only for displaying data and created a combo box based on [APM].apm_id to display the fields by chosing an APM ID from the drop down box.

A user adds a record and moves on to second record, completes the second record. Now if the user wants to list the first record by chosing the APM ID from the drop down box it only populates the fields from table [APM] but not the fields from table [DATA] which the user just added. Infect the fields related to table [APM] changes, but the fields from table [DATA] stays the same.

How ever if I click on the small arrows on the bottom which lets you cycle through records than it populates all the fields from both tables correctly.

How can I fix the issue so that all the fields would populate based on APM ID selected from drop down box.

View 4 Replies View Related

Tables :: Auto Populate Date Field Just Once (on Creation)

Jul 21, 2015

First hurdle in my database tables, I have a field called Date_Created which I want populated just once, whether from a form or an import.

I have another field called Date_Updated which I want to update any time the record is changed. I know I can do this by using the default value of Now(), but how can I get the Date_Created field just to update when the record is created and not any time after ?

I also want to show these values on the form, but have them as view only, hoping that is possible ?

View 5 Replies View Related

Tables :: Alpha Numeric Auto-number Field

Jul 26, 2014

I need some kind of function (I been told) that generates 3 different alphanumeric autonumbers in the same field when adding a new record, starting such field from A-1, B-1 and C-1 to infinite.Because the record gets inserted in the table with an append query and not manually through a form, I believe the function should be placed in the Default Value setting of the field.

View 3 Replies View Related

Tables :: SQL Table View Auto-number

Apr 7, 2014

i have created a Union sql Table View, that i have connected to in access. Its a list from 5 different companines of Supplier accounts. I need to create an autonumber for this table? Not sure if this may be more SQL based than access

SELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'RWL' AS Company
FROM Roth.dbo.PLSupplierAccount
UNION ALL
SELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'FAH' AS Company
FROM FAH.dbo.PLSupplierAccount
UNION ALL

[code]...

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved