Date & Quantity Query Help

Sep 28, 2005

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

View Replies

Forms :: Split Record Quantity To Multiple Records Based On Quantity Per Package

Jun 21, 2013

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

View 8 Replies View Related

I Need A SUM Of Quantity Depending Also By Date!

Jul 28, 2006

I am searching the net for a response to my problem but no result;
I have 1 BD access (Facture) in witch there is 2 tables (Entete_Facture and Detail_Facture) with a relation in Field (Numero_Fac).

It is very easy to use "INNER JOIN" to do a Query (Facture_with_date) fusionning the 2 tables, But I want to add to this query another column : " SUM(Qte_Article) GROUP BY Code_Article " as below:

View 1 Replies View Related

Forms :: DSum - Calculate Purchase Quantity With Criteria Of Product And Quantities Before Sale Date

Nov 2, 2014

I have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria

Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#31/07/2014#"),0)

Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#" & [forms!salesm!sdate] & "#"),0)
But it is not working

Sdate is date and it is on main form

View 6 Replies View Related

Query Doubles Quantity

Mar 28, 2007

I'm sure that this issue is simply. I've done it before, but for some reason I'm having problems today. :confused:

I have 4 tables in one database as follows:

1) Sort Table = Sort #
Sort Date
Qty Sorted
Qty Rejected
2) Sorter Table = Sort #
Sorter Clock #
3) Operator Table = Sort #
Operator Clock #
4) Defect Table = Sort #
Defect Type
Defect Qty

When I attempt to query these tables, my totals increase.
EX: I have 500 items sorted; 100 defects; 2 sorters; 2 operators and 100 defects of type 1 and 100 defects of type 2.

My query results are 1000 items sorted with operator 1 having 100 defects of type 1 and 100 defects of type 2 and operator 2 having 100 defects of type 1 and 100 defects of type 2.

My quantity of items is doubled because it took (2) operators to make the items. My defects are doubled because it took (2) operators to make the defects.

Please help.;)

View 1 Replies View Related

Queries :: Calculate Stock Quantity With Query

Jan 21, 2014

I 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.

View 4 Replies View Related

Modules & VBA :: Loop - Append Query To Work Referring To Quantity Value For One Record

Jul 29, 2015

I have a part table summary with various quantities 1-1000 and want to create a new table where part number repeats with quantity of 1 corresponding back to the sum. If part 123456=20 then this would repeat 20 x and each record Qty=1

Start with summary
--================
Part Qty
111000 2
222000 3

End result all Qty=1
--================
Part Qty
111000 1
111000 1
222000 1
222000 1
222000 1
--================

I stared with a loop and was able to get an append query to work referring to the quantity value (3) for one record from tbl_temp to tbl_main, but not really sure how to advance through many records.

For n = 1 To [Forms]![MainScreen]![Text7]
DoCmd.OpenQuery "qry_Update_Qty"
'DoCmd.GoToRecord , , acNewRec
Next n

maybe a do while or some other approach?

View 3 Replies View Related

Minimum Quantity

Aug 9, 2007

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

View 2 Replies View Related

Change Quantity To Rows

Feb 26, 2006

View 4 Replies View Related

Parts Quantity And Orders

Mar 1, 2006

Hello All,

I am new to this forum :) and learning Access :confused: .
The Database
Tables:
Employees
Products
ExpectedQuantity
PartQTY
Parts

Most of the products are similar so they have the same parts for the majority. However they do not all have the same parts. So I created a parts table that contains all the parts needed for any product. Now each Product also can contain multiples of one part (such as wheels) where I would want to list the Product and the parts list and quantity needed of those individual parts for that product. Hence the PartQTY table.

Now what I want to be able to do, is go into a form where each record is one of the products. A subform (datasheet view) for that product that shows all the parts and the quantity of each part that make up the product. Such as 4
wheels, 5 screws, etc for Product A.

I would like there to be an ExpectedQauntity field in the main form which once you found the product you can enter in the number of that product expected. Then this Qauntity would be multiplied by the qauntity of each part required (in the datasheet subform a column with =[expectedQTY]*[PartQTY]) and list next to each part the number needed to order. So if for product A the expected quantity is 50 I would have to order 200 wheels and 250 screws for that product.

A report would Sum each part qauntity after the expectedqauntity has been entered for each product. So I could print a report that tells me how many of each part we need to order based on the expected qauntity of all the products.

I think I am complicating things and would like a fresh mind to assist me in which direction to go.

Thank You,
Adam

View 9 Replies View Related

Calculating Quantity * Price In Table PLEASE HELP

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

Queries :: Sum Field Then Minus Quantity

Jun 3, 2014

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?

View 2 Replies View Related

