Sorting Access Tables in VB

[Daine]

Hi again everyone.

Just trying to work out how to sort tables in VB. I have two Access queries I'm usling to append data into one table, I tried sorting each query as it was apended in the SQL but using an orderby didn't work and besides I have an added field that give the source table I want to sort by.

I have tried orderby but I can't seem to get it working.

I want to order the data in the table after they both have been appended by InvNo, Date and then the Source Table, this is just to make it easy to view.

After I've sorted I want to run a query over it that counts where the invoices match- thats easy to do but I already know I'll get and error as I'm not doing anyhting with the query other than counting matches, all I want to do is count and display the result to the screen, as if I was running a normal query in access.

[Dököll]

Hi again everyone.

Just trying to work out how to sort tables in VB. I have two Access queries I'm usling to append data into one table, I tried sorting each query as it was apended in the SQL but using an orderby didn't work and besides I have an added field that give the source table I want to sort by.

I have tried orderby but I can't seem to get it working.

I want to order the data in the table after they both have been appended by InvNo, Date and then the Source Table, this is just to make it easy to view.

After I've sorted I want to run a query over it that counts where the invoices match- thats easy to do but I already know I'll get and error as I'm not doing anyhting with the query other than counting matches, all I want to do is count and display the result to the screen, as if I was running a normal query in access.

Hello Daine!

Try using ORDER BY, life saver. Here is an example:

http://www.adit.co.uk/html/sqlforaccess2.html

You will find other means to do this, especially if you need to be more specific. ORDER BY always did it for me.

Good luck and welcome!

[Killer42]

The important thing to remember is that you don't sort data when storing it. It is stored in an arbitrary sequence. In other words, you generally won't know, or care, what order it is stored in. You retrieve it in the order you want it. After all, if you had two reports that need the data sorted different ways, would you store two copies of the data?

If you want to count groups, for instance, that's where the aggregate functions come in. The Access forum is probably the best place to ask about them.

[Daine]

The important thing to remember is that you don't sort data when storing it. It is stored in an arbitrary sequence. In other words, you generally won't know, or care, what order it is stored in. You retrieve it in the order you want it. After all, if you had two reports that need the data sorted different ways, would you store two copies of the data?

If you want to count groups, for instance, that's where the aggregate functions come in. The Access forum is probably the best place to ask about them.

Yeah I realised you can't sort data as it is appending the record as it come to it, the reason I'm asking it here is because I'm coding it from a button, and I have the queries but some will not work as indended in code.

I've used two quieies to append the two tables to one.

Below it I have an order by statement in the query but understandably can't get it to do anything- I have tired appending the tables to the new one, use Orderby to order the table- I used the in program help (usually works well), and looked at the website but it is all done as SQL again I can't do that as I'm not doing anything with the query.

Not sure if this would help:
My program is to compare two programs- our Accounting package and Invoicing System to see if the invoiced values match- I have a table from the Accounting package and one from the invoicing system.

I have appended the values of interest to the table and then want to sort my InvNo, Date and which program its from- I can do this is a series for quiery, which is fine for those literate enought to use Access- but I want it off of a button press for those who could not undertstand the steps.

I have gotten the first three steps done, now I need to order it for viewing and finding the data needed. Then I will do a quiry to find values where both the Invoice number and Value are distinct- then do a count on each Invoice number to see where it is over 1 (ie now apprears in the table twice as duplicate numbers should have been removed in the distinct query unless they were invoiced and charged at different values- which is what I want to find).

Again I have done this though individual queries but the problem is that some of them give runtime errors when copied into code as the query itself does nothing other than allow them to view the data- this will also be a problem at the end when I want to view the final data, but I'll worry about that when I get there, my thought is to dump it to a new table and delete the table when the window is closed, like a normal query would display, can this be done?

[Dököll]

Hello, Daine!

I reread your note and realized you have been trying to use Order by and it does not work for you. Have you tried typing up the query:

 
SELECT * From ThisTable
 
   WHERE UserID <= 3
   AND Name Like "Pool*"
   ORDER BY Name, UserID
 
'{you can also GROUP BY}
 
 

This is just from the top of my head. You can then add to VB, under your button. You mentioned also looking for duplicates, UserID <= 3 would look for UserIDs or the field you think will have dupes if more than one.

I would change to UserID > 1 if yu think there may be more. This should work.

Also if you needed to, and you probably already tried this, but you should query to find dupes in Access (Using Duplicates query). I think running a query to find dupes before hand, in Access, then going in the SQL view, copy the code there, can save you some time.

I would also attempt a Crosstab query, then copy the code. Sorting and grouping through design view can be cumbersome in Access.

Here is something, I just ran using the Northwind database information:

 
TRANSFORM Count(Customers.FirstName) AS CountOfFirstName
SELECT Customers.CustomerID, Customers.CompanyName, Count(Customers.FirstName) AS [Total Of FirstName]
FROM Customers
GROUP BY Customers.CustomerID, Customers.CompanyName
PIVOT Customers.BillingAddress;
 

