Getting total sales by customer

Hi, I need to find the total sales for a customer, I have tried the Group by function but it just groups the company name in ascending order. My fields are: Client_Name, Rep, InvoicePeriod, InvoiceYear, Total I will also need to group by Invoice Period in relevant year. Any help would be appreciated, Thanks in advance. Charlotte

SQL question - Cross Tab query

hello gurus I have a Cross Tab query that works fine. The SQL for this query ends with... PIVOT tblData.Region I've noticed that the column headings in my query result always show the Regions in alpha order (eg: East, MidWest, North1, North2, SouthEast). I've also noticed that I can 'force' the column order by using something like... PIVOT tblData.Region IN(North1, North2, MidWest, SouthEast, East) But, this later method presupposes that I know all of the Regions in advance. If a new Region is added, it won't be in the 'IN' function. Here is my question - what

Update query to replace everything after comma

I'm trying to remove everything after the first comma in a column of addresses. It seems like it should be simple but I can't get it to work. I have tried: UPDATE tblCompetitor2 SET tblCompetitor2.OUTLET = Replace([OUTLET],",*","") Is it something to do with the * wildcard? Thanks

Insert a default record for all values

I have a table with station names for ex. SS-MICU, SS-MIMCU, ect. I need to have a query fun and input a value of SS-MICU 00000 for all records that have a station name. I hope this makes sense. My example is the data I pull looks like SS-MICU 04544 since that is the lowest number I'm pulling back and I have to have a value of SS-MICU 00000 as the first record and I need a way to put that in all records I have. Thanks in advance.

How to Return all Records with Blank Cells?

Might be an easy question, but how do I return all records which have empty cells for the specified FIELD Example: Name School Home Child 1. xxxx xxxx xxxx 2. xxxx xxxx xxxx 3. xxxx xxxx xxxx xxxx 4. xxxx xxxx xxxx 5. xxxx I want to return all records except #3. Because it has all of the values. I only want to see records missing a value. I tried to use. IS NULL in each Field in Design View (under OR not CRITERIA), but it keeps returning everything. Thanks in advance

Compare 2 Fields

My data looks like this: Sym Price1 Price2 A 11.3 11.3 B 8.75 8.75 C 7.8 7.8 D 6.75 6.75 E 5.75 5.75 I would like to create a new field that will compare Price1 to Price2 and return the difference between the 2 fields. Is this something that can be done in Access ? Thank you in advance

Crosstab query to include fields with Null value

Hello ~ I have created a report based on a crosstab query for student attendance. Row = Name Columns = DR, Ed, FE, ILL, RH, TEX, TRP, TUN, UNV, UNX (absent reasons. Each column show the number of dates (count) that child has been absent for that particular reason) Total = counts the total number of absent dates. This works almost great if I'm printing the reports for the entire school because there is at least 1 absence in every column. It will not print a report for a student if he/she hasn't been absent. I need to be able to filter the report by either class or studen

Subquery

