Simple query??

Hello All

I have been wrestling for ages now with a query that I thought would be 
simple .. but can't get the output I want!

I have a table [practice suppliers] with fields SUPPLIER and AUTO CATEGORY 
(and others). The field SUPPLIER is not a unique (key) field, but in fact it 
almost is: ~95% of the records in the table contain unique values in this 
field.Of the remaining ~5%, about half of the records for a given SUPPLIER 
value contain the same value of AUTO CATEGORY, and the other half contain 
different values for AUTO CATEGORY.

I want a query that will return the number of different values of AUTO 
CATEGORY for each SUPPLIER. For the ~90%, and the first half of the 
remaining ~5% (as referred to above), the query should return '1' as the 
number of different values of AUTO CATEGORY ... but for the other half of 
the ~5% it should return the actual number of different AUTO CATEGORY values 
for the SUPPLIER.

My attempts (which seem to keep coming back to the one below) keep returning 
the total number of AUTO CATEGORY values for each SUPPLIER - e.g. where 
there are 8 records in the table with the same value of SUPPLIER and AUTO 
CATEGORY, my query returns '8' - whereas it should return '1'. If the same 
SUPPLIER appears 8 times with, say, 5 different AUTO CATEGORY values, the 
query should return '5'.

Hope someone can help - this is driving me nuts!

Thanks for any help.
Leslie Isaacs

