MSGROUPS.NET | Post Question | Search | About | Groups | Contact | Register | Login



microsoft public excel misc (5725)

microsoft public outlook general (4851)

microsoft public excel programming (4223)

microsoft public word docmanagement (3537)

microsoft public excel worksheet functions (3208)

microsoft public access (3168)

microsoft public windowsxp general (2045)

microsoft public windows vista mail (1629)

microsoft public office misc (1576)

microsoft public windows vista general (1511)

microsoft public windows live mail desktop (1366)

microsoft public access queries (1331)

microsoft public powerpoint (1277)

microsoft public access formscoding (1213)

microsoft public mac office entourage (1204)

microsoft public dotnet languages csharp (1183)

microsoft public access forms (1167)

microsoft public internetexplorer general (1133)

microsoft public access modulesdaovba (1076)

microsoft public windows server sbs (1064)

microsoft public greatplains (1055)

microsoft public mac office word (1026)

microsoft public outlook calendaring (965)

microsoft public excel (936)

microsoft public sqlserver programming (906)

microsoft public project server (884)

microsoft public windowsupdate (825)

microsoft public windowsxp help and support (801)

microsoft public windowsmedia player (785)

microsoft public project (782)

microsoft public access reports (766)

microsoft public mac office excel (765)

microsoft public exchange admin (754)

microsoft public dotnet framework aspnet (743)

microsoft public sqlserver server (703)

microsoft public outlook (701)

microsoft public outlook contacts (681)

microsoft public dataprotectionmanager (665)

microsoft public word vba general (641)

microsoft public windows server general (632)

microsoft public windows server active directory (587)

microsoft public outlook installation (585)

microsoft public word newusers (563)

microsoft public publisher (518)

microsoft public excel newusers (509)

microsoft public excel charting (503)

microsoft public word pagelayout (498)

microsoft public dotnet languages vb (469)

microsoft public vb general discussion (460)

microsoft public development device drivers (446)

microsoft public word application errors (435)

microsoft public office setup (430)

microsoft public sharepoint general (427)

microsoft public access gettingstarted (411)

microsoft public windowsce platbuilder (410)

microsoft public windows powershell (407)

microsoft public onenote (396)

microsoft public frontpage client (393)

microsoft public sqlserver reportingsvcs (388)

microsoft public outlookexpress general (369)

microsoft public access tablesdbdesign (353)

microsoft public pos (353)

microsoft public vc mfc (347)

microsoft public outlook program vba (341)

microsoft public windows live messenger (325)

microsoft public money (310)

microsoft public windowsxp hardware (300)

microsoft public windows 64bit general (296)

microsoft public scripting vbscript (290)

microsoft public windowsxp basics (240)

microsoft public windows mediacenter (230)

microsoft public dotnet general (226)

microsoft public mac office (198)

microsoft public nntp test (195)

microsoft public windows inetexplorer ie6 outlookexpress (192)

microsoft public windows vista music pictures video (154)

microsoft public win98 gen discussion (154)

microsoft public sharepoint setup and administration (142)

microsoft public windows vista hardware devices (130)

microsoft public dotnet framework (105)

microsoft public windows vista performance maintenance (97)

microsoft public visio (89)

microsoft public access 3rdpartyusrgrp (73)

microsoft public dotnet xml (59)

microsoft public access conversion (56)

microsoft public pt windowsxp (36)

microsoft public windows live photogallery (32)

microsoft public access developers toolkitode (32)

microsoft public dotnet faqs (15)

microsoft public dotnet framework aspnet datagridcontrol (10)


microsoft.public.access.queries Post New
Items(1330) /14 Next >> Last >|
Subject Posted Replies From
Sequential Grouping

Hey all, I have a column in a table that goes something like this: 1 2 3 5 6 7 8 And I want to group the sequential numbers together like this: 1 - 3 : Group 1 5 : Group 2 6 - 8 : Group 3 Is there any reasonable way this can be done? Thank you in advance, Ryan Leckey -- Message posted via http://www.accessmonster.com

3/11/2010 10:34:13 PM 3 "blobmiester via AccessMonster.com" <u58...@uwe>



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

3/11/2010 9:24:01 PM 1 =?Utf-8?B?Q2hhcmxpZQ==?= <Char...@discussions.microsoft.com>
select queries that calculate the Median base on group by

I have 2 questions. 1)Is it possible to create additional aggregate functions in MS Access 2007? If yes than this leads to: 2) I would like my select quries to calculate the Median and mode (along with the mean) of each group by row. I can export the entire table to Excel and do it this way but it would be easier to have Access handle it so i can just export the summary table.

3/11/2010 5:21:02 PM 1 =?Utf-8?B?WmIgS29ybmVja2k=?= <Zb Korne...@discussions.microsoft.com>
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

