I am trying to sum a qty field naming it 'TotalRecieve' and then minus a qty from that sum?
I've tried the following
Code : RemainingQty: Sum([tblReceiveDetail].[QTY])-[tblOrderDetail].[Qty]
but i get an error saying 'Your query does not include the specified expression as part of an aggregate function?
I have a form for inserting invoices, and on the subform records I have a command button to print the labels, the label would contain the quantity of the product, so, if the quantity is ex. 11000 and package contains 2000 only.
so I have to print 5 labels with quantity 2000 and one label with 1000 qty
what I need to do, is when I click the label cmd button to insert the 6 records required to print the labels to temp table
I have a report which is a statement. I want to be able to have a field that calculates rent, and minus payments. There are a few rent types, Pro Rated rent, Rent brought forward and rent changed, then there is 1 payment type. I am trying to write the sum a few ways, but have not had success. If rent type i= 1 or 2 or 3 I want it to add, if rent total = 4 I want it to minus.
=Sum(IIf([RentType]=1 Or 2 Or 3,[RentAmount],0))-Sum(IIf([RentType]=4,[RentAmount]))
I need to create a query that will do the next:
use today's date, minus start date, minus sent to BP, minus response from BP.
The field names are:
Start_Date
Sent_to_BP
Response_from_BP
I also have a holiday table that should be used to have only business days passed (so, not weekends and holidays counting).
I have the time per item, and I would like to multiply that by the quantity, so I can tell the total time. But I cant find in access how to * data/time by a number.
View 3 Replies View RelatedI am trying to pull between the current date minus a week in my Access query and I do not know what formula to use.
View 8 Replies View RelatedI have a problem with calculating the total qty of two fields.
Code:
QtyMapics: Nz([MapicsQty];0)
QtySubComp: Nz([SubComponentQty];0)
TotalQty: [QtyMapics]+[QtySubComp]
When I calculate the values of the fields it pastes the values after each other.For example: if you have the values 1 and 2 (which should become 3) then the result is a value of 12.I've tried changing the function to SUM() but that didn't work.The reason I am using NZ() is due to the fact that some values have a NULL value in one or both of the fields and if NZ is not included it will not calculate anything.
I am on Access 2000. I would like to know how could I define the number of symbols that can be input in a certain field? For example in Home Phone field I would like to be able input more than 30 characters, but database doesn’t let me input more than 16. How can I change that?
Thanks a lot.
Regards.
I have a query (Inventory Transactions Extended) in which i am trying to caculate current stock.
I have a form (Inventory Transactions Form) where i either add or remove Inventory Items.
I have used this statement to create a new field (Actual Quantity) to calculate current stock based on stock been added and removed:
Code:
Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition";[Inventory Transactions]![Quantity];-([Inventory Transactions]![Quantity]))
for some reason it does not work. It is not calculating current stock. If i remove stock it shows a negative amount based on the amount i removed, and when i add stock it shows a positive amount based on what i added
I have attached a sample.
[ATTACH][/ATTACH]
Sample.accdb
I like to have my query splitting quantities and give me running/accumulating total.
See attachment as what I'm currently getting and what I would like to get.
I would like to do a loop but never done one before, basically i want to copy the current record by the number of times specified in a quantity field
So if the quantity field in the record says 5 then copy that record 5 times (I have managed to create the copy and paste code but dont know how to make it do it 5 times
Code:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
Hi can anyone help i dont really have a clue about access but im tryin to make some tables but i dont know how to do somethings with them. I have these 2 tables 1 called order with fields stock and order quant and 1 called fills with fileds called stock and lots. They are linked by the filed called stocks. What i need is in another table a field with remaining Shares where it takes fills from order quant. is this possible if so how would i do it.
View 2 Replies View RelatedHi,
I am setting up a query to display a records of products within certain sizes.
I want the width to be input, eg; "500" and i want all records - plus or minus 50 of this record to be displayed. I know i could use the "BETWEEN" "AND" command but i want the user to only enter 1 size if it is possible.
Any help greatly apreciated.
Thanks
Don
Hi
This is probaly really easy, I want to identify just minus figues in a query ie -5.00. I have tried iif([TotalPrice])<0,[TotalPrice]) this shows no result. Can any one advise. Thanks in advance.
Hi guys!
It's unknown for me why Access hasn't got MINUS implemented and Unmatched Query Wizard doesn't help me much. So I'm wondered if anyone had to confront the following task:
Table1
Book Agent
----------
b1 a1
b2 a2
b3 a3
Table2
Book Agent
----------
b1 a1
b3 a3
The problem is I have to eliminate duplicates but I need to find unique row, which is Book and Agent. From my example it's row with (b2 a2). I tried using
Select * From Table1
Where (Book, Agent) Not In (Select Book, Agent From Table2)
didn't work
I also tried EXISTS
didn't work
Unmatched Wizard compares only one field, not two.
Any comments welcome, thank you
To fill a ddl list I could use a MINUS query. I found out that UNION and UNION ALL work, but MINUS doesn't.
Is there a way to make it work, or maybe an other way to solve this kind of problem?
Hi,
I've searched this forum and can't quite get what I need.
I have a subform on a form, in the footer of the subform I total an amount owing field. Then I get the value from the footer control to the main form. I know that the total should be 0 but on the main form I get a -0.0119. How can I get this to show 0.
Thanks
this is vexing me.
I have 4 tables, 2 entities, 1 relationship and 1 lookup
Entities are tblOrg and tblForm
Relationship is tblVentureParticipation
Lookup is tblInfo (Has many different lookups)
When an organization (these are listed in tblOrg) turns in a form, a new record is inserted into tblForm with the ID of that organization and the ID of the form
I want to identify the forms that have NOT been turned in.
So I figured
Step 1:
Identify all organizations who are participating (WHERE tblOrg.OrgID = tblVentureParticipation.OrgID)
Step 2:
Join all of the previously selected organizations with all of the forms (FROM tblOrg, tblInfo WHERE tblInfo.LookupID LIKE 'F##')
Step 3:
Remove all of the entries in tblForm that match the result of Step 2.
I have verified that both of these individual queries return the right information ...
I've been using the following query:
SELECT
[qry_1].Month,
B_Division_Group.Grouping_Name,
Sum([qry_1].Month_Client_Count_from_B) AS Monthly_Count,
Sum([qry_1].Month_Assets_from_B) AS Monthly_Total,
Sum([qry_1].YTD_Client_Count_from_B) AS Yearly_Count,
Sum([qry_1].YTD_Assets_from_B) AS Yearly_Total
FROM
([qry_1] INNER JOIN tbl_branch
ON [qry_1].BranchCode = tbl_branch.BranchID)
INNER JOIN B_Division_Group
ON tbl_branch.BranchName = BDivision_Group.N_Br
GROUP BY
[qry_1].Month,
B_Division_Group.Grouping_Name;
While all monthly and yearly values were positive, the query produced exactly the results expected (i.e. one row of data for each Grouping Name/Month combination, containing the overall totals for each field).
Now, however, some Grouping Names have minus values and the query is showing an extra row (one for positive values, one for negative). It's as if having one or more minus values is being treated as a new Grouping Name/Month combination.
Is Sum() the correct method to use, when dealing with negative values, or should I be using some other function?
I am creating a report and I need data about parts that have not been used in the past 12 months. I'm not sure how to get just numbers from the past 12 months. How do I get the current date minus 1 year?
View 2 Replies View RelatedI have a datasheet subform that is acting like a timesheet. I have a TimeIn field and TimeOut field (both fields formatted as Medium Time); and a field for Hours to total the time. I used the expression =HoursAndMinutes([TimeOut]-[TimeIn]) that I found in another post, and it didn't work. All I got was #Name. Any thoughts as to why that's happening? I would preferably like the hours to total in tenths of hours, ie. 10.5.
Thanks
ScrmingWhisprs
So I am trying to setup a default value in the tbl design. The Following two fields are the two in question(StartDate & ReportDate). It already has relevant dates properly formatted in the StartDate Field. The ReportDate will always be the day before or minus 1 day from the Startdate. My question is that I can't seem to get the syntax right to make it work.
Screenshot included.
hi
i try to build an access form .
i have "big" table that fill in the name of the product , the quantity , and a minimum quantity.
the field : min quantity give us the quantity that is the minimum of the product.
i have a "minimum" table that has the list of the product and the minimum quantity.
i want to see the minimum quantity of a product , when i type the product (in the form if it possiable , if not - in a report)
eran
I need to minus one amount from another in my subform.
How to do that and which command to use? Sum?
Also should I go to criteria and do it there?
Hi all,
I need help with what I think is a relatively simple DB. I'm creating a DB with 2 tables:
1) Inventory Balance with the following fields:
*Month
*FiFo Date
*Article number
*Quantity
2) Transactions with the following fields:
*Month
*FiFo Date
*Article number
*Adjustments qty (positive or negative)
*Receipts qty
*Invoice Create qty
*Invoice Void qty (negative)
I've added the month so I can filter any given month.What I want is a query that will calculate my closing balance (in qty) following the FIFO method and I haven't got a clue how to start.
Can someone help?
Thanks,
Ingrid
Im integrating a Scanpal Batch barcode scanner into my application, and need a little help with my label printing facility.
at the moment the scanner imports a list of barcodes and quantitys this is merged with my product table and results in a table something like this:
barcode | Description | price | quantity
123456 | my stuff | 9.99 | 7
i need a query to move this to my label printing however my print queue works like this
barcode | Description | price
123456 | my stuff | 9.99
123456 | my stuff | 9.99
123456 | my stuff | 9.99
123456 | my stuff | 9.99
123456 | my stuff | 9.99
123456 | my stuff | 9.99
123456 | my stuff | 9.99
can a query do this?
Thanks