My 'attempt':
SELECT [practice suppliers].SUPPLIER, Count([practice suppliers].[AUTO 
CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [practice suppliers]
GROUP BY [practice suppliers].SUPPLIER;


-- 
Get snapshot Viewer from the following link:


0
Leslie
3/19/2008 12:40:54 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
568 Views

Similar Articles

[PageSpeed] 17

Two query solution.

SELECT DISTINCT [practice suppliers].SUPPLIER
,[AUTO CATEGORY]
FROM [practice suppliers]

Use that query (Saved) as the source for the following query

SELECT SUPPLIER
, Count([AUTO CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [The Name of the Saved Query]
GROUP BY SUPPLIER;

If your field and table names consisted of only letter, number, and 
underscore characters, you could do that in one query.  One of many 
reasons to name tables and fields without spaces or other 
non-alphanumeric characters.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Leslie Isaacs wrote:
> Hello All
> 
> I have been wrestling for ages now with a query that I thought would be 
> simple .. but can't get the output I want!
> 
> I have a table [practice suppliers] with fields SUPPLIER and AUTO CATEGORY 
> (and others). The field SUPPLIER is not a unique (key) field, but in fact it 
> almost is: ~95% of the records in the table contain unique values in this 
> field.Of the remaining ~5%, about half of the records for a given SUPPLIER 
> value contain the same value of AUTO CATEGORY, and the other half contain 
> different values for AUTO CATEGORY.
> 
> I want a query that will return the number of different values of AUTO 
> CATEGORY for each SUPPLIER. For the ~90%, and the first half of the 
> remaining ~5% (as referred to above), the query should return '1' as the 
> number of different values of AUTO CATEGORY ... but for the other half of 
> the ~5% it should return the actual number of different AUTO CATEGORY values 
> for the SUPPLIER.
> 
> My attempts (which seem to keep coming back to the one below) keep returning 
> the total number of AUTO CATEGORY values for each SUPPLIER - e.g. where 
> there are 8 records in the table with the same value of SUPPLIER and AUTO 
> CATEGORY, my query returns '8' - whereas it should return '1'. If the same 
> SUPPLIER appears 8 times with, say, 5 different AUTO CATEGORY values, the 
> query should return '5'.
> 
> Hope someone can help - this is driving me nuts!
> 
> Thanks for any help.
> Leslie Isaacs
> 
> My 'attempt':
> SELECT [practice suppliers].SUPPLIER, Count([practice suppliers].[AUTO 
> CATEGORY]) AS [CountOfAUTO CATEGORY]
> FROM [practice suppliers]
> GROUP BY [practice suppliers].SUPPLIER;
> 
> 
0
John
3/19/2008 1:22:40 PM
Hello John

Thanks for your answer.
Does it have to be 2 queries then?
I can see that works, so will obviously use the 2 ... but curiosity drives 
me to wonder how it could be done in one - even with my bad field names!

Thanks again
Les


"John Spencer" <spencer@chpdm.umbc> wrote in message 
news:u9xKbRciIHA.4844@TK2MSFTNGP06.phx.gbl...
> Two query solution.
>
> SELECT DISTINCT [practice suppliers].SUPPLIER
> ,[AUTO CATEGORY]
> FROM [practice suppliers]
>
> Use that query (Saved) as the source for the following query
>
> SELECT SUPPLIER
> , Count([AUTO CATEGORY]) AS [CountOfAUTO CATEGORY]
> FROM [The Name of the Saved Query]
> GROUP BY SUPPLIER;
>
> If your field and table names consisted of only letter, number, and 
> underscore characters, you could do that in one query.  One of many 
> reasons to name tables and fields without spaces or other non-alphanumeric 
> characters.
>
> '====================================================
>  John Spencer
>  Access MVP 2002-2005, 2007-2008
>  Center for Health Program Development and Management
>  University of Maryland Baltimore County
> '====================================================
>
>
> Leslie Isaacs wrote:
>> Hello All
>>
>> I have been wrestling for ages now with a query that I thought would be 
>> simple .. but can't get the output I want!
>>
>> I have a table [practice suppliers] with fields SUPPLIER and AUTO 
>> CATEGORY (and others). The field SUPPLIER is not a unique (key) field, 
>> but in fact it almost is: ~95% of the records in the table contain unique 
>> values in this field.Of the remaining ~5%, about half of the records for 
>> a given SUPPLIER value contain the same value of AUTO CATEGORY, and the 
>> other half contain different values for AUTO CATEGORY.
>>
>> I want a query that will return the number of different values of AUTO 
>> CATEGORY for each SUPPLIER. For the ~90%, and the first half of the 
>> remaining ~5% (as referred to above), the query should return '1' as the 
>> number of different values of AUTO CATEGORY ... but for the other half of 
>> the ~5% it should return the actual number of different AUTO CATEGORY 
>> values for the SUPPLIER.
>>
>> My attempts (which seem to keep coming back to the one below) keep 
>> returning the total number of AUTO CATEGORY values for each SUPPLIER - 
>> e.g. where there are 8 records in the table with the same value of 
>> SUPPLIER and AUTO CATEGORY, my query returns '8' - whereas it should 
>> return '1'. If the same SUPPLIER appears 8 times with, say, 5 different 
>> AUTO CATEGORY values, the query should return '5'.
>>
>> Hope someone can help - this is driving me nuts!
>>
>> Thanks for any help.
>> Leslie Isaacs
>>
>> My 'attempt':
>> SELECT [practice suppliers].SUPPLIER, Count([practice suppliers].[AUTO 
>> CATEGORY]) AS [CountOfAUTO CATEGORY]
>> FROM [practice suppliers]
>> GROUP BY [practice suppliers].SUPPLIER;
>> 

0
Leslie
3/19/2008 3:21:05 PM
Access won't let you do it in one query with your names.

If the names were changed to PracticeSuppier (or Practice_Supplier) for 
the table and AutoCategory (or Auto_Category) for the field then the 
query would look like the following.

SELECT SUPPLIER
, Count(AUTOCATEGORY) AS [CountOfAUTO CATEGORY]
FROM
    (SELECT DISTINCT practiceSuppliers.SUPPLIER
    , AUTO CATEGORY
     FROM practiceSuppliers) as Tmp
GROUP BY SUPPLIER;

Which when it was saved would become

SELECT SUPPLIER
, Count(AUTOCATEGORY) AS [CountOfAUTO CATEGORY]
FROM
    [SELECT DISTINCT practiceSuppliers.SUPPLIER
    , AUTO CATEGORY
     FROM practiceSuppliers]. as Tmp
GROUP BY SUPPLIER;

Access will not allow any square brackets in the sub-query in a from 
clause.  In my opinion, it should, but I don't have a controlling 
interest in Microsoft so I doubt that I will be able to force a change.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Leslie Isaacs wrote:
> Hello John
> 
> Thanks for your answer.
> Does it have to be 2 queries then?
> I can see that works, so will obviously use the 2 ... but curiosity drives 
> me to wonder how it could be done in one - even with my bad field names!
> 
> Thanks again
> Les
> 
> 
> "John Spencer" <spencer@chpdm.umbc> wrote in message 
> news:u9xKbRciIHA.4844@TK2MSFTNGP06.phx.gbl...
>> Two query solution.
>>
>> SELECT DISTINCT [practice suppliers].SUPPLIER
>> ,[AUTO CATEGORY]
>> FROM [practice suppliers]
>>
>> Use that query (Saved) as the source for the following query
>>
>> SELECT SUPPLIER
>> , Count([AUTO CATEGORY]) AS [CountOfAUTO CATEGORY]
>> FROM [The Name of the Saved Query]
>> GROUP BY SUPPLIER;
>>
>> If your field and table names consisted of only letter, number, and 
>> underscore characters, you could do that in one query.  One of many 
>> reasons to name tables and fields without spaces or other non-alphanumeric 
>> characters.
>>
>> '====================================================
>>  John Spencer
>>  Access MVP 2002-2005, 2007-2008
>>  Center for Health Program Development and Management
>>  University of Maryland Baltimore County
>> '====================================================
>>
>>
>> Leslie Isaacs wrote:
>>> Hello All
>>>
>>> I have been wrestling for ages now with a query that I thought would be 
>>> simple .. but can't get the output I want!
>>>
>>> I have a table [practice suppliers] with fields SUPPLIER and AUTO 
>>> CATEGORY (and others). The field SUPPLIER is not a unique (key) field, 
>>> but in fact it almost is: ~95% of the records in the table contain unique 
>>> values in this field.Of the remaining ~5%, about half of the records for 
>>> a given SUPPLIER value contain the same value of AUTO CATEGORY, and the 
>>> other half contain different values for AUTO CATEGORY.
>>>
>>> I want a query that will return the number of different values of AUTO 
>>> CATEGORY for each SUPPLIER. For the ~90%, and the first half of the 
>>> remaining ~5% (as referred to above), the query should return '1' as the 
>>> number of different values of AUTO CATEGORY ... but for the other half of 
>>> the ~5% it should return the actual number of different AUTO CATEGORY 
>>> values for the SUPPLIER.
>>>
>>> My attempts (which seem to keep coming back to the one below) keep 
>>> returning the total number of AUTO CATEGORY values for each SUPPLIER - 
>>> e.g. where there are 8 records in the table with the same value of 
>>> SUPPLIER and AUTO CATEGORY, my query returns '8' - whereas it should 
>>> return '1'. If the same SUPPLIER appears 8 times with, say, 5 different 
>>> AUTO CATEGORY values, the query should return '5'.
>>>
>>> Hope someone can help - this is driving me nuts!
>>>
>>> Thanks for any help.
>>> Leslie Isaacs
>>>
>>> My 'attempt':
>>> SELECT [practice suppliers].SUPPLIER, Count([practice suppliers].[AUTO 
>>> CATEGORY]) AS [CountOfAUTO CATEGORY]
>>> FROM [practice suppliers]
>>> GROUP BY [practice suppliers].SUPPLIER;
>>>
> 
0
John
3/19/2008 5:54:52 PM
John

Many thanks for that: I'll just email Mr Gates ...!

Thanks again
Les

"John Spencer" <spencer@chpdm.umbc> wrote in message
news:Od83hpeiIHA.4396@TK2MSFTNGP04.phx.gbl...
> Access won't let you do it in one query with your names.
>
> If the names were changed to PracticeSuppier (or Practice_Supplier) for
> the table and AutoCategory (or Auto_Category) for the field then the
> query would look like the following.
>
> SELECT SUPPLIER
> , Count(AUTOCATEGORY) AS [CountOfAUTO CATEGORY]
> FROM
>     (SELECT DISTINCT practiceSuppliers.SUPPLIER
>     , AUTO CATEGORY
>      FROM practiceSuppliers) as Tmp
> GROUP BY SUPPLIER;
>
> Which when it was saved would become
>
> SELECT SUPPLIER
> , Count(AUTOCATEGORY) AS [CountOfAUTO CATEGORY]
> FROM
>     [SELECT DISTINCT practiceSuppliers.SUPPLIER
>     , AUTO CATEGORY
>      FROM practiceSuppliers]. as Tmp
> GROUP BY SUPPLIER;
>
> Access will not allow any square brackets in the sub-query in a from
> clause.  In my opinion, it should, but I don't have a controlling
> interest in Microsoft so I doubt that I will be able to force a change.
>
> '====================================================
>   John Spencer
>   Access MVP 2002-2005, 2007-2008
>   Center for Health Program Development and Management
>   University of Maryland Baltimore County
> '====================================================
>
>
> Leslie Isaacs wrote:
> > Hello John
> >
> > Thanks for your answer.
> > Does it have to be 2 queries then?
> > I can see that works, so will obviously use the 2 ... but curiosity
drives
> > me to wonder how it could be done in one - even with my bad field names!
> >
> > Thanks again
> > Les
> >
> >
> > "John Spencer" <spencer@chpdm.umbc> wrote in message
> > news:u9xKbRciIHA.4844@TK2MSFTNGP06.phx.gbl...
> >> Two query solution.
> >>
> >> SELECT DISTINCT [practice suppliers].SUPPLIER
> >> ,[AUTO CATEGORY]
> >> FROM [practice suppliers]
> >>
> >> Use that query (Saved) as the source for the following query
> >>
> >> SELECT SUPPLIER
> >> , Count([AUTO CATEGORY]) AS [CountOfAUTO CATEGORY]
> >> FROM [The Name of the Saved Query]
> >> GROUP BY SUPPLIER;
> >>
> >> If your field and table names consisted of only letter, number, and
> >> underscore characters, you could do that in one query.  One of many
> >> reasons to name tables and fields without spaces or other
non-alphanumeric
> >> characters.
> >>
> >> '====================================================
> >>  John Spencer
> >>  Access MVP 2002-2005, 2007-2008
> >>  Center for Health Program Development and Management
> >>  University of Maryland Baltimore County
> >> '====================================================
> >>
> >>
> >> Leslie Isaacs wrote:
> >>> Hello All
> >>>
> >>> I have been wrestling for ages now with a query that I thought would
be
> >>> simple .. but can't get the output I want!
> >>>
> >>> I have a table [practice suppliers] with fields SUPPLIER and AUTO
> >>> CATEGORY (and others). The field SUPPLIER is not a unique (key) field,
> >>> but in fact it almost is: ~95% of the records in the table contain
unique
> >>> values in this field.Of the remaining ~5%, about half of the records
for
> >>> a given SUPPLIER value contain the same value of AUTO CATEGORY, and
the
> >>> other half contain different values for AUTO CATEGORY.
> >>>
> >>> I want a query that will return the number of different values of AUTO
> >>> CATEGORY for each SUPPLIER. For the ~90%, and the first half of the
> >>> remaining ~5% (as referred to above), the query should return '1' as
the
> >>> number of different values of AUTO CATEGORY ... but for the other half
of
> >>> the ~5% it should return the actual number of different AUTO CATEGORY
> >>> values for the SUPPLIER.
> >>>
> >>> My attempts (which seem to keep coming back to the one below) keep
> >>> returning the total number of AUTO CATEGORY values for each SUPPLIER -
> >>> e.g. where there are 8 records in the table with the same value of
> >>> SUPPLIER and AUTO CATEGORY, my query returns '8' - whereas it should
> >>> return '1'. If the same SUPPLIER appears 8 times with, say, 5
different
> >>> AUTO CATEGORY values, the query should return '5'.
> >>>
> >>> Hope someone can help - this is driving me nuts!
> >>>
> >>> Thanks for any help.
> >>> Leslie Isaacs
> >>>
> >>> My 'attempt':
> >>> SELECT [practice suppliers].SUPPLIER, Count([practice suppliers].[AUTO
> >>> CATEGORY]) AS [CountOfAUTO CATEGORY]
> >>> FROM [practice suppliers]
> >>> GROUP BY [practice suppliers].SUPPLIER;
> >>>
> >


0
PayeDoc
3/20/2008 7:45:56 AM
Reply:

Similar Artilces:

attributes and simple types- the simple made difficult
I want to do something like <distance units="inches">15.0</distance> for validation reasons, I want to put a facet on the value so it can't be negative. To include an attribute, the schema editor forces me to have a complex type. How then do I apply the facet? The .net schema editor wants to do something like element distance (distance) distance attribute units string At this point I don't see how I can put the restriction (facet) on the element value. The editor wants to force me to put in something on the next line (group/ attribute/element/etc.) Thi...

Arabic ODBC query
I am trying to read and write arabic data into Access2000 using MFC ODBC (CRecordset) , but I can only see '?' marks. after doing a research on the news groups , I understand that a tweak needs to be made on RFX_Text can anyone guide me what needs to be done. Appreciate a response. thnx P.Ekkoratha ...

Simple File Saving question...
Everytime I open an Excel spreadsheet it opens to the last cell that I edited. Does anyone know how to make an Excel spreadsheet open to the top left cell on the first worksheet everytime... regardless of where I saved it last? Thanks, Ian Hi Ian, Try this Private Sub Workbook_Open() Worksheets("Sheet1").Activate Range("A1").Select End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the...

Conditional formatting query #2
Hi, I thought I knew how to do this, but it turns out I don't. If I have names in column A (eg Alan, Bob, Cathy), and values in column B (see below), is there any way I can use conditional formatting to change a cell in A, based on a value in B? A B 1 Alan 4 2 Bob 1 3 Cathy 3 Essentially what I'd like is to highlight the name, if the value is 3 or over. I know how to change the cells in B using conditional formatting, but can't figure out how to change A cells. Thanks, Fiona Select all the cells you want Conditionally formatted. Try this with Formul...

Multiple Item query
I've read a few posts on this query and it looks like creating a table where I input the item numbers in it and linking it to the query seems to be a good way to get a lot of items in a query. Now what If I have hundreds of numbers I want to look up? do I input all 100 numbers into the table? Or what if it's like 10 numbers but I want create seperate queries for different groups of numbers, should I have 1 table for one group of numbers? What would be the easiest way for a novice to do this? It is easier to fill the table with just the values you need for the query you want to run, t...

display ratios as written in web query
I am using a web query to display data about screen contrasts, all written with the formatting of "700:1" or "3000:1" It displays corectly in the edit web query window, but everything I try to make it display as delivered fails. I tried text, custom formats with @ etc, but it seems to insist on doing the calculation before turning it back to text, or as a ratio it does the division. (The text formatting for this works OK if I type directly into the spread sheet) Is there a way to make a web query display as written? Any help appreciated. Keith ...

Simple question #4
I'm relatively new to Excel and I want to creat a sheet for my expenses where I can enter a number in one column and have it subtracted from the above cell in the next column and so on, all the way down. I can do this by clicking on the bottom left of the cell and dragging it down but would like it to appear automatically. Sorry if that's not very clear. John In B1 enter the inital balance In A2 and on down enter your expenses In B2 enter: =IF(A2="","",B1-A2) and copy on down -- Gary''s Student - gsnu2007g "gtharwood@gmail.com" wrote: >...

Append Query Not Working 05-03-10
My Append query is not working and i am not sure why... Here is my SQL INSERT INTO tblContractPOTracking ( TrackingID ) SELECT tblDocTracking.TrackingID FROM tblDocTracking WHERE (((tblDocTracking.DocumentNumber)="5" Or (tblDocTracking.DocumentNumber)="6" Or (tblDocTracking.DocumentNumber)="7")); Please help! "not working" is a bit vague ... If you want more specific suggestions, please provide more specific description... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and s...

Display a count of unique records in a query in Access 2007
Hi, I'm not sure if I should be in this group, or Reports, but I'll start here. I have a user who would like to do a count of unique records and display the information in a query, or a report. Here are the basics: She has a list of donors, some of the donors donate more than once to the same cause. What she would like is a list of the number of donors, per cause. But, if the donor ID repeats for the same cause, she does not want that ID counted again. Example: We can get a query to return a count of the records per cause. It is returning a value of "7" for a cause,...

Query Date Help
I need to create a query that will do the following: these are my fields: resign date, hire date I need to subtract the resign date from hire date but I want it to return the answer as years, months & days ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days is there a way to use the DateDiff function to return years, monts & days? Look at this link on "A More Complete DateDiff Function" http://www.accessmvp.com/djsteele/Diff2Dates.html -- Good Luck BS"D "aldunford" wrote: > I need to create a query that will do the following: > > th...

Run query from date input on form
Hello all! I have what I hope is a easy situation. I have a table that has information that is entered daily with a field "Completed Date". I want to add a text box to a form where a user can enter a date and hit a button that will run a query for that specific completion date so I can have the query results exported to Excel. Would I use an unbound text box on the form? How would I set up the query to take the date entered in the form? Thank you very much! - Joker -- Message posted via http://www.accessmonster.com Yes, an unbound textbox would be one w...

question on tables and queries
I've imported 2 tables from excel and ran a query against those two tables to determined the difference. This is something I would like to do each month. I would like to keep the same settings in my database, is there a way I can easily import/update the data in the new table without creating a new one each month? The query would stay the same as I'm comparing the same two files, but the the data will be changing every month. I don't know much about access and would like to know if there is an easier way of doing this besides creating a new table and query each month? I&...

Query Needed 01-04-10
A sample of my data table looks as so: Underlying ID StrikePrice AAPL APVA10C155.00 155 AAPL APVM10P155.00 155 AAPL APVA10C160.00 160 AAPL APVM10P160.00 160 AAPL APVA10C165.00 165 AAPL APVM10P165.00 165 AAPL APVD10C160.00 160 AAPL APVP10P160.00 160 AAPL APVD10C165.00 165 AAPL APVP10P165.00 165 AAPL APVD10C155.00 155 AAPL APVP10P155.00 155 AAPL APVB10C155.00 155 AAPL APVN10P155.00 155 AAPL APVB10C160.00 160 AAPL APVN10P160.00 160 AAPL APVB10C165.00 165 AAPL APVN10P165.00 165 GS GPYA10C155.00 155 GS GPYM10P155.00 155 GS GPYA10C160.00 160 GS GPYM10P160.00 160 ...

Linq Query and Lambda
Hello, I am getting a list of regions as follows: return _context.Regions.Select(r => new RegionModel { Id = r.Id, Name = r.Name }).OrderBy(r => r.Name).AsQueryable(); However I need to get only the Regions which are related with Centers. So I need to check _context.Centers and get all used Regions Ids from each Center.Region.Id and then get all the Regions with those Ids ... Is this possible to do with a lambda expression ... I think it is possible but I am a little bit confused on this. Thank You, Miguel shapper wrote: > Hello...

3 queries into 1
Hello, I am going to try this again starting fresh from my other posts. I am trying to develop an all inclusive query on our inventory. Each fical term we start with new tables. I would like to take our Ending Inventory from our prior fiscal term and combine it with our current inventory balances. In years past I always took our ending inventory and posted them in our purchase order table to have our beginning balances. I thought it would be less time consuming to have a query to combine the 2. Below is a list of data and the query designs. PART # PRODUCT DESCRIPTION END B...

Query in Excell
I would like to create bolean statements in one or more of the columns that result from a query. Basically, I want to test a column for a factor and return a simple 'true' or 'false'. Using this I, hopefully, will eliminate the need to pass the data to Excel and perform the function there. ...

Lookup query value from Form
Hello. I have a form that I would like to lookup values in several taxt boxes. The values I want displayed are from several different Sum Queries. Example: I want to pull Total Working Blance from a query and monthly rent revenue from another query. The values all come from different tables. Is this possible? Thanks for the help. you can use a DLookup() function to retrieve the queries' values. read up on the function in Access Help, so you'll understand how it works. then add an expression to each textbox control's ControlSource property, as =DLookup("MyField", ...

querying similar databases plan
I'm working with hideously unnormalized databases, most of which have similar structures. (Of course, if they had the same structures, it would be too easy.) I work at a company that does cancer research, so they collect information about their patients over a series of visits. The questions they ask (queries) for parts of the final report are standard across all databases, and then some are specific to a the type of study they're doing. What is the best way to approach this, given that I have to summarize like 20 databases in maybe two weeks, and the column names are not necessari...

Double Results in Query
New to Access I am trying to create a query that returns data from 2 tables. one table can have multiple entries on one day per person and the other table will only have one entry per person. 2 tables are DataEntry - will have multiple entries per day per person CallLog - one entry per person per day SELECT DataEntry.OrderTakenBy, DataEntry.Company, DataEntry.Date, DataEntry. Category, DataEntry.ShortCode, DataEntry.ContactName, DataEntry.PositionTitle, DataEntry.PostDateExt, DataEntry.FirmPreview, DataEntry.AllorProgramCodes, DataEntry.Units, DataEntry.value, DataEntry.Mark...

Querying results for two recent dates
I have a table "DETAILS" in which fields are Name Id TestDate Grade (all fields can be duplicated) Now I want a query which should return me 'Name' 'Id' ' TestDate' 'Grade in 2nd Last Test' 'Grade in Latest Test' It should be noted that every person appears the test many times and i just want the recent two results. The query should return names of only those people who have appeared in either or both of the last two tests I was trying to do this by running a query on a query but the results were ...

SQL query for "capping threshold"
Suppose I want to add all the numbers in one column, with thefollowing caveat:All values should be capped at some high threshold level.As an example, suppose the threshold is 10000 and the numbers in thecolumn are 7500, 8500, 9500, 10500, 11500 and 12500. The query shouldreturn 555000 (which is 7500 + 8500 + 9500 + 10000 + 10000 + 10000).Any help will be appreciated.Thanks,Bhat SELECT Sum(IIf([YourField]>10000,10000,[YourField])) AS TheValueFROM tblCapped;Insert the right field and table names. Also I think that your calculation below has one too many zeros in it.-- Jerry Whittle, Microsof...

Update Query question 06-26-07
Hello, I am trying to write what I assume would be an update query. I have 2 fields, acct_num and brnch_num in my table. I need to combine these 2 fields into a new field called cust_num. So, if: brnch_num acct_num 123 45678 123 12 12 1 Then I would need the cust_num to read: 12345678, 12300012, 01200001 I'm not sure how to get the zeroes into the cust number so that that number is always 8 diguts. Any help would be greatly appreciated! I wouldn't use an update query for this. Keep the data separat...

Query Not Returning Correct Amount of Records
I have 6 tables that I have built 6 different queries on. Individually, these queries return the correct amount of records in the tables. Which is 80 records in all tables involved. Then I built a query that collects data from the 6 queries and this query returns 75 records. What am I doing wrong? Thanks Don As a guess, your query is not correctly structured for the data you have. For instance, if one of the 6 tables does not have a matching record that could cause you to "lose" a record or two in the query if you have set up a join to that table and have n...

Pivot Table view of Union Query
Dear All, I can see what I need at the Pivot Table view of a Union Query. However, this cannot be correctly exported to Excel. Although there is a function of "Export to Microsoft Excel", however, all "detail data" in Access was gone - leaving only the count of the data in Excel. How can I show all these detail data in the data area of the Pivot Table? Alternatively, is it possible to copy the Pivot Table in Access and then paste in Excel? I cannot see any Select Rows commands in the pull down menu. Thank you very much. Hong -- Message posted via AccessMonster.com h...

Query based Subform will not allow editing
I have a maintenance DB that has energy lockout points associated with pieces of equipment so we can safely do maintenance. I have an 'edit existing lockout' form, based on a query, that lists in a subform the various points required for a certain piece of equipment. My system used to work, but now I can edit the main, but the subform has locked me out. All I get is a doorbell tone when I attempt to enter info. I can enter the req'd info in the tables, but but my form went snafu. Any ideas? Thanks in advance Does your query allow editing? Check the asterisk in the record...