I can't seem to figure this one out on my own; I'm trying to create a query that counts the number of values in fields across a row that match certain criteria. For example, the data I'm querying looks like:
and I need to find out, for example, how many numbers for each id are greater than or equal to 70 but less than 80. I've tried using DCount, but it gives me the number overall that match the criteria instead of the number per row (so I'm sure I'm doing it wrong). I've tried using a crosstab query, but I can't suss out how they actually work when you've got more than one column of data that you need to work with unless I put ID as column headers and the other fields as row headers... that would make my query about 1500 columns wide and 10 rows long, though, which just seems wrong... ?
In a slightly different approach, I've done another query on the data that makes it look like:
ID Number First Second
1 01 85 76
1 02 68 72
1 03 67 63
1 04 74 73
2 01 32 34
2 02 70 65
2 03 67 80
2 04 68 70
But again, I keep ending up with silly answers (like the count for the entire set of data, rather than just per ID). I'm *sure* I'm just not quite getting how DCount works... and it's possible I'm trying to use it for something for which it wasn't designed. Any alternative examples would be most helpful.
I'm not sure how to compress the data any further. I could, technically, get it down to two columns (ID and <number to query>), but the only way I can see of doing it is to create a temporary table for the first column and append the second column's data to it, which I'd have to do every time any of the numbers changed.
I would prefer to use the first approach, as that query is useful in other calculations and the second is a bit of a faff (though not too bad, just a union query). Anyway, I'm just stuck and instead of continually banging my head against the wall, I thought I'd ask you kind folk for help. :)
I have three tables: tblProducts1, tblProducts2 and tblProductSales.tblProducts1Code CostABC 20BVC 35ABC 30tblProducts2Code CostABC 10BVC 55ABC 20tblProductSalesCode RevABC 70BVC 25ABC 20BVC 15DCC 33I want to produce a query that looks like this:Code Rev Cost ProfitABC 90 80 10BVC 40 90 -50DCC 33 0 33How can I do this?Thanks,Jon
I have a table that records log entries for equipment and I'm trying to compose a query that calculates the time difference between consecutive timestamps for individual operators but can't quite get it to work out
The table headings are
Fatigue Log ID <----- this is the primary key for the table and is unique
I have a form that is bound to a query that returns all records matching a set criteria (namely, the field ApprovalDate is null); the idea is that the user clicks on a command button which gives information, and if the user chooses to continue (vbYes), then the field ApprovalDate has its value set as the current date, and the record is saved.
Next, I have put in DoCmd.Requery to requery the form and effectively go to the next record wanting approval - this appears to work fine, however I need to add something to the code that handles a null return from the requery - preferably a message box and then have the form close.
I'm looking for a way to grab values from one table that match a criteria, and append them/update them to another, but only grab one value once. Example:
I have a database of parts that fit in certain profile sizes. Each profile size has multiple open locations. I need a query to grab a location, assign it to a part, grab a new location, assign it to the next part, etc, without assigning duplicate locations to different parts.
Right now, I'm doing this by using the First/Last aggregate functions, marking them as used, and looping it again and again until all have unique locations. Is there a better way to do this?
I have 2 tables that must be involved in this query. Both have the same fields; one is a temporary table created from an Excel import which will be deleted later, the other is the permanent table.
tblStoreProducts is the permanent table. It has a 2-field Unique index; there can only be 1 of any ProductKey for a StoreKey (there can be multiple ProductKeys within a StoreKey, and multiple StoreKeys for each product, but there can only be 1 entry for each unique combination of ProductKey/StoreKey).
tblImportToStoreProducts is the temporary table. It holds all of the ProductKeys for one StoreKey.
My query needs to identify any ProductKey in the tblStoreProducts that does not exist in the tblImportToStoreProducts for that StoreKey, and then it must set the MaxUnits field to 0.
Basically, the temporary table is this years' inventory for a particular store. If a product was in that store last year but it isn't there this year, the quantity must be set to 0 because the ultimate aim is reporting hazardous materials.
I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error
Private Sub Command5_Click() If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then MsgBox "You welcome" Else MsgBox "Wrong username or password" End If End Sub
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
1. a sum of payments by individuals 2. a sum of charges of individuals.
I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).
I have searched similar posts to solve this, but havn't been able to make it work yet, so apologies if this sounds like an existing post.
On my form (frmContacts) I have a combo box (LstCompany) that lists companies. The default value is set to "N" (so this is the value when nothing is selected).
I have a query (qryContacts) that retrieves records of People and their Companies from a single table (tblContacts - this table includes the fields 'Person' and 'Company'. Some people have no company). I want this query to:
- When no company is selected in LstCompany: show all people (whether or not they have a company). - When a company is selected in LstCompany: show records for that company.
I have added the following expression to my query: Expr1: IIf(([Forms]![frmContacts]![LstCompany])="N",True,([Forms]![frmContacts]![LstCompany]))
Criteria = True
This works fine for showing all records, but the Expr1 field returns #Error if a Company is selected.
Any idea what I am doing wrong, or what will work?
I need for Access only to sum the "hours worked" of my payroll data, only if another field, "pay code" meets a certain criteria.
In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as "premiums" to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.
I want Access to 1) Group By Cost Center, 2) Sum "Hours Paid" that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.
Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
Cost Center Job Type Hours Paid Pay Code Earnings Amount
Is there any way it would be possible to have a number automatically created using NZ() or DMax function; based on certain criteria and also when creating the number preventing a duplicate number creation in the even of a simultaneous record creation?
Here's my example:
I have a CCN Number (Currently the employee manually types it in; and it is the primary key. What I would like is to take out the human element (error possibilities) and let access create it for the employee.
Here is how the CCN looks and the breakdown of each section (the important part's will be the first 3 sets of numbers):
96-06-184-100-000
96 = Region Code 06 = Current Year 184 = Julian Calendar Date (based on the receipt date field) 100 = Filler number based on day of the week (Eg. Monday = 100, Tuesday = 200, etc.) 000 = Filler numbers (Should always be Zero's (000)
I need to find away to pull this information from a table of some sort and combine it to create the CCN and to then add 1 (incriment) to the 4th set of numbers. Example 100 (1st record of the day) 101, 102, 103, etc.
I'm having some difficulty obtaining the answers I want from my query. I'd have been more comfortable using filters in excel to find the answers, but my dataset is too large in this instance.
I devised a query which I thought should achieve the results, but based on a subset analysed in Excel the answers are significantly different.
My table has about thirty fields, the query I've produced is this: SELECT AH_orphans.Product, AH_orphans.Comments, AH_orphans.ESP, AH_orphans.[ESP Parts], <snip, all other fields in table> FROM AH_orphans WHERE (((AH_orphans.ESP)="n") AND ((AH_orphans.FRU)="y") AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a")) OR (((AH_orphans.ESP) Is Null) AND ((AH_orphans.FRU) Is Null) AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a"));
What I'm hoping for is the subset which match the following conditions: - ESP field is not "Y". Possible values Y, N, null - FRU field is not "N". Possible values Y, N, null - Only those records from EntitlementName which include the substring 'uptime' - Only those records from Account which start with the subtring nation - Only those records from Type which include the substring primar - Only those records from [Is a parent?] which equal #N/A.
Asking the same questions in Excel I get around 1.5k records. If I use the quoted query on the same dataset in Access I get precisely 11.
Thanks for reading this far! If anyone can spot the flaw/s in my query, or indeed advise that I'm going at it completely the wrong way I'd be very grateful of some advice.
I have been working on a query in my database and I would like to look up a Number Value from another table based on a Number ID field and the Number Date in the Number Values table, where the Number ID field in the table agrees to the Number ID Index field in the query, and the Number Date field in the table agrees with the Initial Date field in the query. The Dlookup syntax I currently have is as follows:
The Dlookup shown above does not return an error, but it returns a blank field. I know that individually, the Dlookup for the Number ID works, but when I add the And for the Number Date the Dlookup returns a blank. Also, I have verified that there are existing records where the Number Date and Initial Date fields agree.
Also, I changed the format of the date fields in the tables to be ShortDate, thinking that the Timestamp was causing the Dlookup to not find a match. I also tried to use the DateValue() function within a query to reformat with not luck.
If I want to sum the percentages from April, May and June only if a column is Not Null, how would I do that?
example
Tbl 1 PK, Month Percent
Tbl 2 FK, Month Enrolled Qty of Rx in the 1st month enrolled Qty of Rx in 2nd qtr Base (if Qty of Rx in 2nd Qtr is null then Qty of Rx in 1st month enrolled)
If Qty of Rx in 2nd QTR is NOT NULL then QTY of Rx in 2nd Qtr * Sum of April Percent+May Percent+June Percent, otherwise Qty of Rx in 1st month enrolled * Month Percent
Im getting stuck on how to sum the percents of April, May and June and then multiplying the result times the Qty ONLY IF the field is not null.
I only know how to create Query's using the design mode. I dont know how to write SQL statements.
I need to count the number of entries for a particular item located in a table. To keep it simple, we have a database used to process orders for Turkeys, this database has a field for each order called "Long legged Weight". In order to sort the turkeys in the shed, we need to know how many of each weight there are.
Order Number / 10-12 / 11-13 / 12-14 / 13-15 / (etc) ......1 ................1 ......2 ..........................1 ......3 .....................................1 ......4 ..........................1
Query that I have built to create a subform on one of my forms. It's my goal to make the subform easily navigable/query-able for the users, and that is where I've hit a roadblock. The subform contains a field - Balance - which I would like users to be able to search based on numeric/mathematic expressions (i.e. >0 and <40). In testing I have created a text box on the main form (BalanceCriteria), and linked it to the subform's balance field through the query in the Criteria field (forms!MainForm!BalanceCriteria).
This works fine with exact numbers - entering 19 will return client's with a balance of 19 - but returns an error - "Expression is typed incorrectly or is too complex to be evaluated" when tested with a numeric equation (>0).
I'm using the following code to generate a sequential number"
Code: Private Sub Form_Load() Me.txtReceivedDate = Now() 'assign an EmailID when form loads Dim CurMax As Long Dim NewMax As Long 'poll the current EmailIDs for the largest number from current year and add 1
[Code] ....
Until recently, I didn't have any criteria on the DMax and it worked perfectly. It's now been decided that the EmailID should reset back to 1 when the calendar year changes.*
*EmailID is not my primary key - There is a separate autonumber field used as the primary key.
The table I'm using the DMax on has a field named ReceivedDate which stores a Now() when a record is created. The form I'm working in has a textbox named txtReceivedDate that is set to Now() when the form loads as can be seen in the code above.
What I'm trying to use as my criteria in the DMax is to match the Year() of ReceivedDate in tblEmails to the Year() of txtReceivedDate on the form.
For 2013, the current max (CurMax) EmailID is 21. If I set my computers date back to 2012 (where the CurMax is 3) the txtEmailID is still being populated with 22, even though txtReceivedDate shows a 2012 date.
I've tried a handful of permutations of the criteria string:
I've tried a few others long the way that I don't even remember, but there's probably little value in listing all the ways that don't work.
I've got a syntax issue within the criteria since it's the first time I've tried to use one and it contains a couple moving parts.
Once I get this sorted out a follow on question is about dealing with the users changing the txtReceivedDate to a different year. As of now, I have the same code (everything except "Me.txtReceivedDate = Now()") also present in txtReceivedDate_LostFocus(), but I'm not sure that's the best place for it.
I have a RN generator that looks at 9 different lanes and puts in a number based off of a certain criteria but I was wondering if in the frmLanes the actual "Go" button was an incoming number how could I get my current random number generator to look at that number and determine if the current number is within 5 of the "go" number?
Any way to have a beginning number that I would enter into a text box and have the number generator kick off based off of that number. I have included a copy of my db for viewing.