I want to create a query that calculates the percentage of a total and multiplies each percentage times a single number that is stored in a table. Here is what I have so far: SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/ (SELECT Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount FROM AmortSchedule WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2 FROM AmortSchedule WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#)) GROUP BY AmortSchedule.AmtBorUniqueID; I have tried to repace the number 39,000 with a query but I just get an e

Need to Make Query Run Faster - SLQ?

I need to make my query run faster and I am not sure if any improvements to my SLQ can help. It caculates off of 2 very large Tables Table 1: Formation - 93521 Records (Query - "Form SN") Table 2: Shipped Serial Numbers - 35437 Records (Query - "Shipped") Each Table has a corisponding Query that runs that I use to display the inital data in a field that I have listed as Combo =Part Number&" "&Serial Number which I use to like the 2 Queries in another Query called Combo that link. The Combo Query takes about a minute to run, and I was wondering if there is any way to improve the

using "Last" in query desgin

I am trying to build a query that sorts data ascending order by date, so that when I select "Last" in the Total row for other fields in the query I will get the newest value (I have an account field that doesn't change but I want to retrieve the newest account description which can change from one day to the next so I want the desciption of the newest record). The problem is I don't want the date column in the query since I am trying to also trying to group and sum the data and don't want a new row of data for each date value. Any ideas?

Column for line numbers...

Can an expression in a column be used to generate line numbers? 1, 2, 3, etc. Thanks David

Count of Unique Values in Access Field

In the query given below which is done in Access SELECT [Q Test].[Lead Business], [Q Test].Region, Avg([Q Test].[Cycle time days]) AS [AvgOfCycle time days], Count([Q Test].[Contract ID]) AS [CountOfContract ID] FROM [Q Test] GROUP BY [Q Test].[Lead Business], [Q Test].Region; The field Count([Q Test].[Contract ID]) lists the count of contract ids including duplicates. What should I do to let Access Return only count of unique values. For example, there are 100 records having contract id out of which only 10 are unique, balance 90 are repetitions. While currently access ret

Date Query to create a report

I am a flight instructor with a flightschool in California. I am trying to set up a database for the school to help track when our instructors need to recieve a new medical exam, flight review and annual proficency checks. All of these checks need to be done in a certain number of Calander months. For instance an instructor does their inital proficency check on May 1, 2009. He is required to have his next flight check no later than May 31, 2010. Is there a formula that I can use to request records a certain number of months in the future as the acctual day of that month does

Naming Combo boxes

I have to create MANY forms with MANY combo boxes. The strSQL behind the scenes is going to be tedious enough, but... Is there a way to name the boxes in a quick manner, systemically? (i.e. i want the first row of 6 combos to be cbo1a, cbo1b, ===>cbo1f. Row 2 = cbo2a, cbo2b, etc.) But if I have 20+ rows, it's taking me a long time to name them. Any ideas? -- Message posted via http://www.accessmonster.com

Look up by Date issue

I have a date field that I want to filter based on a date. However the date contains the date and time. I want to allow the user to enter the date to filter the date. When we enter the date in the criteria - it comes up with no records. However there is data when not filtered. What can I do? -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

Query that will look for duplicate in two tables.

I have two tables that were originaly Excel tables and imported them into Access. Import went well. Both tables consist of columns named - Name, Department Number, Department Name, and Employee ID. I need to compare the two tables and have Access give me the NON duplicates. I ran a Find Unmatched Query using name in both tables. The results showed almost as many records as are in the original two tables. What I noticed it found in one table Smith, John and Smith,John. These are the same people but one person typed the name with the last name and comma space while the other pers

Iif statement and time format

I have a table with a time field that displays a long form time (12:00:00 AM) by default. Through a query I want to build a work shift field for grouping these records. Below is an expression, but I know the time format is wrong. What format do I use? iif(([AccTime]between 6:01 and 18:00), "Day", "Night") -- TDC

How to get query to return total using wildcard

I have a query that has 3 criterion in it as follows: Business Unit, Location, Category. These criterion are all chosen by the user on a loaded form. Currently the results then are based on what is chosen from a drop down list for each of the 3 criterion. The user also wants a total option. Meaning if Business Unit is chosen, and Location and Category are not chosen (or if a wildcard is chosen) the result returns a total based on that Business Unit. How do I write this in the query?

recursively retrieve records from 2nd table based on records from

I have two separate queries, that show the wanted results. I tried hard but i cannot "merge" them into query/subquery. I am not sure if subqueries are the right way to solve this. I keep having errors or results that aren't the ones expected. Below is the query , with simplified table/field names. SELECT tbl1.ID FROM tbl1 (SELECT ID, field2 FROM tbl2 WHERE field2 = tbl1.ID ) ORDER BY tbl1.ID DESC; Using words the query should Display table1.ID ASCENDING for each table1.ID lookup table2.ID WHERE table2.field2 = table1.ID What I am looking

Brain Freeze help with combobox and query

Hi, I have a personnel form with typical personnel data. On the form is an combo that has a data source of employeeID, LastName, First name. The first (0) column is bound to the combo. The query that feeds the form references the combo as criteria against the employeeID. =[ME].[cboEmployee] Or Is Null The onchange event of the combo asks for a requery of the form Private Sub cboEmployee_Change() Requery Debug.Print "it fired" End Sub The event fires but the form does not requery. Any help appreciated. Kevin

12 Month Running Total

All, thanks for the advice to get me this far. previously i had asked about a 12 month rolling total, below is the SQl but for Decembers the total is wrong, can anyone suggest a fix? AC2007/Xp thanks in advance. SELECT Firm_Employee_Count_time.Financial_Month, Format([Financial_Month],'yyyymm') AS FinMon, Firm_Employee_Count_time.OccurDate, [Firm_Employee_Count_time]![OccurDate]-IIf(Right([Firm_Employee_Count_time]![OccurDate],2)=12,111,99) AS OccurPrevious, Firm_Employee_Count_time.EmployeeCount, Nz([Firm_Employee_Count_Fired]![EmployeeCount],0)*-1 AS FiredNum, Nz([Fi

Stacking Date/Time from Row to Row

Hello there. I'm trying to run an update query that will look at a previous row's end date and fill in the same date in the current row's start date. I've tried using dlast to do this but have read that it doesn't see the rows in a table in any particular order. My goal is to stack dates and times to start and end one right after another. This might not be possible but I would appreciate any assistance if you can provide. Thank you in advance.

military time

The database field is a date-time field. I want to report it in military time but change the format to 0000 not 00:00

Query set-up not using cross-tab

Hi, I keep running into this same problem when I’m building my query and I’ve tried several different approaches. It could be that it is beyond my knowledge but I don’t know what direction to take to find the solution. What is the "cleanest" way to build the following query. I have a table (I simplified the example): Empl Type THours Joe Billable 60 Joe Indirect 100 Mary Billable 160 And I want my query to look like this: Empl Billable Indirect THours Billable% Indirect% T% Joe 62 100 162 39% 63% 101% Mary 160 0 160 100% 0% 100% I tried a cross-tab query but was una

runtime access

I'm doing a freelance job for a company that wants it's dozen employees to have access on their laptops. They're asking me if the runtime version would work, because it's free. What is the difference between purchasing the license and runtime access? See below... ------------------------------------------------------------------------------ ------------------------------------------------------------ Can we use the runtime version of Access on the techs laptops? It is free and would save purchasing for each laptop. The link to Microsoft’s download is below if you need to test.

Count query

How would I set up this query? All I want to do is count the number of errors on a certain cell in our machining process. I have a [Date] field, [Cell 1], [Cell 2] all the way through [Cell 7]. I want a daily number of errors and then an accumulitive number every day. I am then going to have it graph this data. Thanks Joshua McCarley

How to get a query to call the parameter form

I am trying to use a form to get query parameter dates as is detailed in the help article "Make a query ask for input" The query does not open the date range form when the query is used If the form is opened in advance and dates entered then the dates in the form are used what is missing? How is the open form macro called ?

pivot table calculations

I have not done much in pivot tables. But I have designed a pivot table form that does all I need except for the last step - creating a column to show a net amount. That is, I need to subtract……. For beginning leases, I have sums for three types – new, renewal or extension – and a total for all beginning leases. I have the sum of expiring Leases, that’s all I need. I do not care about the type of lease. I need to know a net number for each month – number of all beginning leases minus the total of all expiring leases. I can’t figure out how to get that figure in the pivot ta

mdb file

Over the years I have assembled in ACCENT a database called "Music Archive", to which the latest addition was made in January 2009. Now, when I wish to open the Database, I obtain the following message : "Unrecognised database format Music Library.mdb." When looking at the Help solutions, it says "Error 3343" and then "Compact the DB", which I did,- to no avail. Perhaps you can help me to get back into using this Database.

how to run onhand value report

I get the message enter parameter when entering the zoom feature

How do I access multiple records within a query?

I am trying to calculate the total quantity of components in a nested Bill Of Materials where the true quantity of any sub-level component is dependant on the quantity of the previous higher component. Level Item number Quantity 1 A123 1 2 A124 2 3 A125 2 3 A127 1 2 A126 1 2 A125 1 1 A128 1 2 A130 2

Criteria in append querie

I want to append only non existing ClientID fields into the destination table. Client ID is not a primairy field because it is a import file from website(can't put it in primairy key because import program refresh the table every 5 minutes and replaces all data.) So i do a append query with this criteria: =not Exists (SELECT 'KlantID' from [Klant_copy] where [Klant_copy].KlantID = [Klant].KlantID) Klant= to be copied table (KlantID fields) Klant_copy= destination table (also KlantID fields) Can someone tell me why my append querie criteria does'nt work (to copy only non e

Access 2007 - Date Serial

I want to set up a criteria for the Date Serial where I want to be able to run a report that will allow me to enter a date and then be able to tell me what is going to be coming up due as of 30 days from the date entered. I am not sure what the formula is to make this happen and anything that I look for is not helping me.

Can there be too many sub queries within a single query?

I am trying to combine the results from more than 15 queries into a single query in an attempt to save opening 15 different queries individually. The query runs successfully with the exception of the 15th, or last, query returning null cells. If I pull that sub query by itself it does return the correct data however. Has anyone experienced this in their projects? Thanks for your help, Rick

divison by zero error

this never used to show up till now. 'Compute Appraisal Value If BBG("Orig# LTV").Value < 1 Then ltv = BBG("Orig# LTV").Value Else ltv = BBG("Orig# LTV").Value / 100 loantape("OrigAppraisalValue").Value = CLng(BBG("Orig# Amt").Value) / CLng(ltv)

Using Combo Boxes as a Source for Parameter Queries

Access Gurus, I often encounter a situation where I want to run a select (parameter) query on a particular table, but instead of having the user type in their own parameter, I'd like for them to be able to choose, from a combo box that auto-completes, the actual item in the underlying table, then use this string as the basis for the parameter query. I want to limit the parameters to already-extant items in the underlying table and so avoid close-but-not-exact matching problems by free-typing the parameter. -- Joe Venable Virginia Beach, VA

Sum duplicate lines in a table

I have a table which contains a list of products soted by poduct name. Against each product there is a stock quantity, each Item may appear more than once. I want to be create a query which will give me a list of the products, but with only one entry per item, with a total quantity for each item. Is this possible? Thanks

Query most recent records only

I am not having much luck with trying to write a query to find the last (most recent dates) 5 records for ALL names listed in the 'Customers' table. SELECT tblOrders.*, tblCustomers.ID FROM tblOrders RIGHT JOIN tblCustomers ON tblOrders.Name = tblCustomers.Name ORDER BY tblCustomers.ID, tblOrders.Date; Appreciate any help, Kevin -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

Query prompt one column for multiple random values

I can write a query that prompts one field for a specific value, but would like user to enter multiple criteria Example - Lot numbers range from 1 to 140 transportation back to original consignor is provided by one individual, he is picking up lots 2, 50, 76 and 32 so I want to have these values on the load out ticket How do I allow for nonconsecutive values to be entered?

Query Records in Linked table

I have a Left Join query to find all the record in the tbl_LogJobData that do not have link records in the tbl_Check. Tables tbl_LogJobData and tbl_Check are linked by a primary and foreign (one to Many). How can I change my query to show me one the records in the tbl_LogJobData that have only one linked record in the tbl_Check table. Thanks Little Penny My Code Dim strSQL As String Dim strWhere As String Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string. DoCmd.OpenForm "frmSearchSignBy", , , , , acDialog If IsLoade

Microsoft SQL Server Management Studio / Convert Date

This isn't exactly a Microsoft Access question, but I didn't know where else to go. I am using Microsoft SQL Server Management Studio. The table and field name are “dbo.EncounterData.CreateDateTime” The results are formatted like “2010-03-02 10:36:52.527”. Would you know how I can use the Convert function to format the results as “yyyymmdd”. I know it is something like one of these: select (convert (char(8), getdate(), 112) SELECT CONVERT(VARCHAR(8),CONVERT(DATETIME),112) but I can't get either one to work. -- Tyro from Missouri

This has got to be an easy question?

Is all I want to do is combine two records from a query. I have data from a query from a lunch account and from a dinner account. All I need to do is combine the data into one account. There are probably around 10 fields of data, but they all have the same properties. Surely this is easier than I'm making it out to be? I tried a union query, but didn't get what I wanted. This is what I'm looking at- SELECT * FROM TableData WHERE AccountName=(restname); I want to combine with- SELECT * FROM TableData WHERE AccountName=(restnamelunch); I want to combine the results

Calculating Values in a Query

I have 4 fields in a query, Drawing Dim., Tolerance, Upper Limit, and Lower Limit. I am trying to calculate the Upper and Lower Limit fields by taking the Drawing Dim. +/- the Tolerance (this works fine on my form for entering the data). The problem is that the Tolerance is a combo box (dropdown), and the user chooses from values in the table. I am pretty sure this is why I am getting a Data Type Mismatch Error in my query, but I cant figure out how to get it to work. Can someone please help me? Thanks!!

Unique Count in Sigma Query

Access 2003, Windows XP Pro. SITUATION: Database has lots, which have sublots, which have items. Sublot key is a barcode number. I have a query that joins the three tables, and since a sublot can have multiple items, the sublot key can occur more than once. I want to run a query against this big query that just groups and summarizes some things, one of which is the number of sublots in a given lot. Using a straight Count fails because of the multiple occurrences of the sublot key value. Here's an attempt to show this pictorially: LOT SUBLOT ITEM A A123

Select to reference an other record or row

Hi, I have one table on Microsoft Access 2007 like this Table1 Field1 Field2 Field3 Field4 1 Data1 1 2 1 Data2 3 4 1 Data3 5 6 1 Data4 7 8 2 Data5 9 10 2 Data6 11 12 2 Data7 13 14 2 Data8 15 16 Then I need a query to get something like this Query Result Field1 Field2 Field3 Field4 1 Data1 1 2 1 Data2 1 2 1 Data3 3 4 1 Data4 5 6 2 Data5 9 10 2 Data6 9 10 2 Data7 11 12 2 Data8 13 14 note that changes is just only on field3 and field4 whit an offset (-1,0) and for each change in field1 the first record is duplicate from above and the last data for each change on

Multiple prompt criteria

I want to be able to enter 2 critieria from a prompt box when run query. Eg. [Enter Product] AND [Enter Product]...can this be done? Thanks

can't get query to group

here is the sql. can someone help me determine why the query isn't grouping by GoupNumber? The GroupName is the same for the GroupNumber. ID is unique. SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName FROM Accounts GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID HAVING (((Accounts.ID) Like "01*")); Example ID GroupNumber GroupName 010101 01 red 010102 01 red 010201 02 blue 010301 03

Append New Record Via A Form?

Have never used Append Queries and therefore lack the knowledge of the same. I have a sample table "Students" with fields like: 1. ID (AutoNumber) 2. Name (Text) 3. Nationality (Lookup with Row Source being Selection from the table "Countries") 4. Date of Birth (Date) Now I want to create a form which would not be showing the records already in the database but simply present a one page layout with the 4 controls for each of the fields and a button which upon being clicked shall add the data entered in the same to the Table "Students". Please c if any of u xperts can provide

challenging query

I have a table with a code field and a price field. I need to find records where the code field data is the same (I did a duplicate query) Now I need to make a report that shows those duplicates but only if the price field is different. I am trying to show where there are duplicate codes that have different prices -- Thank you

Comapring rows of a table to multple rows within the same table

Hi, I have the following data which I have been tasked with identifying a primary case for each customer. Unfortunately the data contains duplicate records such as Case 1 and 2. I would be extremely grateful if you could tell me how can I compare all the rows related to Customer 1 and flag a case as the primary case. CaseID, CustomerID, PrimaryStatus, SecondaryStatus, GroupA, GroupB 1,1,New, Unassigned, RU, HK 2,1,New, Unassigned, RU, HK 3,1,In Progress, Normal Review, RU, HK 4,1,New, Unassigned, IN, UK 5,2,New, Assigned, JP, 6,2,In Progress, Special Review, BR, HK 7,2,Co