Queries :: Multiply Time By Quantity

Apr 27, 2015

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 Related

General :: Sum Quantity In Form From A Different Table

Apr 22, 2014

I have a table called ( purchasing ) I also have a form called ( stock )

I purchase parts from stock and those quanities goes into the table ( purchasing )

Is it possible to create a text box in the form ( stock) that looks at all the table purchasing and sums up the qtyreqd for the part in stock ...

I have tried = sum([purchasing]![qtyreqd]) but just get an error ....

View 4 Replies View Related

Need To Calculate Quantity With Price In Access

Mar 25, 2013

I need to calculate qty with price in a form.

View 14 Replies View Related

The Number [quantity] Of Symbols Allowed In A Certain Field

Mar 3, 2006

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.

View 2 Replies View Related

General :: Splitting Quantity Into Separate Rows

Mar 5, 2013

I have a query that holds info for a WORK ORDER FORM. E.G The work order requests 3 staff member.

Is there a way to have a separate table or query split this quantity into three rows so i can assign an employee to each request?

I sometimes get requests for 20-30 staff and dont really want to add lines manually.

View 1 Replies View Related

Tables :: How To Break Down Repeated (Quantity Ordered) 1 Through 5

Apr 17, 2013

Right now I have a flat database with too many unnormalized fields. I am trying to figure out how to break down the repeated "QuantityOrdered" 1 through 5. "QuantityReceived" 1 through 5.

"RequestedItems" 1 through 5 and "Price" per unit 1 through 5. I'm not sure if they go into the same tables, if they are each listed 5 times and if in different tables how many instances 1 or 5 to show each as seperate and how to create a relationship between them.

View 4 Replies View Related

Calculating Quantity * Price In Table PLEASE HELP Wont Show Up

Oct 11, 2006

When I use the code given to me the program complies but it does not show the new total price in the for box
dim a, b, c
a = me.[your quantity textbox]
b = me.[your price textbox]
c = a*b
me.[your total textbox] = c
This is the code I used and my total box is called (line_item_cost)
is there anything that I am doing wrong?

View 5 Replies View Related

General :: How To Find Out Total Quantity Sold Per Product

Mar 19, 2014

I've now populated my database with all my sales information.

Now, towards plan/prepare 'replenishments' from suppliers, I need to know how many of each product I've sold (ideally between two given dates ....because recent sales history is more important than say sales information from 1 year ago), but let's keep it simple!

How can I get such info out of my database, the info is contained in my database something like this...

Code:
Order date Qty Product ID

1-feb-2014 1 widget A
2-feb-2014 1 widget A
2-feb-2014 1 widget B
3-feb-2014 2 widget A
4-feb-2014 5 widget C

(in my database, qty is declared as 'double', where Product ID is categorized as 'text')

So I'd like an 'order summary' from the above raw data (contained in my database) to look something like this...

Code:
Widget A Sales = 4 units
Widget B Sales = 1 units
widget C Sales = 5 units

How can I do that please? (I looked at access reports, but couldn't see a way of multiplying 'product by quantity' & then total it all up).

View 12 Replies View Related

Queries :: How To Calculate Current Stock (Actual Quantity)

Oct 18, 2014

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

View 2 Replies View Related

Auto Updating Total Order Cost After Quantity Is Changed

Oct 11, 2006

Ok so here is my problem:
I have a form that is realted to four differnt tables with the names: CUSTOMER, ORDER, PRODUCT AND ORDER LINE. With-in the table Order-Line there is a entity called, line_item_cost whcih is the total amount of the order with respect to Quanity_Ordered which comes from the ORDER LINE table and Price which comes from the PRODUCT table and Discount (%) which comes from the CUSTOMER table. When the users goes to change the Quantity_Ordered I need the form to automaticly update the line_item_cost which would calculate [Price]*[Quantity_Ordred]-[Price]*[Discount] . I tried to build a code in VBA to do this, Here is the Code:
Dim a, b, c, d
a = Me.[Quantity_Ordered]
b = Me.[Price]
c = Me.[Discount]
d = a * b - b* c
Me.[Line_Item_Cost] = d

VBA Complies the program but it does not show up in my form.
I think I might be updating it at the wrong point.
Any Suggestions.????

View 4 Replies View Related

Queries :: Split Quantity And Give Running / Accumulating Balance

Oct 21, 2014

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.

View 5 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

Modules & VBA :: Loop - Copy Current Record By Number Of Times Specified In Quantity Field

Sep 20, 2013

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

View 2 Replies View Related

Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

Aug 19, 2015

I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.

I have tried

Code:
Between [StartDate:] And [EndDate:]

And

Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work ....

View 13 Replies View Related