Queries :: Subtract Value From One Table From Each Item In A Group In Another Table

Apr 23, 2015

I have a database to keep track of time spent on development work. The database uses mainly two tables: Estimates and Status.

The Estimates table holds a static number for each item to be worked on. We generally subtract this number from the total number of hours in Status spent on each of the items. In queries, to calculate the overall delta, we subtract the Estimate from the overall Status for each item.

However, we would like to create a report that gives us a running total for each item. So, if we have 100 hrs in the Estimate table for Item A and 5 hrs for item B, then the report would ideally show something like this (delta between Status table and static value in Estimate table):

Code:

Item | Resource Name | Estimate | Actual | Delta
--------------------------------------------------------
A John Doe 100 10 -90
A Jane Doe 90 5 -85
A John Appleseed 85 5 -80
B John Doe 5 10 5
B Jane Doe -5 5 10

This is so that we can see how our actual hours spent working on a task line up to our estimates. So, if we are under estimating our work, we can easily see this.

In Excel, this is of course no issue, but it becomes an issue when trying to write a query in Access to report this information.

As I said, we can do the overall numbers, just not the line item numbers.

View Replies


ADVERTISEMENT

Reports :: Sum Group Items By Specific Item And Hide Details Of Group Summing

May 29, 2015

Despite Google I can't seem to figure this out.

I have some data in a format similar to:

Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000

I want my report to total by style, without details and to look like:

driver careful 13500
racer reckless 12500
snail slow 6000

How do you get a report to sum the group items by a specific item and to hide the details of that group summing?

View 2 Replies View Related

Queries :: One To Many Table Group And Count

Jul 6, 2013

I have 2 tables. tblOrders and tblOrdersItems. tblOrdersItems is the child and tbl Orders is the parent. the linking field is OrderID.

I am wanting to count all the OrderID in tblOrders. the criteria i need is EmployeeListID which is stored in tblOrdersItems.

When I put these tables into query design, add the criteria, grouping(see below)

Code:
SELECT tblOrders.OrderID
FROM tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee
WHERE (((tblOrders.OrderDate)>Date()) AND ((tblEmployeeList.EmployeeListID)=[Forms]![rptReportsMainScreen]![txtEmployees]))
GROUP BY tblOrders.OrderID;

I get (record count at bottom of query screen) 87 records. then i add a field to give me the count so i can then use it i get many more.

How to get the count working. i have uploaded the relevant tbl and my attempted query.

View 9 Replies View Related

Subtract One Table From Another?

Aug 11, 2006

Hi, I hope someone can help me out with this problem:

I have 2 tables with identical stucture, and no unique ID number.
Both had identical data, and the databse was then copied onto 2 laptops. Now further data was added onto each.

I want to now merge the three databses (ie original one, and 2 laptops) into 1, without creating duplicates- so I need to identify which are the new fields added to the databases. I figured that by somehow subtracting the original table from the new ones, i would be left with the new data, but dont know how to do this. Or is their another way?

View 2 Replies View Related

Queries :: Compare Data In 3 Fields In 1 Table / Group By Largest

Dec 4, 2013

I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:

PRODUCT table:
ProductName
Chemical
ChemicalAbstract
PhysicalState
NFPAHealth
NFPAFlammability
NFPAReactivity

qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.

The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.

In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:

Flammability
Rating 4
Product 1
Product 2
Product 3

Rating 3
Product 1
Product 2
Product 3

Rating 2
Product 1
Product 2
Product 3

Health
Rating 4
Product 1
Product 2
Product 3

[code]....

and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.

View 4 Replies View Related

Forms :: Subtract Value In Table Using Textfield And Button

May 22, 2013

I have a table in wich I have stored numbers and i want to subtract them using vba.

example

Table1
ID number
1 20
2 40

textbox(ID):2 texbox(value):5 button(subtract)

So when i click button i want to subtract record with id 2 (40-5=35) and i want the new value to rewrite the previous one in the table.

result:
Table1
ID number
1 20
2 35

I want to know if it's possible to do this in vba.

View 8 Replies View Related

Queries :: Group Financial Data To One Main Table - Round To 2 Decimal Places

Feb 25, 2014

I have about ten append queries to group various financial data to one main table.

