Forms :: Total Query - Count Of Fields Based On Data In Other Fields

Jun 28, 2015

I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.

When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.

If I type * in the box (to denote all values) and press enter I get the results expected.

View Replies


ADVERTISEMENT

Forms :: Count Of Records Between Value Of Two Fields - Enter Total In Bound Field

Aug 8, 2013

I have two fields on the main form and I need a total count of records between the value of the two and then enter the total in a bound field on the main form

View 1 Replies View Related

Calculation Across Fields - Show Total Count For Each Record

Nov 14, 2014

I need a solution for the following problem:

For example:

............F1 F2 F3
Rec1 ....4 ..2 ..5
Rec2 ....3 ..3 ..1
Rec3 ....1 ..2 ..1

Now, I want to know how many 1s are there in Rec3 the answer is obviously 2 but I want a field(F4, for e.g) that calculates F1+F2+F3 showing all their 1s 2s and 3s.

A solution I found was...... F4=IIf([F1],1,0)+IIf([F2],1,0)+IIf([F3],1,0)

But I have over 50 Fields that contain 1s and 2s and I can't count them all by using this formula as it will be tremendously long and access will reject it.

So I need another formula(or function) that will look up for the 1s across multiple fields and show the total count for each record in a new field.

Tried 'look up' function but achieved nothing ....

View 3 Replies View Related

Tables :: Set Up Validation Of Fields Unit And Size Based On Another Fields Data?

Nov 18, 2013

Basically in my order details table i have the following fields

Product
Unit
Size

At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie

ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big

When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.

How do i set up validation of the fields Unit and size based on another fields data?

View 4 Replies View Related

Forms :: Display Data Based On Previous Record But Not All Fields

Jan 22, 2015

There is a form which the user fills in 8 fields and saves it. Once saved, a new record opens.

However, I want to modify the save button to save the previous record and display a new record with only a limited no of fields to display from previous record, say 4 fields display from previous record, and the user fills the remaining 4 and saves it. Thus, the save cmd should continue to display 4 fields from the previous record.Already tried below code for save button but it displays all data of previous record as it is, doesn't display blank space for combo.118 and text2

DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
Combo118.Text = ""
text2.Text=""

View 1 Replies View Related

Total Data In Fields

Jan 3, 2007

Good Afternoon All,
I have a table devoted to time spent on certain projects. I am trying to make a query to show the total time spent on each project (the data is not in time format) and then turn that into a percent for a report. I have tried doing a summary query, but that isn't giving me the data i am looking for...is there an easier way:confused:

View 13 Replies View Related

Forms :: Disabling Fields In Access Based On Data Entered In A Previous Field

Sep 9, 2013

How to disable fields in Access based on data entered in a previous field? For example if "yes" is chosen from a drop down show "Date field" if "no" is chose hide "Date Field".

View 2 Replies View Related

Combine Like Product Fields In Table And Total Quantity For Those Like Fields?

Apr 22, 2015

I have a product table that includes the name of the product, the quantity ordered, and the unit price. This is a summary table for the whole year showing all of the orders. The Primary Key is [ID] from the [tblOrders] table. I attached a screen shot of my query. I don't know if you can see it. I want to generate a report where each product is listed once, the unit price for that product is listed, the total number of that item that was ordered throughout the year, and finally, the total amount spent for each item during the year.

View 2 Replies View Related

Forms :: Sum Total Of 2 Fields On Third Field

Oct 22, 2013

I've tried many things (compact and repair, Nz(,0), sum()) and I can't get it to work.

I entered =[field1]+[field2] but it behaves like if I had entered =[Field1] & [Field2].

Yes instead of adding the numbers it just puts them together.

For example: Field1=3 and Field2=5 instead of field3=8 it shows Field3=35.

View 3 Replies View Related

Beginners Question - Auto Update Fields Based On Fields In Another Table

Nov 26, 2007

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)

DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15



IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static


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.

thank you
-Tim

View 2 Replies View Related

Auto Rearrange Fields Or Hide Fields In DataSheet Based On Criteria

May 17, 2007

I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.

This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.

What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.

If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)

Private Sub Form_Load()
If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then

Me.CASETIF.Visible = True
Else
Me.CASETIF.Visible = False
End If
End Sub


And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form

Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap


Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria

Thanks
Rahul

View 1 Replies View Related

Using DCount To Count Fields On Query

Jan 3, 2005

Good day:

Code:

