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.
I have another problem with a query I am doing today. When I run the query it brings up everything I need, but 5 of the Promo#'s are the same, but they have different # of Executed in them. They should not be like this, they should be together in 1 Promo#, not broken into 2. Here is my SQL code:
SELECT [Promo count].PromoNo, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo AS [Executed Demos], IIf([Promo Executed]![CountOfPromoNo]=0,0,[Promo Executed]![CountOfPromoNo]/[Promo count]![# of Demos]) AS Percentage FROM [Promo count] INNER JOIN [Promo Executed] ON [Promo count].PromoNo = [Promo Executed].PromoNo GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo;
Its taken from 2 queries Promo count and Promo Executed.
I have a table which displays playerID, YearID, RosterID, and Games played. Then I have a query that looks like this
SELECT Positions.playerID, Positions.YearID, Positions.rosterID, Max(Positions.[Games Played]) AS [MaxOfGames Played] FROM Positions GROUP BY Positions.playerID, Positions.YearID, Positions.rosterID;
My problem is that I dont want any doubles so if a player have 2 rosterID's in 1 year I just want to see that roster ID associated with the MaxofGamesPlayed. How do I do this???
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 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.
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 a novice to db design but I started my db quite a while ago but have only just noticed it keeps doubling in size periodically, even though I have not added many records or changed much. The first time I noticed this phenomena it was at 65Mb (already too big?), it then periodically (and randomly) doubled in size until it was about 480Mb!! I have been told that when db objects are deleted they are not actually deleted and this would explain the size explosions I saw. I have compacted and repaired the db and it now stands at a much more likely 11Mb. However, I really wanted to understand why this occurs so that i could prevent it from reoccuring (I have set my db to compact on close now - which seems to keep it small in size).
I have a whole load more questions aswell but you will see those in the near future!!
Basicially I have two tables, which are Linked tables and they are linked to two Excel files. Each of the tables has almost the same information: Stock Number, Location and Lot.
The purpose of this query is to check Quantities on both spreadsheets to see if there are any discrepancies. Query works fine if Stock Number, Location and Lot are the same on both spreadsheets but if they differ somehow i.e. Stock Number and Location are the same but Lot is different then Access doubles/triples records.
I have a feeling there is an error in Relationships. I googled it and I may need to set-up many-to-many relationship but the problem is that Access does not alow me to do it. I created a junction table but then I cannot set-up one-to-many relationship between tables, which I think is because tables are Linked.
Code: SELECT DISTINCT [Query Union].[Stock Code] AS SKU, [Query Union].[Location Id] AS [Location No], [Query Union].[Lot No#] AS Lot, IIf(IsNull([TMP2 SOH].[Physical stock]),0,CDbl([TMP2 SOH].[Physical stock])) AS [TMP2 Qty], IIf(IsNull([TMP1 SOH].[Good Stock]),0,CDbl([TMP1 SOH].[Good Stock])) AS [TMP1 Qty], [TMP1 Qty]-[TMP2 Qty] AS [Variance (TMP1 - TMP2)] FROM ([Query Union] LEFT JOIN [TMP2 SOH] ON ([Query Union].[Stock Code] = [TMP2 SOH].[Item number]) AND ([Query Union].[Location Id] = [TMP2 SOH].[License Plate Id]) AND ([Query Union].[Lot No#] = [TMP2 SOH].[Lot number])) LEFT JOIN [TMP1 SOH] ON ([Query Union].[Stock Code] = [TMP1 SOH].[Stock Code]) AND ([Query Union].[Location Id] = [TMP1 SOH].[Location Id]) AND ([Query Union].[Lot No#] = [TMP1 SOH].[Lot No#]);
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
I delete the old 10MB table and import a new 10MB table and now the the DB is 20MB - why is not 10MB - number of records is about the same not double. DB works fine and you can't tell any difference except the size.
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:
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.
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
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.
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.
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.
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?
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).
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
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.????