3/11/2010 5:06:01 PM 2 =?Utf-8?B?Y2lubmll?= <cin...@discussions.microsoft.com>
Edit and save query criteria

I have 2 Select queries where the results criteria changes on a yearly basis for 2 fields. There are 6 tables referenced in the queries. Only 2 tables referenced with 1 field each where the criteria would be changed. How could a user edit and save these changes? These are very Novice users of a database who would not have access to the open database. This was a homegrown database, but now trying to make it useable for others who have little to no experience. Thanks for any and all suggestions. -- DN

3/11/2010 4:54:03 PM 4 =?Utf-8?B?RE51ZGluZw==?= <DNud...@discussions.microsoft.com>
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

3/11/2010 4:32:02 PM 2 =?Utf-8?B?cmljaGI=?= <ri...@discussions.microsoft.com>
Iif Statement Problem

I am trying to run the below query to simply find data in a column that is a certain criteria, then taking data in another column and multiplying it and it is not returning the correct amounts based on the below. Can someone help me to write this correctly. Thank you. Adjusted DR Payout: IIf([Rep % of Average]<80,[REP DR PAYOUT]*0.8,IIf([Rep % of Average]<90.01 And [Rep % of Average]>79.99,[REP DR PAYOUT]*0.9,IIf([Rep % of Average]<105.01 And [Rep % of Average]>90.01,[REP DR PAYOUT]*1,IIf([Rep % of Average]<115.01 And [Rep % of Average]>105.01,[REP DR PAYOUT]*1.1,IIf([Rep %

3/11/2010 4:12:01 PM 2 =?Utf-8?B?Y3NhbmRicm9vaw==?= <csandbr...@discussions.microsoft.com>
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.

3/11/2010 4:01:01 PM 1 =?Utf-8?B?QW50aG9ueQ==?= <Anth...@discussions.microsoft.com>
Add a space between two values

I have a query and I have to add a space between two values, see my exp below: Station Name & Item: [DBA_DEVICES]![deviceName] & [DBA_ITEMS]![itemId] This currently displays it as SS-HVICU04521 and I need it to display as SS-HVICU 04521 with a space in the middle. Thanks for everyones help in advance.

3/11/2010 3:57:01 PM 2 =?Utf-8?B?QW50aG9ueQ==?= <Anth...@discussions.microsoft.com>
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

3/11/2010 2:54:32 PM 2 Chris <que...@gmail.com>
Random Selection of Records

I have a table named Table1 that has 200,000 records. Is there a way to randomly select 40,000 records from this table ? Thank you in advance.

3/11/2010 2:02:10 PM 3 =?Utf-8?B?Y2FybA==?= <c...@discussions.microsoft.com>
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

3/11/2010 2:00:01 PM 1 =?Utf-8?B?Y2FybA==?= <c...@discussions.microsoft.com>
error

i have a module that loops thru number of lines, parses them and updates or inserts into tables. for k = 0 to Ubound(Lines) if .. currentdb.excute "Update ..." elseif .. currentdb.excute "insert ..." next k if i replace the Ubound(Lines) with 1 or 2, after the for loop is finished i get the "Object Invalid or no longer set" error. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

3/11/2010 9:05:27 AM 1 "igg via AccessMonster.com" <u53...@uwe>
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

3/11/2010 2:12:01 AM 2 =?Utf-8?B?UGVnZ3kgQmFsbA==?= <PeggyB...@discussions.microsoft.com>
DISTINCT or GROUP BY?

Using Access 2007. My table is as follows tblIndividual IndID (autonumber, primary key) OLD_FamID (not unique) LastName (not unique) OLD_MembID (unique) I am trying to set up a query to show me the first instance of each OLD_FamID. Data: OLD_FamID LastName OLD_MembID 1 Jones 1 1 Jones 2 1 Jones 3 2 Smith 4 2 Smith 5 3 Rogers 6 3 Rogers 7 Query would show 1 Jones 1 2 Smith 4 3 Rogers 6 How do I do this? I've been playing around a bit

3/10/2010 10:51:40 PM 4 "Kathy R." <geneseo...@frontiernet.net>
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

3/10/2010 9:51:12 PM 5 Jonas <sun...@yahoo.com>
Date serial for 2 months prior

Hello, I am using the date serial function in a query to pull data that is reported 2 months after the fact. I can get the start date to work DateSerial(Year(Date()),Month(Date())-2,1). But I cannot get the end date to come up correctly. Can someone please help me on this one. Thanks much.

3/10/2010 9:05:01 PM 2 =?Utf-8?B?TGlzYUs=?= <Li...@discussions.microsoft.com>
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

3/10/2010 8:10:09 PM 3 "bassstuf via AccessMonster.com" <u47...@uwe>
Data Not Displayed in Query

I have a ODBC query built to pull data from a table in Access and linked to another database table. The query seems to work fine to compare data from the two tables, however there are a few blank fields displayed in the query. I cannot figure out why these fields are blank as there is data in the second table. If I try to enter data it dings and wont let me. It is obviously there, but it not being displayed in the query. Any ideas on a fix would be greatlu appreciated.

3/10/2010 7:07:01 PM 2 =?Utf-8?B?Tm92aWNlMjAwMA==?= <Novice2...@discussions.microsoft.com>
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?

3/10/2010 7:01:01 PM 3 =?Utf-8?B?Sm9obg==?= <J...@discussions.microsoft.com>
How to populate a column in a row based on a value in another colu

I have several tables and I’m having a problem with my last one. I have two columns in this table, ‘License’, which are populated using combo boxes, and these combo boxes are populated from using queries on other tables: I have an ‘Entitlement’ table with has and Entitlement ID (different to it’s primary Key ID). For each entitlement there can be one or more templates and so I have a ‘Template’ table with a ‘Template ID’. As it happens a template can also be in one or more entitlements so there is also an ‘Entitlement-Template’ table to manage the many to many link. My lic

3/10/2010 6:16:11 PM 3 =?Utf-8?B?UGhhdEFkbw==?= <Phat...@discussions.microsoft.com>
Column for line numbers...

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

3/10/2010 5:34:02 PM 5 =?Utf-8?B?MTE2?= <dweek...@stdgear.com>
Hours for month as value in query

Hi Everyone, I don’t know if this is a simple or complex request but it’s definitely complex for me. I want to have a field in my query that does the following: If Month has 5 Fridays, Then value=200 If Month has 4 Fridays, Then value =160 I would assume that I’d need a parameter to ask for the month when I open the query. I’m going to use this field in a calculation with another field. Can anyone help me with this or point me in the right direction or am I assuming too much here as far as the capabilities of Access? Any suggestions will be greatly appreciated. Kay

3/10/2010 4:29:01 PM 7 =?Utf-8?B?QWNjZXNzS2F5?= <Access...@discussions.microsoft.com>
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

3/10/2010 12:14:48 PM 8 "vsm" <u58...@uwe>
Is it possible and how can it be done?

I have a table looking like this: Id TaskLeader PreviousId .... 4 Hans 2 5 Kari 7 6 John 5 7 Jim 4 8 Kari 6 I'm wondering if it's possible to make a query that give me this result below (and of course if it's possible; how) ?: Id Leader Previous Leader 8 Kari John 6 John Kari 5 Kari Jim 7 Jim

3/10/2010 10:39:01 AM 5 =?Utf-8?B?SGFsbGdlaXI=?= <Hallg...@discussions.microsoft.com>
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

3/10/2010 6:44:01 AM 1 =?Utf-8?B?Q2VudHJhbFZhbGxleVBpbG90?= <CentralValleyPi...@discussions.microsoft.com>
Foreign charset showing up unexpectedly

I'm getting some very strange results from a query I'm running. I've boiled it down to a problem when using a Group By query when joining tables on two or more columns. In that case, one of the fields is showing some Asian character set (Korean, I think) in the result. In viewing the table directly, or querying with only one joined column or no Group By, the normal English values show. Here is the query that returns Korean in the Description field (note: this is a scaled-down query just to demo the problem): SELECT Tasks.Description, Max(Tasks.InitialLOE) AS MaxOfInitialLOE

3/10/2010 1:32:02 AM 5 =?Utf-8?B?R2FyeSBL?= <Ga...@discussions.microsoft.com>
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

3/10/2010 12:30:49 AM 2 "auujxa2 via AccessMonster.com" <u37...@uwe>
Query on two tables with matching null fields

Suppose I have two tables: Table 1: ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 Table2 ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 The following SQL: SELECT Table1.ID, Table2.ID FROM Table1 INNER JOIN Table2 ON (Table1.c = Table2.c) AND (Table1.b = Table2.b) AND (Table1.a = Table2.a); Returns: Table1.ID Table2.ID 4 4 I do want these tables connected by inner joins not left or right (in other words I want only those records where all t

3/9/2010 10:39:48 PM 4 tryit <tryit....@gmail.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

3/9/2010 9:55:53 PM 2 "mattc66 via AccessMonster.com" <u16...@uwe>
How do you sum on top of a group by

How do you do a sum on top of a group by or how do you combine an if statement with a group by? My code looks like this: SELECT t_Starts_AppFlow.[Organization Level 1 : R], t_Starts_AppFlow.[Location Level 3 : R], t_Starts_AppFlow.[EEO/AAP], Count(t_Starts_AppFlow.[Step Name : CSW]) AS Hire, Sum(t_Starts_AppFlow!Female) AS [Hire Female], Sum(t_Starts_AppFlow!POC) AS [Hire POC], Sum(t_Starts_AppFlow!Manager) AS [Hire Manager], Sum(t_Starts_AppFlow![Non-Manager]) AS [Hire Non-Manager], Sum(t_Starts_AppFlow!Engineer) AS [Hire Engineer], Sum(t_Starts_AppFlow![Non-Engineer]) AS

3/9/2010 9:28:01 PM 1 =?Utf-8?B?d29ya3dlZWs=?= <workw...@discussions.microsoft.com>
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

3/9/2010 9:24:05 PM 3 =?Utf-8?B?Sm9obg==?= <J...@discussions.microsoft.com>
2010 DATE CRITERIA

I have a querie and I can get any date criteria report except for the year 2010. Any suggestions? Thanks

3/9/2010 8:42:01 PM 2 =?Utf-8?B?U3V6?= <...@discussions.microsoft.com>
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

3/9/2010 7:36:06 PM 2 =?Utf-8?B?VERD?= <...@discussions.microsoft.com>
Getting rid of a Having clause

Hello I appologize ahead of time... this is going to be nasty... SELECT dbo_CUSTOMER_ORDER.CUSTOMER_ID AS CustID, dbo_CUSTOMER.NAME, dbo_CUSTOMER_ORDER.ID, dbo_CUSTOMER.COUNTRY, dbo_CUSTOMER_ORDER.CUSTOMER_PO_REF AS CustPO, dbo_CUSTOMER_ORDER.ORDER_DATE AS CODate, dbo_CUSTOMER_ORDER.SELL_RATE, dbo_CUSTOMER_ORDER.CURRENCY_ID, IIf([dbo_CUSTOMER_ORDER].[USER_10] Is Null,"No Priority", IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency","Emergency", IIf([dbo_CUSTOMER_ORDER].[USER_10]="Service","Service", IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergenc

3/9/2010 4:16:09 PM 1 =?Utf-8?B?RGF3blRyZWFkZXI=?= <DawnTrea...@discussions.microsoft.com>
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?

3/9/2010 4:12:07 PM 1 =?Utf-8?B?d29ya3dlZWs=?= <workw...@discussions.microsoft.com>
Comparison Query - How to write it!

I need to write a query which will make one table’s worth of information (Labour Hours Table) look up its corresponding fields in another table (Rates of Pay) and report back the resulting “rate” per hour Table: Rates of pay Field : ID / Autokey Field : Labour ID Field :Customer ID Field : Job ID Field :Shift ID Field :Rate Table: Labour hours Field :ID / Auto Key Field :Job ID Field : Labour ID Field :Date Worked Field : Shift ID Please advise how this query would look, or point me in the right direction of how to write it, as it is driving me mad! Many thanks

3/9/2010 3:16:03 PM 6 =?Utf-8?B?U2FyZWxsYQ==?= <Sare...@discussions.microsoft.com>
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

3/9/2010 11:59:01 AM 2 =?Utf-8?B?ZWxzYW1pcm8yMzUx?= <elsamiro2...@discussions.microsoft.com>
Nz - need code -

I have a crosstab query that yields no value where I need a 0 value for further calculation. I do not write code: This is the SQL for the crosstab. TRANSFORM Sum(qLeaseEndDataAllDatesStatusJoin.Count) AS SumOfCount SELECT qLeaseEndDataAllDatesStatusJoin.[Lease Month], qLeaseEndDataAllDatesStatusJoin.[Lease Year], Sum(qLeaseEndDataAllDatesStatusJoin.Count) AS [Total Of Count] FROM qLeaseEndDataAllDatesStatusJoin GROUP BY qLeaseEndDataAllDatesStatusJoin.[Lease Month], qLeaseEndDataAllDatesStatusJoin.[Lease Year] PIVOT qLeaseEndDataAllDatesStatusJoin.Status; I'm pretty su

3/9/2010 1:33:01 AM 2 =?Utf-8?B?QmliaQ==?= <B...@discussions.microsoft.com>
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

3/9/2010 12:10:43 AM 7 "kc-mass" <connearney_AT_comcast_DOT_net>
My Access query shows blank in the label merge

When I try to merge the Access query I want into label format, the query shows blank. I can merge the database table with no problem but not my query. What am I doing wrong?

3/8/2010 10:23:02 PM 1 =?Utf-8?B?Q2x1YiBTZWNyZXRhcnk=?= <Club Secret...@discussions.microsoft.com>
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

3/8/2010 10:16:01 PM 3 =?Utf-8?B?dGlnaGU=?= <ti...@discussions.microsoft.com>
I need to always Round UP if greater than zero at all. HOW?

I am in office 2007 Access DB - I have quantities that are greater than Zero but the Round function will round down to 0, if the number is not over .5 - how do I get it to ALWAYS round UP to the next nearest whole number even if it's .00001?

3/8/2010 7:44:01 PM 8 =?Utf-8?B?UlA=?= <...@discussions.microsoft.com>
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.

3/8/2010 5:31:01 PM 3 =?Utf-8?B?Sm9zaHVh?= <Jos...@discussions.microsoft.com>
DateAdd compile error

Using this formula to subtract 5 hours from a date/time value in tblTIMEZONE. DateAdd("m",-5,[tblTIMEZONE]![DST_Begin]) I'm getting a compile error. The DST_Begin value is stored as a date/time value. What am I doing wrong?

3/8/2010 5:00:01 PM 1 =?Utf-8?B?S2lyayBQLg==?= <Ki...@discussions.microsoft.com>
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

3/8/2010 4:26:01 PM 1 =?Utf-8?B?dG1lY3VyaW8=?= <tmecu...@discussions.microsoft.com>
Group like numbers

Good Afternoon, I have a query set up and I would like to do the following: Group numbers 100 thru 199 in group 100 200 thru 299 in group 200 300 thru 399 in group 300 I did end up building a table which has the policy number ie: 101, 102 and so forth and the corresponding category next to it. Can someone help me with the best way to do this? Many thanks in advance!

3/8/2010 4:23:01 PM 4 =?Utf-8?B?aGlzcGVhY2hlcw==?= <hispeac...@discussions.microsoft.com>
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

3/8/2010 3:53:01 PM 9 =?Utf-8?B?QWNjZXNzS2F5?= <Access...@discussions.microsoft.com>
Subquery with subraction - challenging

Sorry I repost my question because I think my previous post got burried. I have to subtract a value from one table and calculate the result in another table. Here are tables and desired values: Table Demand Item Qty Date aaa 10 03-mar-2010 aaa 7 05-mar-2010 aaa 5 12-mar-2010 ccc 8 06-mar-2010 ccc 12 09-mar-2010 Table Stock Item Qty aaa 12 bbb 9 ccc 6 the result should be table Net_Demand Item Qty Date (how to get column Qty and left from Stock table values) aaa 0

3/8/2010 2:22:01 PM 5 =?Utf-8?B?Y2xhbGM=?= <cl...@discussions.microsoft.com>
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.

3/8/2010 12:25:33 PM 1 "auujxa2 via AccessMonster.com" <u37...@uwe>
Pass Through Query - not returning rows

I've written some SQL, which when run on SQL Server 2005 returns rows. Plugged this SQL into an Access 2003 pass through query, but when run it reports an error saying pass through query with return records set to true did not return any records. I've got other pass through queries in this Access database that work fine. The SQL in this query is more complicated and creates a temp table - could this be causing the problem?

3/8/2010 9:38:01 AM 1 =?Utf-8?B?R2FyeSBXYXRzb24=?= <GaryWat...@discussions.microsoft.com>
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

3/8/2010 6:58:02 AM 3 =?Utf-8?B?amFtY2NhcmxleQ==?= <jamccar...@discussions.microsoft.com>
efficient method of finding closest date by specify input value

can it be specify the input value to the formate with YY-YY, that means let the use to key in 09-10 from the query. and the 09-10 means the date range (01/04/2009 to 31/03/2010) <staff> peter 01/08/2009 SAM 02/03/2009 IAN 03/01/2010 ==================> key in: 09-10 then, the output will shown below: output: peter 01/08/2009 IAN 03/01/2010

3/8/2010 6:28:01 AM 3 =?Utf-8?B?eG1p?= <...@discussions.microsoft.com>
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 ?

3/7/2010 9:15:01 PM 6 =?Utf-8?B?V2lsbHlOUEc=?= <Willy...@discussions.microsoft.com>
Runtime Error 3079

I trying to create a query that will that show me all the records in the tbl_OperatorLogJobData that that have a value greater than 2000 in the link table field tbl_JobGrandTotals.TotalLogEnv and have a record count of one in the link foreign key field tbl_PeriodicCheck.OpLogJobDataID. I receive runtime error 3079: The specified field :OpLogJobDataID" could refer to more than one table listed in the FROM clause of your SQL statement. 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

3/7/2010 8:02:50 PM 2 Little Penny <LittlePenn...@yahoo.com>
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

3/7/2010 5:54:01 PM 0 =?Utf-8?B?QmliaQ==?= <B...@discussions.microsoft.com>
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.

3/7/2010 2:59:01 PM 0 =?Utf-8?B?RWl0YW4=?= <Ei...@discussions.microsoft.com>
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.

3/7/2010 2:56:01 PM 1 =?Utf-8?B?RWl0YW4=?= <Ei...@discussions.microsoft.com>
Calculating the days in a month

I have a Control which is a Date field named 'NEWTOPD'. Based on the date entered in the field it calculates the days offor the whole month making amends for Leap-year as well. If I want only the number of days in that month starting from the date entered in 'NEWTOPD' - to be entered in a calculated text field (which is a number field) named 'Text361' how should I modify the following code? Text361 stores the data in (PDDAYS). 'UPDATE (PDDAYS) BASED ON NEWTOPD DATE Dim iMon As Integer Dim sDate As String sDate = (NEWTOPD)

3/7/2010 2:50:01 AM 3 =?Utf-8?B?TGVv?= <...@discussions.microsoft.com>
how to run onhand value report

I get the message enter parameter when entering the zoom feature

3/7/2010 1:36:01 AM 1 =?Utf-8?B?anVuZWJ1Z2c=?= <juneb...@discussions.microsoft.com>
Help !

Novice to Access, so apologies for daft question I need to clean up the record. address quoted with house number ie 25 Connaught Street 253 Ellis Road 154/A Garner Road 1 Field Avenue I want to seperate house number and street name. Query number 2, I have phone number but have "-" in it , ie 020-8428-428 wish to remove "-" and read number as 0208 428428 as etc. please help !!!! Thanks

3/6/2010 11:52:03 PM 8 "Yaar75" <u58...@uwe>
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

3/6/2010 11:33:01 PM 1 =?Utf-8?B?SGlyc3V0dXM=?= <Hirsu...@discussions.microsoft.com>
two criteria in DLookup

No error when compiling but when open form, got 'type mismatch'. Here is the code: Private Sub Form_Load() If DLookup("[userid]", "users", "Trim([userid]) = '" & Left(fOSUserName(), 8) & "' AND [EmailAll] = " & -1) Then cmdMassEmail.Enabled = True ElseIf DLookup("[userid]", "users", "Trim([userid]) = '" & Left(fOSUserName(), 8) & "' AND [Grade] = " & -1) Then cmdGrade.Enabled = True End If End Sub Both [EmailAll] and [Grade] are logical fields. I got fOSUserName from http://www.mvps.org/access/api/api0008.htm

3/6/2010 10:53:14 PM 5 "Song Su" <son...@live.com>
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

3/6/2010 12:13:00 PM 3 Bart <...@telenet.be>
QUERY

any query to get from tableA to tableB?. tableA: author titlea titleb vol1 sold count date share comment a b c 10 5 8 datea 10 a b c 10 15 8 datea 12 5+15=20 (10+12)/2=11 TableB: titlea vol1 sum(sold) count date avg(share) comment a 10 20 8 datea 11 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

3/6/2010 8:35:50 AM 2 "igg via AccessMonster.com" <u53...@uwe>
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.

3/5/2010 11:10:47 PM 2 =?Utf-8?B?Q2V0dGluYQ==?= <Cett...@discussions.microsoft.com>
Why does MSAccess cut query after 2886000 records

We have a large database (Access 2007 on XP) with a data table containing over 10 million records. We have a series of queries and observed that one query failed to return all records - It stopped after returning exactly 2,886,000 records. Rerunning the query produced results from all 10 million records. We made a copy of the database and issued the same set of queries. Sometimes the set of queries produces the desired results from all data records, while other times one (not always the same query) will truncate after processing exactly 2886000 records. Why does this

3/5/2010 7:35:01 PM 1 =?Utf-8?B?UGhpbEs=?= <PhilK @discussions.microsoft.com>
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

3/5/2010 6:59:02 PM 4 =?Utf-8?B?R3VpdGFyemFubg==?= <Guitarz...@discussions.microsoft.com>
No Records Found ERROR---HELP!!!

Never used to get this error msg till now. I suck t VBA any help PLEASE!!! Public Function ArchiveResults() Dim historical As DAO.Recordset Set historical = CurrentDb.OpenRecordset("Tbl_HistoricalResults", dbOpenDynaset) Dim results As DAO.Recordset Set results = CurrentDb.OpenRecordset("Tbl_Results", dbOpenDynaset) historical.AddNew historical("Run_Time").Value = results("Run_Time").Value historical("CUSIP").Value = results("CUSIP").Value historical("Group_Deal_Collateral").Value = results("Group_Deal_Collateral").Value historical("UPSIDE_FACTOR").Value = results("UPSI

3/5/2010 6:02:01 PM 4 =?Utf-8?B?a2hhbm5lcg==?= <khan...@discussions.microsoft.com>
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)

3/5/2010 5:37:01 PM 1 =?Utf-8?B?a2hhbm5lcg==?= <khan...@discussions.microsoft.com>
Queries and Reports in Office 2007

For the life of me, I can't find where to open any of my saved queries or reports in Office 2007. Can anyone help?

3/5/2010 3:10:01 PM 1 =?Utf-8?B?TWVzdW5rZWw=?= <Mesun...@discussions.microsoft.com>
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

3/5/2010 3:06:01 PM 2 =?Utf-8?B?dmVuYWpvZQ==?= <vena...@discussions.microsoft.com>
query to trim name field?

Hello, There is a name field (last, first) in my query that I would like to create a new field that only return the last name. What is the proper function to use? Thanks Example: Johnson, sam Return: Johnson (without comma)

3/5/2010 2:59:01 PM 5 =?Utf-8?B?Q2Ft?= <...@discussions.microsoft.com>
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

3/5/2010 12:19:01 PM 1 =?Utf-8?B?TmVpbA==?= <N...@discussions.microsoft.com>
Julian Date conversion

Hi and TIA! I posted this to another grroup but then saw this one and it looks more suited to my question. I've researched and tried many many conversion functions but none give me the results. One Example Public Function JDatetoDate(MyDate As Integer) As Date JDatetoDate = DateAdd("d", Right(MyDate, 3) - 1, "01/01/" & Left(year(Now()), 3) & Left(MyDate, 1)) End Function ?JDatetoDate(8044) returned 2/13/2018 expected 2/13/2008 ?JDatetoDate(0044) returned 2/13/2014 expected 2/13/2010 Any ideas. Basically want to convert a 4 digit julian to a date/time and then I

3/5/2010 4:43:33 AM 9 "Reggie" <No_Spam_chief123...@yahoo.com>
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

3/5/2010 4:23:06 AM 2 "KevinE via AccessMonster.com" <u19...@uwe>
calculated field - difficult

I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: O

3/5/2010 3:09:01 AM 11 =?Utf-8?B?Y2xhbGM=?= <cl...@discussions.microsoft.com>
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?

3/5/2010 3:02:01 AM 1 =?Utf-8?B?cmNz?= <...@discussions.microsoft.com>
days between end of one record and start of another

I have a rental database. I need to be able to calculate the number of days a property is vacant. Nothing I have tried has worked. I do not write code. My database has a table called Leases With fields: Number Property ID Lease Begins Lease Ends All help appreciated. -- TIA Bibi

3/5/2010 2:51:01 AM 12 =?Utf-8?B?QmliaQ==?= <B...@discussions.microsoft.com>
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

3/5/2010 1:27:07 AM 4 Little Penny <LittlePenn...@yahoo.com>
Querying dates between two tables

I have a query that has multiple columns with dates. I need to be able to query one column against another column. Is that possile? Example: Column A has an admission date to a hospitalization Column B has an authorization date. I need for my query to only pull hospitalizations (column A) that occurred after authorizations were issued (column B) Is it possible to tell one column to look at and query against another column?

3/4/2010 11:19:01 PM 5 =?Utf-8?B?RnJlZW1hbiBnaXJs?= <Freeman g...@discussions.microsoft.com>
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

3/4/2010 9:50:01 PM 2 =?Utf-8?B?VHlybw==?= <T...@discussions.microsoft.com>
Running Balance in Access 2007

Hello I have the following query with 3 columns: SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposit FROM tblRegister; How can I have a running balance? Both columns, Payment and Deposit are positives numbers and each raw will always have either or (never both), Payments will be subtracted from the running balance. This is what I would like to have: TransID Deposit Payment Balance 1 1,000 1,000 2 600 400 3 10 410 4

3/4/2010 9:23:02 PM 8 =?Utf-8?B?U2lsdmlv?= <Sil...@discussions.microsoft.com>
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

3/4/2010 8:15:01 PM 3 =?Utf-8?B?TG9nbw==?= <L...@discussions.microsoft.com>
Concatenating with and/or without a comma

My parameter in Criteria in a query is the following: Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " & Trim([FirstName])))) I am intending it to give me: Surname, FirstName but if there is no Surname, then: FirstName (without the space and the comma) Can somebody please correct the above parameter so that it will give me what I am wanting? Thank you for any help.

3/4/2010 7:21:02 PM 10 =?Utf-8?B?T3ds?= <...@discussions.microsoft.com>
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!!

3/4/2010 6:35:01 PM 8 =?Utf-8?B?dHNxdWFyZWQxNTE4?= <tsquared1...@discussions.microsoft.com>
how field name output same as input

Thanks for any help. I have a query from a couple tables, and one of the field names in the table is 'IGP' and I have a field in the query that was IGP and now I changed to IGP: iif(blah IGP blah) which gives me a circular error. The table is used by a bunch of other things, so I don't want to change the field name there, and the field name for the output is used by a bunch of things, so nor do I want to change the name there. I am using Access 2007. I thought about changing the output field to IGP_, and I could change the embedded queries in Excel that use it, but is there a

3/4/2010 5:23:01 PM 2 =?Utf-8?B?SWFuIEVsbGlvdHQ=?= <IanElli...@discussions.microsoft.com>
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

3/4/2010 4:19:02 PM 3 =?Utf-8?B?TGFycnlQ?= <Lar...@discussions.microsoft.com>
Query of a subform

I'm looking to create a query that will pull subform data into it. The subform (four of them) have data that gets manually entered. All the boxes in the subform have dropdown boxes except one. This box is used to enter amounts. Will it be possible to pull this data? I can't seem to find it when I use the Query Wizard or Query Design. Thanks for any help.

3/4/2010 4:00:01 PM 1 =?Utf-8?B?R2Rlc3Jvc2llcnM=?= <Gdesrosi...@discussions.microsoft.com>
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

3/4/2010 3:33:01 PM 1 =?Utf-8?B?Ui5HYXJjaWE=?= <RGar...@discussions.microsoft.com>
Find and replace

I'm trying to scan a field in one of my tables and find a specific character and remove it. However, the character is a " so I'm having difficulty. The field I speak of of contains the sizes of our material so the values look like this: 1/4" 1/2" 1/3" and so on. How can I find all of the " in my feild and remove them? I don't want to replace them I just want to remove them. Thanks, Chris Savedge

3/4/2010 3:07:07 PM 3 "Chris" <chr...@legendvalve.com>
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

3/4/2010 3:07:01 PM 5 =?Utf-8?B?ZGVlZHM=?= <de...@discussions.microsoft.com>
Subform-Query Problems

I have read thru many other threads to try and find a solution to my problem but to no avail. My issue: I have two tables that I am trying to link so that my 2d table data can be used as a subform on the main form for which all the data on the 1st table is stored. What I am trying to do with the subform is to have a datasheet that shows the date of visit..type of 8823 issued..etc but to correspond to the PROJECT for which the visit is for in the Main Form. I get the message below.. I have tried to change my query and I have no idea what I am doing wrong. I have Project Name as my Primar

3/4/2010 2:47:59 PM 6 "Konchetta via AccessMonster.com" <u22...@uwe>
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

3/4/2010 2:42:11 PM 3 =?Utf-8?B?Z2F0b3I=?= <ga...@discussions.microsoft.com>
Totals query and Memo field

I want to query a table and only display the record with the latest date. When I add the Totals row to the query grid, the memo fields cut off at 255 characters. Is there any way around this? The fields display everything in a regular query but I only want the latest date. Thanks, Jim

3/4/2010 2:16:13 PM 3 =?Utf-8?B?Smlt?= <...@discussions.microsoft.com>
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

3/4/2010 11:20:01 AM 1 =?Utf-8?B?RmFyYXogQWhtZWQgUXVyZXNoaQ==?= <FarazAhmedQure...@discussions.microsoft.com>
Hyperlink Part

Hello, I have a query that builds a hyperlink from various other fields which work fine. This query is then used on a form and for the hyperlink field I use the OnClick Event and FollowHyperlink which also works fine. However I want to display some text such as "Click here" instead of the hyperlink, does anyone know how to do this? Here is the field that builds up the hyperink: Hyperlink: HyperlinkPart("http://MyAddress" & [FieldName]) I was expecting it to be something like: Hyperlink: HyperlinkPart("http://MyAddress" & [FieldName], "Click here") but that returns an

3/4/2010 9:50:01 AM 1 =?Utf-8?B?TWFydGlu?= <Mar...@discussions.microsoft.com>
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

3/4/2010 7:03:01 AM 3 =?Utf-8?B?UGVsbGlzc2llcg==?= <Pelliss...@discussions.microsoft.com>
How to Handle Nulls in SQL?

I have a query that returns all records, except for nulls. I’m experimenting with this: SELECT * FROM Trades WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And [Forms]![QueryForm]![cboTo]) AND ((Trades.Symbol) Like 'IBM') AND ((Trades.Cust) Like '*') OR Nz([Trades.Cust], 0)); 9 records are returned, but it should be 11; there are two nulls the field named ‘Cust’ in the table named ‘Trades’. So, two records in the rows with IBM that are skipped. Somehow SQL is not evaluating these two records properly. What am I doing wrong? Thanks for the help!! Ryan--

3/4/2010 5:41:01 AM 8 =?Utf-8?B?cnlndXk3Mjcy?= <ryguy7...@discussions.microsoft.com>
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

3/4/2010 1:28:05 AM 1 =?Utf-8?B?TW96ZWU=?= <Mo...@discussions.microsoft.com>

Pages: 2 3 4 5 6 7 8 9 10 11 12 13 14











Newest Articles

Multi-Column Report
5 min. 54 sec. ago

addressing mail
7 min. 28 sec. ago

Drop down list in Microsoft Word 2007
11 min. 28 sec. ago

How do I find out why? Error 3251
14 min. 55 sec. ago

Path.GetInvalidPathChars "Visual Basic 6.0 Classic" equivalent
17 min. 29 sec. ago