Queries :: Make DSum For Products Coming In And Out?
Nov 9, 2014
I am trying to make a dsum for products coming in and coming out. find the example below
Products In
Apple 10
Mango 5
Products out
Apple 5
Now I want to show statement for sum as follows
Product. In. Out. Net in hand
Apple. 10. 5. 5
Mango. 5. 0. 5
But it is showing only
Product. In. Out. Net in hand
Apple. 10. 5. 5
what I understand is dsum cannot find the out field for mango as there is no field found for mango
View Replies
ADVERTISEMENT
Sep 6, 2012
I have an Inventory DB Table called [EngData] that has a subform table called [Inventory]. I have in the subform a calculated text box that Sums three columns, StockQtyMade, StockQtySold and StockQtyShrink. This works perfectly.
On the Main form I have a calculated text box that references the subform calculated field. This also displays correct info.
On another Form I wanted other users to see the total stock on hand by using a calculated field called PullTotalQty. This is set by using DSum(...). The only problem is that the DSum(...) keeps giving totals that are not accurate. Sometimes the quantity is a few pieces less then the correct amount and in other cases the qty is many times what it should be.
Whatever the amount in error it is consistent each time a particular record is displayed. I have tried many things to get this to work but nothing has fixed the issue. I have also tried changing the query and nothing works. Changing the query and using the [Inventory] table in the DSum domain gives a different wrong quantity.
Here is the code: I reduced the code down to one column to be summed (StockQtyMade) just to see if I could get that working first. I will add the other two columns later. for testing purposes I am only looking at the StockQtyMade amounts.
Me.PullTotalQty.Value = DSum("[tblInventory subform].[Form].[StockQtyMade]", "[SumStockQuery]", "[tblInventory subform].[Form].[PartID]= " & Me.[ID])
Here is the SumStockQuery SQL:
SELECT Inventory.TransactionID, Inventory.PartID, Inventory.PrintNo, Inventory.StockRev, Inventory.StockQtySold, Inventory.StockQtyMade, Inventory.StockQtyShrink, Inventory.StockQtyXIncomplete, Inventory.StockLoc, Inventory.TransactionDescription
FROM Inventory
WHERE (((Inventory.TransactionDescription)<>"xNextOP"))
ORDER BY Inventory.TransactionID DESC;
Why the totals are incorrect??
View 3 Replies
View Related
Apr 16, 2015
I have been trying to get this to work for days and I cant get it to work correctly. I have a query that I need to search only for the records that have 12,22 and 24 so in the fields criteria I have been trying
Total: Where
Criteria: In (12,22,24)
and it works BUT it only works for the first record it finds then it seems to skip and continue to search for all records after that.
View 3 Replies
View Related
Dec 6, 2013
As you know, the prices are, unfortunately, dynamic "objects".
So, I have a table for products:
tblProducts
ID_Product - Autonumber (PK)
ProductName
and a table for prices
tblProductsPrices
ID_ProductPrice - Autonumber (PK)
ID_Product - Number (FK on tblProducts
Price - Double
PriceDate - Date/Time
The problem: I have a certain date. I need to know the available prices at that date for all products.
I have a solution; but, for each product, I apply an external function that use two aggregate functions: DMax and Dlookup.
For my small tables, this isn't a problem but I like to know if it is a better way.
View 10 Replies
View Related
Sep 14, 2014
find only some clients that has only 4 types of products, but no other type of products.
Just to put it in a much easier way to understand. If I have to find only the client that did buy only 1 or more from the 4 products. Fridge, tv, dvd player, mobile phone. But I'm not interested in the clients that also did buy for example, laptop, pc, video cameras, etc. So if the client has only one of the 4 products, I want to list them all, did they buy another type of product too, then not.
How could I create a query that will show me only those clients?
View 4 Replies
View Related
Apr 8, 2013
Finally got my DB straight so I am not using calculated fields however can't figure out the best way to handle the Combo Box so I do not show closed records ( Item checked out and item returned).
Please see the attached table of materials checked out and materials returned. How to setup a query so that if an item has been returned and matches the item checked out, Both the original check-in and the matching return records are filtered and not displayed in the Combo Box.
Also, I am a little worried about partial returns as when an employee only returns half the amount.
View 14 Replies
View Related
Apr 1, 2014
I use access 2010. I have a form that allows the user to assign products to operations. Each operation can produce multiple products and each product can be produced at multiple operations. I manage this many-to-many relationship through the use of a third table that stores primary keys for associated products / operations.
tblOperation
tblProducts
tblOperationProducts
The query im trying to design will populate a listbox on the form that contains products which are available to be added to a given operation. In addition to the listbox there is also a combobox that allows the user to select the operation they want to add products to. As the user selects an operation, the query behind the listbox should return records for all products except the ones that have already been assigned to the operation selected in the combobox.
So far I've been able to make the listbox return all products always or return products that haven't been assigned to any operation at all, but I'm struggling to formulate the logic in my mind on how to show only products that have no association (through the tblOperationProducts table) with the operation selected in the forms combobox.
This is the query I have right now, it's not working:
Code:
Select tblProducts.ProductID
From tblProducts
EXCEPT
SELECT tblProducts.ProductID
FROM tblOperation RIGHT JOIN (tblProducts RIGHT JOIN tblOperationProductMM ON tblProducts.ProductID = tblOperationProductMM.ProductIDMM) ON tblOperation.OperationID = tblOperationProductMM.OperationIDMM
WHERE (((tblOperation.OperationID)=[Forms]![frmOperationProducts]![cboOperation]));
View 1 Replies
View Related
Apr 17, 2014
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
View 2 Replies
View Related
Apr 3, 2014
I have a products form, we are a manufacturing company, with a listbox to show recent inventory transactions. This is based on a query which shows all transactions with the current part id, and that all works well and fine.
The problem is, I would like to limit this query to show only the last 10 transactions in the listbox and not make it editable, ie not enabled. I set the show only in query design view to 10 and it says in the sql statement select top 10, however, the listbox consistently shows all related records. What am I missing?
View 8 Replies
View Related
Nov 18, 2013
I am using DSum to total some records located in a subform. I have code to save the value returned by DSum to a table on a lost focus event. It works great except for one thing. The value saved is always one record behind. The save code is:
Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset)
rst.FindFirst "ID = " & Forms!frmInvoice!txtID
With rst
.Edit
!Total = Forms!frmInvoice!txtTotal
.Update
End With
Exit Sub
txtTotal is the textbox containing the DSum value. It displays the proper value. If I insert a blank row the routine will finally pass the right value. I tried running the code from a button but it still copies the old total.
View 3 Replies
View Related
Nov 17, 2014
I'm attempting to do a DSUM in a query against a linked table that has a field name of "VOID". The field name shows up as a yes/no when looking at the structure and when looking at the data, it is either 0 or -1. I've tried just about everything I can think of but this is the structure I have:
Tons: DSum("Net","dbo_tblSALESTKT","JOB='" & Left([JobNumber],6) & "' AND Ticket_Date=#" & [forms]![frmForemanInfo]![CurrentDate] & "# AND [dbo_tblSALESTKT]![VOID]=0")
If I remove the VOID part, it will sum correctly but when adding in the VOID=0 or false or No or anything, the query doesn't show me a sum anymore. what I'm doing wrong.
View 2 Replies
View Related
Jan 16, 2014
I have a query which includes a deposit field and a Transaction_Date field. I would like a cumulative deposit on each record (arranged according to date). The following is the expression I am using:
=DSum("[Deposit]","[myQuery]","[Transaction_Date]<=#" & [Transaction_Date] &"#")
I am getting completely nonsense values from the expression - some of the fields are blank (the first few fields) and then the values bear no relation to the deposits preceding them. I have tried all sorts of formats including using Format([Date], 'dd/mm/yyyy') on either side and then on both sides but with no effect.
View 4 Replies
View Related
May 15, 2014
I can't seem to get my head around an issue i'm having with a DSum, the statement is:
Code:
=DSum("[Amount Used]","[tbl_Rwk_Used]","[Cage Number]='" & [IBC_Number] & "'" And "[ID]=" & [ID])
I've tested both statements separately and they work ok, but as soon as I add the "AND" operator, the whole field is summed and the criteria thrown out of the window apparently.
[ID] Is the same in both tables and relates to a main record
[Cage Number] and [IBC_Number] are related in both tables, but are and are strings
View 2 Replies
View Related
Oct 19, 2013
I am having a query showing customer as client, paid amount as pamount, billingdate, payment date as pdate.
I want to get dsum of pamount specific for each client with date criteria for example if i open query through form mentioning start and end date then the sum must vary as per the date given. the date is "billingdate" as mentioned above.
View 10 Replies
View Related
Jul 10, 2013
I need to do a DSum with criteria that picks up a previous month based on todays date, I have got the following but I don't know how to format the last part of the criteria.
If I take out the Month(DateAdd("m",-13,(Date()))) and replace it with 6 it works fine.
Code:
DSum("[invquan]","[qrySalesByStockCode]","[stcode] = '" & [stockcode] & "' and [Month] = Month(DateAdd("m",-13,(Date())))'")
View 1 Replies
View Related
Jul 9, 2013
I am trying to create a user-friendly database to enter invoice records (deductions and additions) for securities. I would like to be able to run queries with running totals from month to month inclusive (end total of one month = starting total of next month). I have already figured out how to create a running total; however, I have numerous "companies." Each record could be from any of 30 companies. When I add up the running total from say January 1 to February 28th, I get the additions and deductions of all the companies, not just the one I want. Inputting the companyID (example A110) in the query box only adjusts what is displayed, not the running totals that go by the total deductions from the records before that record (I assigned each record a "database ID" because dsum does not recognize dates in the criteria box, if I want to do a running sum.
I also was wondering if it would be possible to build forms with macros to create these queries in a user-friendly manner (so the company that I am interning for can create queries when they need them without me there).
View 12 Replies
View Related
Feb 26, 2014
I'm trying to generate a query that can be used for a pareto chart (Bar Chart shows the count of a defect and a line chart as a second axis counts the cumulative percentage)
I've grouped my data, and sorted the Count of each time the record appears but I cant get my head around working the cumulative percentage. My datasheet currently looks like this:
Reason Count Per Expr1
A 35 47.9 Random Numbers
B 11 15.1 "
C 10 137 "
D 9 12.3 "
E 7 9.6 "
F 1 1.4 "
Expr1: DSum("Per","Rwk_Pareto","[Per]<=" & [Per] & "")
View 6 Replies
View Related
Apr 17, 2015
I have the following in a query:
Count: Val(DSum("Num","Google_export_1Cancer_Target_Prea" ,"DatePart('m',[StudyEntryDate])<=" & [AMonth] & " And DatePart('yyyy',[StudyEntryDate])<=" & [AYear]))
Our Financial Year runs from April to March.
The formula works fine up to, and including, December. Then, for the last three months of the financial year (Jan, Feb and Mar), the running total of the 'Num' field stops and the entry for January is just January's 'Num' with the running total starting again for February and March.
How do I rectify this?
View 4 Replies
View Related
Dec 6, 2013
I have a query, i need to get dsum of total task given to an employee.
I have grouped task_description and select count and it is now showing each employee as 1 which is correct.. i ma looking to get dsum based on this task count but with a criteria that i want to see these tasks sum month a have a month field which i get from assigned date the month field is showing like this december-2013.
Based on this i want to calculate dsum as first to see task count which i mentioned earlier, then employee id which is in query as ID and then month.. this sum will calculate monthly tasks given to each employee.
View 5 Replies
View Related
Sep 25, 2014
I m using Access 2010.I m Facing a problem in the undermentioned expression,
OPB: 1*Nz(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#" & Format([FromDt],"dd-mm-yy") & "#"),0)
when i run the query it gives "Undefined Function 'Format' in Expression" ?
View 3 Replies
View Related
Feb 8, 2006
Hi allive just been asked this by a co worker on a remote sitethey have a client name they want to input to a table in Access 2000in a surname field which is just a txt fieldthe name to be input is 'comming'when they tab to the next field access changes it to 'coming'i tried it this end in a new database and it does tha same thing ..and in Acc 97any thoughts ...or a fix?Thanks
View 3 Replies
View Related
May 4, 2005
I have built a table with a field name of Birth date and have applied an input mask to have the data shown as: ##/##/##. Now, I would like to have another field labeled age. Is there a formula that I can create to have access calculate this in my table? I have tried "=mydate()-"Date Of Birth"" in the validation rule under the properties of the Age field; however, I keep getting an error. Any suggestions?
View 2 Replies
View Related
Sep 15, 2006
This is I think a very simple query.
with a number of forms/ queries etc open and you click on at the back it is not coming to the front for amendment.
It is probably an indicator somewhere but I just can't find it
any help appreciated
Ta
humph
View 3 Replies
View Related
Nov 6, 2006
Hello,
I have split a database useing the database spliter wizard. But I still have network speed problems. What I am wondering is if anyone knows if useing an ODBC connection between the front and back ends is more efficient than file sharing across the network?
Thanks for any information on this
Tim
View 4 Replies
View Related
Aug 15, 2006
Thanks if anyone can offer a suggestion, I cannot find a similar problem described on the forum.
I have a form which runs at startup maximised. Pop-up and Modal are set to 'NO', Auto Resize and Center are also set to 'NO', Border style has been set to various settings, and Moveable has also been tried at 'YES' and 'NO'.
A command button on this form opens another form with the same settings which I want to take the original form's 'place' in the application window.
This all works fine, just that when I close the second window (using DoCmd.Close), the original window is then no longer maximised underneath it. I want to make sure certain windows are maximised at all times.
I know I could hack this with a DoCmd.Maximise somewhere, (or by switiching to full Pop-up/Modal mode and hiding the Application window) but the results of these are all a bit of a hack.
Can anyone shed light on the mysteries of Access's default form maximisation behaviour?
Or does anyone do what I have described successfully?
View 4 Replies
View Related
Aug 19, 2005
Hi,
i was working with a database and accidently deleted the first row, i couldnt get the automatic number to number another row 1, so i took out this table and created another one. however even though my new record is in order, when i pull this through to the website it's coming in the order 1,3,4,5,6,2... and the coding for the page worked fine with the db beforehand!!! so now i'm a bit lost
if anyone has any ideas, they'd be appreciated!!!
Thanks in advance!!!
Megan
View 14 Replies
View Related