I have used the round function (iff (Round(Nz([FIN_data]),2)) in the queries to round the original data into 2 decimal places but there is still one or two lines exceeding 2 decimal places.

What is the better approach to have only 2 decimal places for all append data?

View 4 Replies View Related

General :: Subtract ListBox Items In Field Of A Table?

Sep 26, 2014

I have a form with a textbox, listbox and a button.

my textbox is based on one of my tables, and when I enter a value (which is saved in my table) in my textbox and press enter, certain values in my table goes to my listbox, and my listbox will just additem whenever I do same thing in my textbox all overagain.

how can I subtract listbox.column(1) items in my table field "Item_Quantity" where my Listbox.column(0) is equal to my table field "Item_Description".

My Listbox column count property = 4
Row source type = value list

View 1 Replies View Related

Tables :: Date / Time Fields In Table - Subtract To Get Total

Oct 31, 2012

Any way of setting up a table containing the following date/time fields.

StartDate
StartTime
EndDate
EndTime

Ultimately I will need to be able to subtract these date/times to get a total time between the two. Should I combine the start date and times in one cell or keep them separate.

In either scenerio, how do I subtract the two in a query for a report?

View 1 Replies View Related

Subtract Number Range While Keeping Track In A Table / Form

Mar 31, 2014

I have two tables (one is a query)

Table 1 (query based)

EMP_ID
Prev_Emp_ID
EMP_ID_DDSK

332-123
1
500

332-133
1
501

332-144
0

332-156
1
502

332-654
1
503

332-456
1
504

332-967
0

Table 2

Res_Numbers
Num_Of_Employees

500
10

505 - after 5 numbers are placed
Will be added for next hire

My goal: to place employee numbers into new employee field "EMP_ID_DDSK" (table 1), Numbers will come from (from "Res_Numbers" field (table 2)

Example: If field "Prev_Emp_ID" = false

Get the next number in line from table 2 "Res_Number" and place it in table 1 field "EMP_ID_DDSK".

Example: if "Prev_Emp_ID" = false for 5 new employees. Take the next set of numbers and place into field "EMP_ID_DDSK".

View 4 Replies View Related

General :: MS Access Code To Update (add / Subtract) Value (quantity) On Table / Form

May 15, 2015

I have a 'tblStock' with fields 'ProductID', 'InitialStock', 'Buy', 'Sell' and 'UpdatedStock'. I also have a form 'StockUpdate' add values and also add new records to 'tblStock' .

If I have value [100] for IntialStock quantity, Buy [0] and sell [10], UpdatedStock will be [90] (that's done and fine!).

The problem is, I would like to make the UpdatedStock value [90] to be the NEW InitialStock, so that any BUY or SELL will keep updating the UpdatedStock and making it the NEW InitialStock for the next transactions and so on....

View 8 Replies View Related

Query The 3rd Most Recent Item From A Table

Oct 18, 2007

I have a table with a DateTime field. I would like to create a query that queries only the 3rd most recent record in the table. So there would only be one record per ID. If there is only two records for an ID, it would show nothing. I have tried a few things already, but none of them have worked quite right. I was wondering if anyone had a new idea.

View 11 Replies View Related

Calculating Line-Item Cost In Table

Oct 11, 2006

I am having trouble calculating a field in a table and on a form.
I have the following Tables
CUSTOMER: Customer No, Name, Address, City, State, Zip, Discount, Phone, Fax, Contact Person.
ORDER: Customer No, Order No, Order-Date, Delivery-Date.
ORDER LINE: Order No, Product No, Quantity Ordered,
line-item-cost
PRODUCT: Product No, Description, Price, Product Image.

I need to calculate the line-item cost and need it to update every time the user enters a new Quantity ordered on the ORDER LINE form.
I know its (Quantity Ordered*Price)-(Price*Discount) and I did this is a query and it worked but I need it to update in the form and update in the table not the query. I have tried everything (Macros, Queries, and Formulas) and I just can not get it to work.
Does anyone know how to do this?
Thanks

View 1 Replies View Related

Filter Query If Item Is Sold In Another Table.

Sep 14, 2007

So here goes :)

I have made this example database to show you my problem, I have 2 tables here:

1. "items" table that works like warehouse listing items in stock and has these fields:

ID autonumber
Example name TEXT
Sold Yes/No

2. "sales" table has these fields:
ID Autonumber
Example name created using Lookup in Design view

I created Query called "sales Query" that I use to enter data and my problem is I need only to be able to enter items that are not checked in "Items table".

In other words I need to be able to select in the query dropdown items currently in stock.

How to solve this the easy way? I tried using criteria "False" but dropdown still lists checked items WHY?

View 5 Replies View Related

Modules & VBA :: Save Item In Listbox To Table?

Jul 8, 2014

code to save data in listbox to a table in vba access

View 2 Replies View Related

Tables :: Add To A Table With Lookup Does Not Find Item

May 16, 2014

I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.

View 1 Replies View Related

Modules & VBA :: Save Item In List Box To A Table

Jul 11, 2014

Save Item in listbox (Row source type : Value list) to a table in a database .

View 3 Replies View Related

Multiple Item Form From Query To Table?

Jan 14, 2013

I am trying to augment my database to have a price list. Because I wanted the price list to be of record and saved, I used a form with a subform. The main form taking the price list header info price list ID, price area, and date and putting into tblPricelist) and using the sub form to table to load the actual price list info (product, price) and putting into tblpricelistsub.

What I am trying to do is to be able to bring the entire price list as it is and be able to update (or not) the price of all or some of the items and then have it save as a new entry into the tables. I am trying to do this without having to use a sub form and lookup every product all over again (500 products).

Is there a way to do a query to bring up the entire price list, turn that into a form, and then be able to update the prices that would in turn create another entry into the price list and pricelistsub tables?

View 13 Replies View Related

Queries :: Update In Table From Same Table Based On Criteria Column In Same Table

Sep 29, 2013

How can I update (some columns) in a table from the same table based on a Criteria column in the same table.

View 2 Replies View Related

Tables :: Delete Item Entered In One Table From Another Automatically

Dec 24, 2013

Im trying to make a basic customer database and pos system. The part iam having trouble with is, I have two tables as follows:

Inventory: orders(invoices):
productId ProductId
amount (in stock) amount(sold)
pricePerUnit CashTendered

I want the amount(in stock) sold in each order to be deducted from the total in the amount(sold) automatically.

View 1 Replies View Related

Forms :: How To Get Only One Item In A Combo Box When Using Table / Query Option

May 1, 2013

How do I get only one item in a combo box when using the Table/Query option (ie No Duplicates). I want only one of each field with no duplicates.

View 2 Replies View Related

Linking Multiple Items From A Table To One Item In Another In Forms

Mar 4, 2015

I have what should be a really simple database to build. In table tbl1PlanHdr is the header item. Many items in tbl2MaintItem will then be allocated to each header. MaintItems items will be allocated to more than one Header.

I want to set up a form that has the tbl1PlanHdr as the main part of the form and tbl2MaintItem as the subform. Possibly as a datasheet, or continuous form.

I need the ability to update items in the subform back to the orginal table.

I have a relationship of one to many from the tbl1PlanHdr to tbl2MaintItem using the primary key in tbl1PlanHdr.

But should this be a one to many from tbl2MaintItem to tbl1PlanHdr

The end result required is to be able to get a data dump of all the tbl1PlanHdr items with the corresponding tbl2MaintItems items.

View 8 Replies View Related

General :: Order Database - Discount Table With Percentage For Each Item

Aug 23, 2013

I have been asked to create a database that creates orders, debits stock etc for my company. Now I have been asked to put in some kind of discount table with percentages for each individual item for each individual customer. What would the best way of doing this?

I figured in the Customer Table I would have a code such as 'CustomerADiscount' and 'CustomerBDiscount' etc. and then in my table maybe

Product A - CustomerADiscount - 10%
Product A - CustomerBDiscount - 20%

Product B - CustomerADiscount - 5%
Product B - CustomerBDiscount - 7%

This link these values up within a query? Would this be the best way of doing it?

Is Access able to determine a percentage in "10%" or "5%" or do I need some type of formula for it instead?

View 3 Replies View Related

Forms :: Entering One Item To Create Multiple Entries In A Table?

Nov 7, 2014

Basically I am inputting stock into my database so I can have a record of what I have left and who it's gone too. Where I'm stuck though is when I want to add an item that I have more than one off but only enter it once and have it assigned a unique stock ID number (this is in the form of an autonumber) for however much quantity I choose to enter. So for example I have 5 keyboards, I type in 'Keyboard' into my item field (named txtItem) and then type '5' into my quantity field (named txtQuantity). When I then press Add Stock (named btnAddStock) I should then see in the table, 5 separate Keyboards listed each with a different Stock ID number.

View 14 Replies View Related

General :: Create New Table By Item ID To Show Balance Of Stock

Feb 17, 2014

I have created 3 tables for simple inventory.

a) Item details
b) stock in
c) stock out

All I simply want to do is create a new table by item id to show balance of stock.

View 2 Replies View Related

Forms :: Populate Textbox From Table Field By Clicking Item In Listbox

Aug 28, 2013

I am trying to populate a textbox from a field in a table based on clicking on a item in a listbox. User clicks a name from the Client table in the client field, and the date that is stored in the orderDate in the same row. I want the text11 textbox to show that date.

View 9 Replies View Related







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