I have a query that will sort groups of zip codes using the following expression: Between [T_ZipRange]![StartZip] And [T_ZipRange]![EndZip]. The first and last zip codes are entered and the expression returns the zip codes between the "start" and "end" range. The problem is that if a zip code is only 5 digits: xxxxx- instead of xxxxx-xxxxx, the zip code will show up in other sorts. The zip code field is a text field. How can I limit the sort, based on the number of digits in the zip code?
I have a large data set with values ranging from 1-25,000. I would like to be able to calculate how many entries there are in each increment of 10 (1-10, 10-20, etc.), then perform the same query for each increment of 100. Any ideas?
I have set up a form to run a query with the criteria linked to the options/drop down menus on the form. I am able to search for criteria set out in the combo boxes. However, what i would like to ensure is that if the drop down boxes are left blank the results will be to show 'all' results for that field.
I import data through different files into a table, the data comes in different formats. I have a problem sometimes some records have double quotation marks and carriage returns. I know how to replace them as
Carriage Returns
Replace([FieldName],Chr(13) & Chr(10),""
Double Quotation Marks
Replace([FieldName],Chr(34),""
But if i want to use an iif statement because in the field there could be a Double Quotation Mark or Carriage return then it does not find the record
iif([FieldName] like Chr(34),Replace([FieldName],Chr(34),"",[FieldName])
creating a query with a running sum (cumulative total) across two categories. I need to accumulate Wages by employee, by calendar year for every day/every job worked. I have a table containing over 33,000 records, the years span from 2009 to 2014 with multiple employees.
The Dsum option I have found on the internet is too time-consuming and locks up my query for the over 33,000 records I need to generate results for. It needs to be a SQL statement. Although all the SQL statement I have tried simply total all Wages for every entry ever made in the column, instead of per calendar year, per employee.
Here are my fields: Calyear = Ascending Employee Name = Ascending WorkDate = Ascending ID
[code]...
There may be more than one entry per day per employee in a given year. This is so I can calculate certain payroll taxes which are based on cumulative wages amounts.
I've a club database the records score for juniors on age groups that are set as of the 1st January each year. I would like to set this automatically from the date of birth. I have found some code that is very close to what I need apart from the fact it groups on the exact DOB resulting in changes to age group mid year.
'----------------------------Code Start-------------------------------- Public Function AgeGroup(dtmBirthDate As Date) As String Dim intAge As Integer 'Age Calculation intAge = DateDiff("yyyy", [dtmBirthDate], Now()) + _ Int(Format(Now(), "yyyy") < Format([dtmBirthDate], "yyyy")) Select Case intAge
I have a number of groups in a particular database. These are based on the month that they relate to, but they are also split into four separate groups for each month. So for example, I will have four groups for November 2014:
November 2014 Raw Data (which contains linked tables in another database) November 2014 (which contains the bulk of the queries that are used for processing) November 2014 Reporting (which are the queries that are used for month end reporting - obviously) November 2014 Misc (anything that doesn't fit into the above, although there are always queries in these groups)
Now, I have been using this database since 2012, so as you can imagine there are quite a significant number of groups. Adding queries to new groups for each new month is therefore starting to take a while by scrolling down to the bottom... Is there a way to do this by using VBA? It would save a bit of time each month.
I have a query that lists the total number of parts received by month. I would like to be able to compare those monthly totals with a rolling 12 month total. I've been able to get the rolling 12 month total, but I can only get it to work for one 12 month period.
Is there a way to get a 12 month total for each month? In other words for April, I would like a 12 month total of May 2013 through April 2014. For May, I would like a 12 month total of June 2013 through May 2014. Is that possible? If so, how would you go about doing it?
I have a field in my table titled REL_MONTH that shows a negative number for how many months ago something is (i.e. 00 = May, -1 = April, -2 = March).
I have built a query that lists all the members in a group aged 15 to 18. Now I need to build another query that counts a subsection of that group (the number who gained employment in the quarter) and express this as a percentage of the whole of the 15 - 18 group.
I presume I can use the original query to get the number of 15 - 18 year olds. I can write the query to calculate the subset of 15 - 18 year olds who gained employment, but how do I get the total numbers of the groups and express them as a percentage?
I am trying to do is to make a for loop to insert multiple text fields in on table.
Depending on the counter (Zaehler) it should insert that representing text field. for example if Zeahler is 1 it should input whatever is inside the text field KVP_Kfm1 if its 2 then it should input the textfield KVP_Kfm2 and so on. here is the code that I'm trying to work but sadly it wont.I believe that the mistake is that my syntax is wrong but i cant figure out what is right.
Code: For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr) SQL = "INSERT INTO tblLifecycle_Projektion(ID_Berichtstand, KVP_Kfm, KV P_technisch, AeJ, MoPf, skAe, MiBst, Sonstige_Effekte, " & _ "KVP_technisch_FTR, KVP_Kfm_FTR, AeJ_FTR, MoPf_FTR, sk Ae_FTR, Sonstige_Effekte_FTR, Jahr) VALUES " & _
There are about 75 distinct sets of ID's and Names not shown here
The rows were sorted by the Mtr_Readings and we have to take the best four readings out of each group and average them. If a group has more than four rows then the top 4 are taken out and the balance is discarded. If the group has four rows or less all of the rows are taken.
I did try nested select statements and kept getting "invalid argument to function" exceptions.
I have a macro that runs a 'make table' query followed by a series of 'append queries' each time the macro is called. I require the data from each append query to be pasted below the existing data already created by the original make table query and the append queries that have already run. The issue that I am having is that the data in fact appears to be appended in an unpredictable way (ie sometimes I run the macro and find that data from the append queries is below the data from the make table query, at other times I run the macro and find that data from the append queries appears above the data from the make table query. Unfortunately, this causes problems with downstream queries, which rely upon finding the last row of data subject to various filters. Can anyone help me understand how Access determines the sorting/positioning of new data appended to an existing table, and therefore resolve this issue?
I have a set of two queries (which I'll call A and B, for convenience). Query A reads a table containing customer order numbers, a sequentially-assigned line number for each item requested by a customer on a particular order, and a part number associated with each order line. The line numbers are not integers, but run out to three decimals - so 1.000, 1.100, 1.150, 1.200, etc. would all be valid line numbers. All Query A does is to sort the table, with the primary (first) sort being order number, and with line number being the secondary (2nd) sort. Each record also contains a field equal to the integer value of the line number, so for lines 1.000, 1.100, 1.150, 1.200, this third value would always be 1; similarly, for lines 2.000, 2.100, and 2.200, the third value would be 2.
Query B uses query A as its source of input, and performs grouping (using the "Group By" function) so that query B's output displays only one record for each unique combination of customer order number and INTEGER line number; the part number displayed on each row of query B's output is supposed to be the part number on the first row of query A's output whose order number and integer line number correspond to each group in query B. (I am using the syntax of "First(Part Number)" in query B to report this.) This seems to be working most of the time, but not consistently; sometimes, for each unique order number / integer line number combination, Query B reports a part number from the wrong row of Query A. So, for example, order 12345, integer line 1, as reported from query B, may report the part number on line 1.300 from Query A, instead of line 1.000 from query A as intended.
Does anyone know a fix for this? I've got a team of anxious coworkers who are hoping for it.
I have a crosstab where I want year and month in the header so I have formatted a variable MTHYR to be of the form mm/yyyy.
This works well as Access only wants one variable in the column header. My problem is that it is not sorted nicely and with my data being between Aug 2013 and Jun 2014 the headers look like this.
1/2014,10/2013,11/2013,12/2013,2/2014, not a very nice order.
Is there a way I can reformat MTHYR or a completely different solution?
My current code is
Code: TRANSFORM Count(VOR_CALD_REGION.[Case Number]) AS [CountOfCase Number] SELECT VOR_CALD_REGION.[Dealer Name], Count(VOR_CALD_REGION.Case Number]) AS [Total Of Case Number] FROM VOR_CALD_REGION GROUP BY VOR_CALD_REGION.[Dealer Name] PIVOT VOR_CALD_REGION.MTHYR;
I have a field on a table that is a DateTime format and it needs to be that way. I am trying to query that table for all the dates to use in a combobox but I only wat the date not the time. The problem is when I format the field to just date I can't sort it anymore as a date and I can't group them. I still get multiple lines in my combobox because there multiple times for the same day. what is the proper way in a query to extract just the date from a field and still sort it as date ie 1/12/2015 is greater than 12/15/2014?
Hi all. I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number. I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.
Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..
I'd apreciate some help with this :) Thanks
Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)
i intially, I wish to have attendance stats for 3 groups of people over 2 days in a report
i have the two queries for the raw data, but wish to combine into a report that will ultimately become a "dashboard" report for half a dozen queries., and can only get one or the other to work do i need to combine the queries into a summary query of sorts?
I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.
What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.
I have a table that contains a Zip Code field. Some of the Zip Codes are missing the leading 0. Zip Codes are a 5 digit field and these codes only have four. how do I get Access to recognize the number of characters and then add the leading 0 to only the ones that have 4 numbers?
Has anyone had any dealings with a way to calculate distance between 2 zipcodes? Of course all the zips wouls be in the current DB.... Then need to query those within a certian distance of another zipcode. I have a feeling this will need to be linked to an external source or app........ BUT... Anybody have any experience with anything similiar?
I would like to print a bar code on my invoices and then have my delivery drivers run the invoice under a bar code reader when they return from delivering. I know how to print the bar code I am nit sure how to go about interfacing a bar code reader in access 2000. Any thoughts?
