I have a form that could have duplicate material descriptions , is it possible to have on that form a field that counts the number of line items for that material and when the material changes the new number will appear .
View RepliesI have a query that I would like to put a count into but I don't know how
1. count how many line items there are in the query
2 count how many line items there are where the "days late" are
I'm creating an employee audit database, and, in the audit form, the user (ie. supervisor) can select a number of items from a listbox. Each item selected corresponds to an error that the employee has made, and, as such, the employee's Audit Score has two points deducted for each item that is selected.
Incidentally, there are other, solitary elements to the form, but this particular listbox houses a collection of items that are related under a single category.
The score is displayed at the bottom of the form, and it needs to update in real-time.
The problems that I am encountering are that I am unable to count the number of items selected and then I am unable to multiply that count by 2 (the point-value of each item on the list.)
I have 2 tables linked with a 1 to many relationship by NAME. Tables look like this....
TBL-STORE
NAME ADDRESS
Walmart 12 Nowhere
Target 14 Somewhere
Lowes 10 Anywhere
Sears 16 Nowhere
TBL-PURCHASES
NAME [NUMBER OF ITEMS] [PURCHASE DATE]
Walmart 4 1/4/14
Walmart 2 1/5/14
Target 0 1/5/14
Lowes 3 1/5/14
Sears 1 1/5/14
Sears -1 (returned to store) 1/6/14
I want a DISTINCT COUNT of [NAME] if Total[NUMBER OF ITEMS]>0, so the correct number will be 2 in the example.
This is one way I've tried to write the SQL, but I keep getting an error...
SELECT Count([TBL-STORE].[NAME])
FROM [TBL-STORE] INNER JOIN [TBL-PURCHASES] ON [TBL-STORE].[NAME] = [TBL-PURCHASES].[NAME]
WHERE ([TBL-PURCHASES].[NUMBER OF ITEMS]>1)
HAVING ([TBL-STORE].[NAME])="DISTINCT");
I also tried an Count(IiF( and can't get that to work either..
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate()
DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I have loads of projects in a table, they all have a status (eg Red, Green, Amber).
I have a query for each so if you run qry_RagRed, it will show in a sheet all Red projects and obviously have a total in there.
So I have a form which has buttons on it for navigation, what I want is to display various things on there so for example, a field that simply has the number of red projects, or green etc.
All I want it to do is tell me a number based upon number of items in a query.
I have a database that prints out a contract of line items (as a report) for the home remodeling business.
I use a query to limit the line items which show the description and cost as a subreport.
The only way I could create a total of these line items was to enter an unbound text control, and put in the formula =sum([cost]) where cost being the field name. It works fine, but I need to but the result in a currency format - - two decimals and a $ sign. I sure would like it to do it at the query level, instead of at the report level (will make it more user friendly)
Any suggestions?
I want to return the number of record with the SQL search in MS Access. I am using the following code, the expected result will be a value. But somehow, it does not give any result.
StrSQL = "SELECT COUNT(*) FROM table1 WHERE [Condition]='A' "
How do I return the count value with the search?
using Access 2000.I have a table with fields (image, info1, info2, info3).I want to produce a report where 15 records are displayed in a table like manner. each table cell contain the whole record and the table is 5 rows by 3 columns.
View 4 Replies View RelatedHow to generate prescription order form, so that I can order 10 line items at a time. I have the following tables,
Suppliers
---------
SupplierID
SupplierName
SupplierContact
SupplierAccount No
SupplierPhone No
Order Form Header
-----------------
SupplierID
SupplierName
SupplierContact
SupplierAccount No
SupplierPhone No
Date
Order Items
-----------
OrderID
ItemID
Item Name
Item Description
Item Size
Item Code
Item Qty
Items
-----
ItemID
Item Name
Item Description
Item Size
Item Code
Item Qty
I have a report that I am trying to complete based on several queries. I am trying to count the number of records based on certain criteria and using the following DCount.
=DCount("[Calculated time]","IPG1","[Calculated Time] <= 0.04" And [Ship-to party] In ("SN00207PJZ","SN09162XXX","SN09324XXX"))
I want to count the number of IPG1 records that are under .04 and have the Ship-to party of the ones listed. I have tried everything that I can think of to get it to work but can's seem to get it to. I figure it's something easy but I don't see it.
I have an Orders form (frm-Orders) that includes a subform (frm-Order Details Subform). The subform has line item totals.I want to be able to sum the line item totals and show the result on the main form, but I can't get it to work. Seems like it should be an easy thing to do.
Someone suggested I try this but it didn't work:
In the footer of the subform I created this expression - =SUM([Line Item Total].
Then in the main form I created a textbox with this -- =[Forms]![frm.Orders].[Form]![frm-Order Details Subform].[Form]![txtSum].
Any way to filter the average price of of a Product within the last 5 occurences (Line Items). It would pull a week back so WHERE: Between Now()-7 and Now(). Example:
Code:
PARTID | Price | Date
--------------+-------------+---------
111223344 | 5 | 3/1/2015
111223344 | 7 | 3/2/2015
111223344 | 8 | 3/4/2015
111223344 | 10 | 11/22/2014
111223344 | 20 | 10/1/2014
111223355 | 5 | 2/5/2015
111223355 | 6 | 2/1/2015
to:
What I want:
Code:
PARTID | avgPrice | MinDate
--------------+----------------+-------------
111223344 | 10 | 10/1/2014
111223355 | 5.5 | 2/1/2015
I just want to know how to manage items in set and individual item. Suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K
and 5 pc set = A,B,C,D,F
and 3 pc set = G,H,K
How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
E.g. order is for 5 pc set = 3000
A=3000
B=3000
C=3000
D=3000
F=3000
Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.
I have a database for booking items in and out and would like the database to automatically count the number of items that are overdue.
I have a COMPLETED field which is a check box, when ticked completes the booking, if it is not ticked then the booking is incomplete.
I also have the "Date Returned" which is the date the customer is supposed to return the items.
So using these two fields how can I count the number of overdue items?
if COMPLETED=false and Date Returned<Date() +1
that's basically the logical expression I think would work, but I don't know the code/method to implement this into my database :x please help!
Hello all,
I am new to this forum. I have this problem where I need to sort the 4 books in the tables below according to the highest quantity of book sold.In this scenario,
Book C would be 1st in the table with a total quantity of 8.
How do I write a query in access to perform such operation. I have to use DISTINCT to get each book and also count the quantity(e.g. Book C:5+8).
But it seems that I can't use Count and distinct together in Access.
Title SalesNo Quantity
BookA22
BookB 31
BookC 45
BookC 53
BookD 6 1
Hope u all can help!
I'm building an order entry database. It has two tables (amongst others) called tblOrders and tblOrderDetails, related together on the OrderID field. For every order, there is one record in tblOrder, and as many records in tblOrderDetails as there are individual lines in the order (so, if, for example, the order is for 10 pencils and 2 pens, then tblOrderDetails has 2 records).
Each entry in tblOrderDetails has a Status field, which indcates whether or not the items have are in manufacture, shipped, delivered, etc).
I need a way to get Access to show me only "Open" orders (i.e. ones in which not every item has been shipped). Can I set up a query to determine how many lines each order consists of, and then is there a way to get access to check if all of these are "Shipped"?
Many thanks in advance for any pointers,
Gary
Any idea how i can count the number of records that have the same order number of the one on the report list? For example, there are 3 records in my query that have Order# 1001, I would like it to show a "3" next to order 1001 in the report.
Any idea where and how I would make this happen (in the query or the report)?
Let me know!
Thanks!
ovadoggvo
I'm stuck on this one part of my code where I am trying to count values in a column called 'ItemQty' in table 'dbo_Item'. I only want to count the values with the associated values in column 'OrderNumber'. I am getting the values of 'OrderNumber' from an array. Here is my code...(it doesn't work though. When I put a Msgbox to print out what the ItemQuantity value ends up to be, it only prints my code back to me.)
Code:
For Each Order In q
MsgBox "Order = '" & Order & "'"
ItemQuantity = ItemQuantity + ("count(ItemQty) Where OrderNumber LIKE '*" & Order & "'")
Next Order
My Data gets one of 4 labels: A, R, N, X. I want a pie chart that shows how many of each I have.
I created a query (StatusCounts) using Group By and Count. It lists "OrderStatus" and CountofOrderStatus.
Whenever I use this query to create a chart I get the default Access data not the data that's actually in that query.
I have a table with the name of a report, the items on the report, and the number of items on the report. I would like to count the instances of the names of the individual reports that appear on another table and then divide that count by the number of items on the report.
View 1 Replies View RelatedI would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
View 8 Replies View RelatedI try to explain my problem.
There is a report have 50 records.
I print the report first time and Set the number by Data>running sum method. This start from 1 to n...
I require a Form Where i enter to initial number example 22 and say preview report.
The report Set the line number of Details Section Like
22
23
24 etc...
How can i do this. Please help
There are 3 drop down button that should filter category of items in a database:
Category 1
Category 2
Category 3
Whatever users choose in category 1, it will filter the items in Category 2. whatever users choose in category 2, it will filter the items in category 3. users can choose 1 to 2 categories or sometimes the 3 categories. the items will show in a subform. the subform are getting the items from a query. the dropdown button gets its value from a Table.
My challenge is that, i want to get the number of items in Category 1 once users chose it. if they choose something in Category 2 the number of items will change also. there will be 3 textbox for Category 1 COUNT, Category 2 COUNT, and Category 3 COUNT.
as of now, what im getting is only the filtering of Category 1 and Category 1 COUNT.
I want to have line item numbers on report look like this:
Number....Order Number
1............9533-1425-20
2............5866-3411-14
3............2332-2355-14
3............2332-2355-14 ->repeating item
4............4399-5208-12
5............7392-2305-62
5............7392-2305-62 ->repeating item
6............2332-2355-14
Each new row of record needs to have their own line item number starting from 1 with criteria if order number is the same then carryover line item number on that record. I know how to get line item starting from 1 to last by assigning unbound control field with entry "=1".
Like other posters, I need the default value to be the last value plus one. I am using a subform, and the field I need to increment is ITEMNUMBER for items that are ordered for a specific project.
It is acceptable to me to replace ITEMNUMBER with 1 for the first item, I just want it to increment after that.
Clearly the use of DMAX in this situation will not work as ITEMNUM would continue to increment so that any given project might have ITEMNUM values as, for instance, 357-363 when it needs to be 1-7.
BTW - if I could use the maximum value I would set the default value of ITEMNUM on my form as:
(DMax("[ITEMNUM]";"Projects"))+1