The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
I need to retrieve the count of emails in a shared exchange mailbox in Outlook 2010. I found the following software that retrieves the count I need, but only in default folders or ones that are 1 layer deep. The folder I need is two layers deep Mailbox -> Inbox -> Completed. Is there anyway to drill down just one more level?
Const olFolderInbox = 6 Set objOutlook = CreateObject("Outlook.Application") Set objNamespace = objOutlook.GetNamespace("MAPI") Set objInbox = objNamespace.GetDefaultFolder(olFolderInbox)
I have a fairly simple query to weed out all the records in our database that are missing vital pieces of infomation :
Code: SELECT Contacts.Name, Contacts.Address1, Contacts.Address2, Contacts.Town, Contacts.County, Contacts.Country, Contacts.PostCode, Contacts.Telephone, Contacts.Code FROM Contacts WHERE (((Contacts.Address1) Is Null) OR ((Contacts.Address2) Is Null) OR ((Contacts.Town) Is Null) OR ((Contacts.County) Is Null));
Is there anyway to count how many fields are missing for each record ?
This is kind of a weird problem, and everything I've learned about access has been on my own through empirical experimentation so forgive my if my description doesn't make sense, or if I overlooked something really simple. (Apologies for that, I've looked over all the guides on queries and record counting I could find and got nothing on this.
I can't figure out a better way of explaining my situation so I'll do the best I can.
I need to make a query that shows the number of people that have registered for a class. In one table I have the primary key Schedule ID which is the unique class, which is linked in one-one relationship with another field of the same name. In the second table with that field I have the field schedule ID which shows up multiple times, for each student enrolled in the course, second is the field Participant. There is the following data: Schedule ID...Participant 1 ................ <name1> 1 ................ <name2> 1 ................ <name3> 4 ................ <name4> 4 ................ <name5>
What the result of the query should look like is this:
Schedule ID...Number of participants 1 ................ 3 4 ................ 2
But instead I get Schedule ID...Number of participants 1 ................ 5 1 ................ 5 1 ................ 5 4 ................ 5 4 ................ 5
I have tried various variations of the Count and Dcount functions but I always wind up with that result (if it works at all)
This is the expression I am using:
Expr1: DCount("[Schedule ID]","Class Roster")
where "Class Roster" is the name of the Table that Schedule ID is located. I have tried changing it out with the name of the first table, and I have tried using participant instead of Schedule ID, but neither works. So far I've been able to use a crosstab query to get something roughly what I want but I think the assignment calls for a regular query.
Again, sorry if this doesn't make any sense, I did my best to explain it, and I couldn't find an answer anywhere. Thanks in advance for any help!
Oh, by the way, it's access '03 that I am using.
UPDATE: I have a second problem I'm trying to solve, and this one seems more confusing than the first to me. I have a table with the following fields:
Project Name, Task name, employee ID, Production Week, and Hours Worked. The first two being text and the last 3 being numerical.
I need to create a query that totals up the hours worked for each employee (one employee ID can show up under multiple projects/tasks) and returns the total hours in a particular week (week 20) of each employee that has worked over 40 hours, and only the ones that have worked over 40 hours. I can not figure out how to create a field that will sum up the hours worked for a given employee, nor can I figure out how to show only rows where the total hours record exceeds a certain amount. It seems like I would want a conditional like an IIF statement but those seem to be only for returning values, not showing/hiding.
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 "
I am basically looking for a way to display the total number of records for a selected number of tables and queries in my database on one screen or on one single page view. The problem with using reports or forms is that they tend to bind you to a single table as a record source. This summary would require multiple tables and queries. The summary data would look something like this with a total coming from a Count() or count type function:
# of records: 3000 # of records to insert: 500 # of records to delete: 400 # of records to update: 2100 # of records changing location: 100
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings FROM tblBookings GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0 The Hilton. Bookings 3 The Carlton. Bookings 0 The Lowry. Bookings 2
Just spent the past hour in here trying to nut this one out, but not sure I've found something quite the same...though I know the answer will be painfully simple.
I have a customer table and a product table, and a query that groups customer first and last names along with a count of products per customer e.g. 1,1,3,2,3,4,2,1 indicates customer A buys qty 1 of product z, customer B buys qty 1 of product x, cust C buys qty 3 of product y and so on.
All I need to do now is do something to also output the total number of products. ie as per example above, 1+1+3+2+3+4+2+1 to get 17.
Can I do a count of the count or do I do some sort of sum of the count results?
I've tried everything I'm capable of as a newbie, and I'm not having any progress.
Hey all, I was wondering if anyone might know how to obtain a reocrd count, I need to create a PERMIT number based on the number of records in my table. So I need the number of records in my table plus 1, which will then give me half of my permit number, I will then add the 2 last digits of the current year and add a "-" I think I have the date done, but I dont know how to obtain the number of records in my table? Can anyone help? Thanks
:mad: I have created a database to hold information on non conformances we have with our suppliers, I am really stuck, what I am looking for is to put a field into a table which counts issues with the different suppliers, if anyone can help it would be much appreciated! Thanks
I'm building a health data system that stores data from health tests as follows: [test01], [test02], [test03],.........[test25], etc etc. Each of these tests comprised of a "Pass"/"Fail" value.
Now, i'm building my queries for a feedback report to hand over to the client that is based solely on the 1 single client record. In other words the queries are designed to return only 1 single data record to be placed in the client report where the based on the corresponding [TestID].
So within this 1 data record i want a query to count the number of times "Pass" appears in the test fields?
As there are some 40-50 [test##] fields in this one test battery i don'y particularly want to go back and code an IIf statement for each component if i can help it.
I have a query that updates 1 field in tableA which is the sum of values from 3 other related tables. However, when 1 of the 3 tables has a record count of 0 then the query does not work??? How can I update tableA excluding tables with record count <1 ?
I am working through a lot of data and this is basically what I'm trying to do.
Let's say we have billed occurances for ABC Pet Store. What I need to do, is to see how many "billed lines" appear in the data. So if there are 3 billing occurances (let's say bill amount greater than zero) - in the data I want the query to populate a "billing occurances" field with "3."
Cant find what I am looking for on the forum so if anyone can help me or tell me what to search for that would be great...
I need one of my forms to display a running total of updated records, but only those records that the user clicks a command button on. i.e if i have a recordset of n records each time I move to the next one (by clicking a command button) I want the control on the form to display the count of those records that have been updated only.
I am assuming I would need to add some code to the after update event of the form or on click event of the command button but cant really figure out what code to write.
In have a table which contains customer address details. I simply want to display a 2-column list. the first column to display a disnct list of city's from the main table. the corresponding column must give the no. of records that contain the entry in the first column.
eg if the main table had 5 customers that lived in london and 6 in Maidstone then
I have two tables - one contains customer names, the other customer appointments. So one customer - many appointments.
Each appointment is booked at a set interval (every 3 weeks, 4 weeks, 5 weeks...) which can vary from one appointment to another.
I want to do a count, in a query, to show in a report.
I need to count:
Total Cus_ID by interval - so how many customers are booked every 2 weeks, every 3 weeks, etc.
I need the count to be based on the customer's LAST appointment only.
I have tried, select query (group), crosstab (!)... querying a query... Total line using Max... then Last...
Nothing I tried works. The sum of appointments by interval should equal the total number of clients in the database... It gives me 4 times that... so it is counting every appointment, not just the last appointment entered.
I also will be including two other fields: activecustomer = yes and source = Eve - need to know criteria to set.
Does anyone have a good technique for enforcing a minimum number of records in a table?
I am attempting to ensure that in a table of Roles, there be a minimum of two Roles defined as Technicians. The field which identifies a Role as Technician is a Boolean.
I wrote a Function which does a domain lookup to count the number of Roles defined as Technician and am calling it from the BeforeUpdate event.
However, if there are 2 Technician Roles, and I mark a third Role as Technician, change my mind and try to unmark it...of course the Function can still see just the 2 existing.
I realize I can Undo the edit, but a user may not.
I’ve attached a zipped example of the problem I am having with customised navigation buttons.
In my main database the form “subfrmPersonsContact” works perfectly. I have exported it to a new database and now the customised record count fields do not work at all – they are in fact blank even though the navigation buttons do still work.
The code running the form and the record count fields is listed below – I have no idea why it’s not working.
Any help would be greatly appreciated.
Cheers,
Rusty :D
Private Sub Form_Current() On Error GoTo err_Form_Current
Dim rs As Recordset Dim Count As Integer, Position As Integer
Set rs = Me.RecordsetClone rs.MoveLast rs.MoveFirst
Position = Me.CurrentRecord Me!txtRecPos = Position
If Position = 1 Then Me!gotoPrevious.Enabled = False Me!gotoFirst.Enabled = False Else Me!gotoPrevious.Enabled = True Me!gotoFirst.Enabled = True End If
If Position = Count Then Me!gotoNext.Enabled = False Me!gotoLast.Enabled = False Me!txtRecCnt = "of " & Position Else Me!gotoNext.Enabled = True Me!gotoLast.Enabled = True Me!txtRecCnt = "of " & Count End If
rs.Close
exit_Form_Current: Exit Sub
err_Form_Current: If Err.Number = 3021 Then Resume Next Else Resume exit_Form_Current End If End Sub
I've searched the database and found similar problems but don't understand the replies (mainly DCount()).
I have a control form where a user can search the database by entering information. The form asks them to type some text that will appear in a description field, and match any records with the same text.
However, when the user types the text and clicks the button to open the form (which is linked to a query searching the description field), I want a message box to say no records found before opening the form. Mainly because when it doesn't find a matching record, the form opens with a new autonumber.
I either want a matching record to appear, or a dialog box saying no matching records found and the form not opening therefore not creating a number that is not used.
I tried in the command button entering if recordcount>0 then openform but it doesn't like the else statement or doing a if recordcount=0 underneath.
I've only recently gotten to know about continuous forms and their use a bit. So for example, I used a maximum number of pre-defined (6) visits. I need to change that still, but during the development of the sales forms I already ran into a problem.
At first I was planning to have a certain maximum number of items to have on a form and create an invoice from that. But of course that would limit the number of items on one invoice which isn't good. So, now I have a continous form which adds a line containing productname, price, nr of that item etc. anytime needed.
In the previous design idea it would have been easy to calculate the total amount of money at the end of the list since the list was predefined. So I could just say invoiceamount = itemID1amount + itemID2amount + itemID3amout etc. With the "new" design however that is impossible to do since the number of objects is unknown. So how do I create a piece of code which does that? In the continous form I have a SaleID which keeps track of which items belong to a certain Sale and a TotalItemsID autonumber which keeps track of the number of lines (items) which belong to that specific Sale.
So I need to count the total number of TotalItemsID's within a certain SaleID and then have a piece of code which creates a sum of that counted list of items.
I'm new at this, I've found the function Dcount() should be used to accomplish this, but the rest, as of yet, remains a mistery to me.
I have a Report Generated on Access Database, the report is 56 page now i'm tring to put the total records on that report, how do i do that ??? pls help
2) If I move rst to last position, number of recordcount is correct, but! If there's no record in my string I get an error that rst can't move to last position.
2) If I move rst to last position, number of recordcount is correct, but! If there's no record in my string I get an error that rst can't move to last position
The filter output from a recordset in the following code is 1. But this is incorrect: How can this be corrected?
Private Sub Command43_Click() Dim curDatabase As Database Set curDatabase = CurrentDb Dim rs3 As Recordset Dim t As Recordset Set rs3 = curDatabase.OpenRecordset("Select * from [Courses under Programs]") rs3.Filter = "ProgramCode = 'ANS.CT'" Set t = rs3.OpenRecordset t.MoveLast t.MoveFirst MsgBox t.OpenRecordset.RecordCount End Sub
In my access database I am using a form and at times a record may need to be updated multiple times. Is there a way to track and count how many times a record has been modified? I already track the last modified date and time.I searched and was not able to locate what I am trying to do, and maybe my search term was not exactly correct.
