ACCESS FEED: [MS_AccessPros] first specifier in totals query doesn't work

Rabu, 23 Oktober 2013

[MS_AccessPros] first specifier in totals query doesn't work

"First" would mean the first alphabetically. I'm working with a very strange
app, in which the business logic which allows multiple vendors/parts for any
particular part ID, but the first manufacturer (alphabetically) is the
"default" part. Yeah, I know, I wouldn't have done it that way either -
forces you to do strange things like put an asterisk as the first char of
the manufacturer you want as the default (unless you're lucky enough to have
your chosen manufacturer be the first alphabetically).

At any rate, the strange thing for me was that this query has actually
worked for several (at least 3) years, and now no longer functions
correctly.

To get it working, I've written some VBA code, to produce the table. But
it's a bit slower than the query.

I will try the MIN function instead of FIRST.

Thanks for the help!

Rob Whitney

GARDENING is cheaper than therapy ... and you get TOMATOES!

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Tuesday, October 22, 2013 12:52 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] first specifier in totals query doesn't work

Min will work, too. I didn't notice the GROUP BY clause. But if Rob wants
an updatable query (this one is not), that requires a subquery.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Tuesday, October 22, 2013 6:11 PM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] first specifier in totals query doesn't work

Hi John,

Since Rob stated "first Manufacturer Name (alphabetically)" and the only
other field returned is ce_tmp_Default_Purch_Part.ID I assume Min() should
work. If additional fields were needed then I would use a subquery or
similar solution.

Duane Hookom MVP
MS Access

----------------------------------------
> From: JohnV@msn.com
>
> Duane & Rob-
>
> First grabs the first physical record that satisfies the request - it
> is not sensitive the the Primary Key or any specified sort order.
>
> What defines "first"? The manufacturer with the lowest name in the
> collating sequence? The manufacturer that has the lowest value in its
> Primary Key? There is a way to do this using a Subquery, but I need to
> know what you mean by "first."
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
>
> -----Original Message-----
> Duane Hookom
>
> I have never found FIRST or LAST to be a reliable solution. Consider
> using
> Min() rather than First().
>
> Duane Hookom MVP
> MS Access
>
> ----------------------------------------
>> From: roblwhitney@frontier.com
>>
>> I'm using Access 2010 on a Win7 Pro 64-bit client.
>>
>> I have 3 tables which need to be queried, to determine a Manufacturer
> Name. Each part can have 1 or more Manufacturers. The goal is to
> select the first Manufacturer Name (alphabetically), and build a table
> with the Part ID, and that Manufacturer Name. But when I run this
> query, it isn't always selecting the first Manufacturer Name. It
> almost seems that the selection is random. It appears that the "first"
> specifier isn't functioning as it should.
>>
>> Is there something wrong with my query logic? Have I violated some
>> syntax
> rule? Or is this another Microsoft "feature" ?
>>
>> Here is the query:
>>
>> SELECT ce_tmp_Default_Purch_Part.ID, First(dbo_VENDOR_QUOTE.MFG_NAME)
>> AS FirstOfMFG_NAME INTO ce_tmp_parts FROM ce_tmp_Default_Purch_Part
>> INNER JOIN (dbo_VENDOR_PART INNER JOIN dbo_VENDOR_QUOTE ON
>> (dbo_VENDOR_PART.VENDOR_PART_ID = dbo_VENDOR_QUOTE.VENDOR_PART_ID)
>> AND (dbo_VENDOR_PART.VENDOR_ID = dbo_VENDOR_QUOTE.VENDOR_ID)) ON
>> (ce_tmp_Default_Purch_Part.PREF_VENDOR_ID =
>> dbo_VENDOR_PART.VENDOR_ID) AND (ce_tmp_Default_Purch_Part.ID =
>> dbo_VENDOR_PART.PART_ID) GROUP BY ce_tmp_Default_Purch_Part.ID ORDER
>> BY ce_tmp_Default_Purch_Part.ID, First(dbo_VENDOR_QUOTE.MFG_NAME);
>>
>> Thanks for your help!
>

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]