ACCESS FEED: Bls: Bls: Bls: [MS_AccessPros] Re: Sort Ascending Not Run Properly in Append Query

Kamis, 27 Desember 2012

Bls: Bls: Bls: [MS_AccessPros] Re: Sort Ascending Not Run Properly in Append Query

Duane, i have tried to execute the query again and again,,,but the result still wrong,...

Z and ZZ table fields :
[ID]= Long In., Indexed Duplicates (ok)
[Data]=Int., No indexed

example data (Z Table) :

ID Data
-- -   ---
5      1
5      5
6      3

then i made an append query from Z to
ZZ, :

INSERT INTO ZZ ( ID, Data )
SELECT Z.ID, Z.Data
FROM Z
ORDER BY Z.Data;

after i ran the query the result is
wrong, ZZ's Records same as the Z's Records like above...
because of the syntax :
ORDER BY Z.Data, i think the result should be like this :

the Results that should be in ZZ table :
ID Data
-- -   ---
5      1
6      3
5      5

but if i change the Append Query to Make
Table Query :

SELECT Z.ID, Z.Data INTO ZZ
FROM Z
ORDER BY Z.Data;

the results are right (Sort ascending
run properly in make table query or select query but not in Append Query)...

Regards
Hendra

________________________________
Dari: Duane Hookom <duanehookom@hotmail.com>
Kepada: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Dikirim: Kamis, 27 Desember 2012 22:59
Judul: RE: Bls: Bls: [MS_AccessPros] Re: Sort Ascending Not Run Properly in Append Query

What "result is wrong"? Do you not see the records sorted correctly when you create a query and order by the appropriate field?

Duane Hookom MVP
MS Access

----------------------------------------
> To: MS_Access_Professionals@yahoogroups.com
> From: agesthahendra@ymail.com
> Date: Thu, 27 Dec 2012 23:01:20 +0800
> Subject: Bls: Bls: [MS_AccessPros] Re: Sort Ascending Not Run Properly in Append Query
>
> Thanx Duane,...
>
> Yes, and when i try to order it using append query, it doesn't work...this is just a simple sql :
>
> INSERT INTO ZZ ( ID, Data )
> SELECT Z.ID, Z.Data
> FROM Z
> ORDER BY Z.Data;
>
> the result is wrong ([Data] not sorted properly)..., but if i change to Make Table Query or Select Query it works ... why append query gives the wrong results...?
>
> regards
> Hendra
>
>
>
>
> ________________________________
> Dari: Duane Hookom <duanehookom@hotmail.com>
> Kepada: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> Dikirim: Kamis, 27 Desember 2012 1:30
> Judul: RE: Bls: [MS_AccessPros] Re: Sort Ascending Not Run Properly in Append Query
>
>
>
> Hendra,
>
> I never expect records in a table to be in any particular order. I think of records in a table to be like marbles in a box. There is no order unless you use a query or similar with an "Order By".
>
> Duane Hookom MVP
> MS Access
>
> ----------------------------------------
> > From: agesthahendra@ymail.com
> >
> > Thanx Clive,..
> >
> > But how if the Z table is a Detail table ..? i named the field as ID is just an example, the truth name is [IDR_Det], it's a Link Child Field...so it's really possible to not unique...
> > But...beside that i really wonder if i change the Append Query to Make Table Query or to Select Query the result is right that the [Data] is sorted ascendingly...
> > Only Append Query gives the wrong result...
> >
> > Regards
> > Hendra
> >
> >
> >
> > ________________________________
> > Dari: Clive <zctek@aol.com>
> >
> > Hi Hendra,
> >
> > Normally an ID Field would be unique. Your Z table
> > has the value 5 repeated in the first two records.
> >
> > You should always have a unique field or unique
> > combination of fields for a primary Key.
> >
> > Hope that helps,
> >
> > Regards, Clive.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "agesthahendra@..." <agesthahendra@...> wrote:
> > >
> > > Hi Everyone,...
> > >
> > > I try to describe what i mean :
> > >
> > > I have two tables, named Z (as source) and ZZ (as destination), they each has 2 fields, [ID] (Long Int. - indexed) and [Data] (Int. - no indexed),
> > > i want to append all the records in Z to ZZ, for example i've made 3 records in Z table (as source) :
> > >
> > > ID Data
> > > -- ----
> > > 5 1
> > > 5 5
> > > 6 3
> > >
> > > then i made an append query from Z to ZZ, :
> > >
> > > INSERT INTO ZZ ( ID, Data )
> > > SELECT Z.ID, Z.Data
> > > FROM Z
> > > ORDER BY Z.Data;
> > >
> > > after i ran the query the result is wrong, Still the same as the Z's Records like above... because of the syntax : ORDER BY Z.Data, i think the result should be like this :
> > >
> > > ID Data
> > > -- ----
> > > 5 1
> > > 6 3
> > > 5 5
> > >
> > > but if i change the Append Query to Make Table Query :
> > >
> > > SELECT Z.ID, Z.Data INTO ZZ
> > > FROM Z
> > > ORDER BY Z.Data;
> > >
> > > the results are right (Sort ascending run properly in make table query or select query)...
> > >
> > > I need to use Append Query, What do i miss here..?
> > >
> > > Regards
> > > Hendra
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

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