Give that a whirl then write if additional help is needed...

[Daine]

[quote=Dököll]Hello, Daine!

I reread your note and realized you have been trying to use Order by and it does not work for you. Have you tried typing up the query:

 
SELECT * From ThisTable
 
   WHERE UserID <= 3
   AND Name Like "Pool*"
   ORDER BY Name, UserID
 
'{you can also GROUP BY}
 
 

I got the ORDER BY working I just had a silly error- the date field was a text field type instead of date/time meaning the last date would be the 9th of the month, once I changed it to a date it read as it should.


And yes I copy from the SQL part of Access, it where I got a lot of my code from lol.


For the other part, finding the non-unique values:
These are the three Access Queries I have:

SelectUnique
SELECT DISTINCT [Invoicing Errors].InvNo, [Invoicing Errors].GSTFREE FROM [Invoicing Errors];

CountUnique
SELECT First([SelectUnique].InvNo) AS [InvNo Field], Count([SelectUnique].InvNo) AS NumberOfDups FROM [SelectUnique] GROUP BY [SelectUnique].InvNo HAVING (Count([SelectUnique].InvNo)>1);"

DisplayErrors
SELECT CountUnique.[InvNo Field], [Invoicing Errors].Period, [Invoicing Errors].ACCID, [Invoicing Errors].NAME, [Invoicing Errors].GSTFREE, [Invoicing Errors].From INTO DisplayResults FROM CountUnique INNER JOIN [Invoicing Errors] ON CountUnique.[InvNo Field] = [Invoicing Errors].InvNo WHERE (((CountUnique.[InvNo Field])=[Invoicing Errors].[InvNo] And (CountUnique.[InvNo Field])<>'HS000000' And (CountUnique.[InvNo Field])<>'OX000000')) ORDER BY CountUnique.[InvNo Field], [Invoicing Errors].Period, [Invoicing Errors].From;

If I run the last one in Access it works fine as it run all the others VB doesn't do this so I can't get the last step done as VB will not run other queries.

[Daine]

Hello, Daine!

I reread your note and realized you have been trying to use Order by and it does not work for you. Have you tried typing up the query:

 
SELECT * From ThisTable
 
   WHERE UserID <= 3
   AND Name Like "Pool*"
   ORDER BY Name, UserID
 
'{you can also GROUP BY}
 
 

This is just from the top of my head. You can then add to VB, under your button. You mentioned also looking for duplicates, UserID <= 3 would look for UserIDs or the field you think will have dupes if more than one.

I would change to UserID > 1 if yu think there may be more. This should work.

Also if you needed to, and you probably already tried this, but you should query to find dupes in Access (Using Duplicates query). I think running a query to find dupes before hand, in Access, then going in the SQL view, copy the code there, can save you some time.

I would also attempt a Crosstab query, then copy the code. Sorting and grouping through design view can be cumbersome in Access.

Here is something, I just ran using the Northwind database information:

 
TRANSFORM Count(Customers.FirstName) AS CountOfFirstName
SELECT Customers.CustomerID, Customers.CompanyName, Count(Customers.FirstName) AS [Total Of FirstName]
FROM Customers
GROUP BY Customers.CustomerID, Customers.CompanyName
PIVOT Customers.BillingAddress;
 

Give that a whirl then write if additional help is needed...

I got the Order By working it was a silly error- the date field was a text type rather than Date/Time meaning the 9th dates were always last, a type change to Date/Time fixed that.

I always copy from the Access SQL unless it is basic, makes it easier and less chance of errors.

As for the finding the unique invoice/invoice value totals, here are the three queries I used in Access, it would link them together wher the last one was referenced VB won't so I now need help on how to join them up, any help is apprecaited.


SelectUnique
SELECT DISTINCT [Invoicing Errors].InvNo, [Invoicing Errors].GSTFREE
FROM [Invoicing Errors];

CountUnique
SELECT First([SelectUnique].InvNo) AS [InvNo Field], Count([SelectUnique].InvNo) AS NumberOfDups
FROM [SelectUnique]
GROUP BY [SelectUnique].InvNo HAVING (Count([SelectUnique].InvNo)>1);

DisplayErrors
SELECT CountUnique.[InvNo Field], [Invoicing Errors].Period, [Invoicing Errors].ACCID, [Invoicing Errors].NAME, [Invoicing Errors].GSTFREE, [Invoicing Errors].From INTO DisplayResults
FROM CountUnique INNER JOIN [Invoicing Errors] ON CountUnique.[InvNo Field] = [Invoicing Errors].InvNo
WHERE (((CountUnique.[InvNo Field])=[Invoicing Errors].[InvNo] And (CountUnique.[InvNo Field])<>'HS000000' And (CountUnique.[InvNo Field])<>'OX000000') )
ORDER BY CountUnique.[InvNo Field], [Invoicing Errors].Period, [Invoicing Errors].From;