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

Kamis, 27 Desember 2012

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

Thanx Bill,..
Please call me Hendra...it's my first name... :)

The truth is there is 1 field more in ZZ table, named [Hasil], after inserting rcords to ZZ i want to set [Hasil] values with :

    s = "SELECT *
FROM ZZ"
    Set db = CurrentDb
    Set rs =
db.OpenRecordset(s)

    If Not rs.EOF Then
    rs.MoveFirst
    X = 0
    While Not rs.EOF
    db.Execute
"UPDATE ZZ Set Hasil =" & rs!Data - (X + 1) & " WHERE
Data = " & rs!Data
    X = rs!Data
    rs.MoveNext
    Wend
    Else
    MsgBox "Belum
ada data", vbInformation, "INFORMASI"
    End If

and i need [ID] for other purpose,...but i've found the reason why the append query doesn't give the correct result, it's because [ID] indexing in ZZ,
it seem that Access auto sort the [ID] in ZZ because it's indexing...at least now i get the more new knowledge about indexing and sorting...thanx Bill

Regards
Hendra

________________________________
Dari: Bill Mosca <wrmosca@comcast.net>
Kepada: MS_Access_Professionals@yahoogroups.com
Dikirim: Jumat, 28 Desember 2012 4:09
Judul: RE: Bls: Bls: Bls: [MS_AccessPros] Re: Sort Ascending Not Run Properly in Append Query

Agesta

Why are you using an ORDER BY clause with an INSERT query? The only reason to do that would be if you were selecting a TOP xx such as:

INSERT INTO Tbl_B ( ID, Data )
SELECT TOP 100 Tbl_A.ID, Tbl_A.Data
FROM Tbl_A
ORDER BY Tbl_A.Data;

The final ordering in Tbl_B will still rely on the indices in that table, not how you insert the records. I think you are misunderstanding how sorting works in tables. Technically speaking, the order of table records is meaningless. They can be sorted when displayed in a form or report.

Regards,
Bill Mosca,
Founder, MS_Access_Professionals
That'll do IT <http://thatlldoit.com/> http://thatlldoit.com
MS Access MVP
<https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E

My Nothing-to-do-with Access blog

<http://wrmosca.wordpress.com> http://wrmosca.wordpress.com

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Agestha Hendra
Sent: Thursday, December 27, 2012 12:24 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Bls: Bls: Bls: Bls: [MS_AccessPros] Re: Sort Ascending Not Run Properly in Append Query

Duane

I Give up...i tried again with the diffrent table name ... and again Append Query give the wrong result :

INSERT INTO Tbl_B ( ID, Data )
SELECT Tbl_A.ID, Tbl_A.Data
FROM Tbl_A
ORDER BY Tbl_A.Data;

What's wrong with my MS Access....

________________________________
Dari: Agestha Hendra <agesthahendra@ymail.com <mailto:agesthahendra%40ymail.com> >
Kepada: "MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> " <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> >
Dikirim: Jumat, 28 Desember 2012 3:13
Judul: Bls: Bls: Bls: Bls: [MS_AccessPros] Re: Sort Ascending Not Run Properly in Append Query

Hey...this is really strange,...now the query return the right / expected records...Lol,,, :D :D
how it can be..??

Regards
Hendra

________________________________
Dari: Duane Hookom <duanehookom@hotmail.com <mailto:duanehookom%40hotmail.com> >
Kepada: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com <mailto:ms_access_professionals%40yahoogroups.com> >
Dikirim: Jumat, 28 Desember 2012 2:07
Judul: RE: Bls: Bls: Bls: [MS_AccessPros] Re: Sort Ascending Not Run Properly in Append Query

Hendra,

Are you suggesting this query doesn't return the expected results:

SELECT ID, Data
FROM ZZ
ORDER BY Data

Or, are you still expecting the un-ordered records in a table to be ordered?

Duane Hookom MVP
MS Access

----------------------------------------
> From: agesthahendra@ymail.com <mailto:agesthahendra%40ymail.com>
>
> 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 <mailto:duanehookom%40hotmail.com> >
>
> 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
>
> ----------------------------------------
> > From: agesthahendra@ymail.com <mailto:agesthahendra%40ymail.com>
> >
> > 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 <mailto:duanehookom%40hotmail.com> >
> >
> > 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 <mailto:agesthahendra%40ymail.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
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.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]

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

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

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