=DCount("[DEPARTMENT]","[Query1]","[DEPARTMENT]='ENGINEERING'

I have a query with these fields: DIVISION, DEPARTMENT.

The DIVISION fields are populated with undergrad or grad. The DEPARTMENT fields are populated with ENGINEERING.

I wish to count the occurances of undergrad engineering and count the occurances of grad engineering and return the values in separate text boxes.

Any ideas on how to count occurances of TWO fields?

Thanking in advance.

Dee

View 2 Replies View Related

Queries :: Query Count Difference Of Two Fields?

Jul 20, 2013

i have a access table (AUTH) with following field

Company Auth Held

Tata 12

Dell 11

HP 21

Opera 11

Bangour 10

i used the following query to calculate the Held from a table named pers.

SELECT AUTH.company, AUTH.Auth, Count(AUTH.company) AS Held
FROM pers INNER JOIN AUTH ON pers.company = AUTH.company
GROUP BY AUTH.company,AUTH.Auth

then i got the result as under

Company Auth Held

Tata 12 02

Dell 11 08

HP 21 20

Opera 11 12

Bangour 10 12

now i want the difference between Auth and Held as Sur/Defi

how can be it done.

View 2 Replies View Related

Queries :: Adding Spaces Based On Total Character Count

May 31, 2014

I have 4 fields I'm trying to combine, but I need to add spaces between field 1 and the rest of them. The total character count needs to be 22 including the spaces.

Example:

Field 1: THE

Field 2: 1234

Field 3: BOAT

Field 4: 0001

End Result: THE 1234BOAT0001

Need to add 7 spaces to equal 22 characters.

Fields 1, 2, and 3 can vary in number of characters.

View 6 Replies View Related

Forms :: DLookup - Using Two Fields (number And Drop Down Text) To Query Table For Data

May 29, 2015

DLookup function. (this is for a stamp collection database).

On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.

I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.

I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.

That express is :
=DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")

So it will populate the "StampName" data to match the "Catalog" number entry just fine.

However, I need to add a second layer to incorporate the Country.

Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".

I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :

=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")

Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.

Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.

View 4 Replies View Related

Auto Data In One Field Based On Other Fields?

Feb 27, 2008

Hi,

I have a table that is used for project progress. One of the fields is named "Completed". I would like to know if it's possible to auto fill the data in the "completed" field based on three other fields within the same table.

ex: If field 1 = Yes, and field 2 = Yes, and filed 3 = Yes, then field "completed" = Yes

I'm open for any ideas, I'm new to access and am not sure how to get this started.

Thanks

View 3 Replies View Related

Tables :: Calculated Fields As Data Type In Tables - Calculating Total?

Apr 23, 2013

I am using calculated field as a data type in access 2010.

They are working fine.

However, I added a new field and now the final calc won't work.

I have Subtotal adding loads of fields together. Works fine.

Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.

The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?

View 2 Replies View Related

Getting Data From Rows To Columns Based On Common Fields

Jul 30, 2013

I have a big Excel file with payroll information about employees. Per employee per date, there are a couple of rows with mostly identical data (such as the employee's home address) but 2 differences: one will have paycode A with amount B, another will have paycode C with amount D, etc. I want to simplify the file to have 1 row per employee per date: in the example given that row would have paycode A with amount B as well as paycode C with amount D, in 4 separate columns.

I think I can technically solve this by creating Excel files per pay code/amount, and then linking them together by person and date, 1 by 1, through Access. This is feasible because there aren't that many different pay codes. But still, I was wondering if there was anyway to solve my problem in Access itself.

View 1 Replies View Related

Adding Fields Based On Values In Other Fields

Jun 6, 2007

Hi,

I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example.

When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.

View 3 Replies View Related

Tables :: Limit Data Input Based On Another Fields Criteria?

Dec 22, 2014

I have two tables linked by ID (Table1,Table2)

The Table1 holds data that is a request for a task.

ID, Request, Task_tms (the number of times the task is required to be done)

1, Make a cup of coffee,15

Table2 is my allocation of people to the tasks.

ID, Person, Task_done (the number of times this person has done the task)

1,Sally,10
1,Eddie,5

What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.

Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)

The two table are in datasheet format linked on a form.

View 1 Replies View Related

General :: Make A Field Based On Data From Multiple Other Fields

Feb 19, 2013

Let's say I have a table called "Courses" which among other things has "Completed"(Yes/No) and "Category"(Text).

Now, there also another table "Progress" which has similar columns.

I want Progress.Completed to reflect if all courses of the specified category have been completed.

How would I do that? I'm new to access ...

View 1 Replies View Related

Modules & VBA :: Displaying Specific Fields Of Data Set Based On Combobox Choice

Sep 19, 2014

I have a Table with 57 fields. I would like to display this table in a form as a subform, but only certain fields depending on what selection is made in a combo box.

For instance, if the user selects "Missing Information" in the Combo Box, then the form will show a few standard fields such as ID, Market, Sales Manager, and then some specific ones such as date missing information requested and date missing information received.

If the user selects another option, again the standard fields will remain plus a few different ones.

I have done much searching on this and feel like I am so close but so far. I have looked into controlling the record source of the subform, columhidden =false and a multitude of others. All of which may or in fact probably do work in this situation but I can't seem to put it all together.

View 4 Replies View Related

Forms :: Update Record Based On Two Fields

Jun 30, 2013

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)

View 8 Replies View Related

Query Based On Value Between Two Fields?

Jun 2, 2014

using access 2010. I have two tables, an example of their structure is in the attached PDF, and in text below. I want to be able to join/ answer question between the tables like the one outlined below.

Reference Table: A table with the most likely pet based on income and location

Header: Country, State, Income Min, Income Max, Pet
Record: US , NY , 1 , 5 , Cat
Record: US , NY , 5 , 9 , Dog

Question Table: A table with income and location

Header: Country, State, Income
Record: US , NY , 5

What type of pet will the people in the question table have?

Answer: Header: Country, State, Income, Pet
Record: US , NY , 5 , Cat

The logic works like this:

Question Country = Reference Country
Question State = Reference State
Question Income >= Reference Income Min AND Question Income <=Reference Income Max

Obviously I am not working with cats and dogs but this is my table structure.

View 3 Replies View Related

How Can I Configure A Table To Auto Data Entry Into Fields Based On A Previous Field

Aug 31, 2006

My situation is this. I have 3 tables that I have imported from my mainframe system, between these 3 tables I have the data of product code, description,supplier code, supplier name, order method, and ABC code.

I am trying to create another table that I can capture daily Out Of Stock data for products.

What I would like to do is to enter the product number in the first field of my new table, and then the remaining fields will auto populate with the correct details based on the product data stored in the parent tables that I have imported.

How can this be done?

View 3 Replies View Related

Queries :: IIF Statement Expression - Create Alias Column Based On Data From Two Fields

Jun 20, 2013

I am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.

Here's the logic:

True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"

False: "GS"

View 2 Replies View Related







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