Keith Williamson
Kwilliamson at RTKL.com
Thu Feb 8 13:33:52 CST 2007
Well....our labor detail table, alone, is 2.5 million records. Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos Sent: Thursday, February 08, 2007 12:54 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Linked Table Limits? Well since a number is normally compressed into fewer bytes than a text field with the same value there would be some economy there with a fewer number of bytes needing to be compared or indexed but if everything is indexed the same I don't know if you would notice a huge difference. No doubt if you get into huge databases with millions of records the compression that numerics bring to the party would make things a lot faster. GK On 2/8/07, Keith Williamson <Kwilliamson at rtkl.com> wrote: > I thought that both Access and Sql do searches much faster on numbers, > rather than text. ?? This application is pretty slow. I assumed it > was from all the text keys. ?? > > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com > > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland > 21231-3305 > > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos > Sent: Thursday, February 08, 2007 12:19 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Linked Table Limits? > > Well, I was taught that numbers that you don't expect to do > mathematics on SHOULD be stored as text..... perhaps those guys went > to my school? ;-) > > GK > > On 2/8/07, Keith Williamson <Kwilliamson at rtkl.com> wrote: > > Sounds similar. This sql database is a major software > > (Architectural/Service Industry....Labor-Based) called Vision. > > Unfortunately, the designers didn't really seem to have a lot of > > "vision" when designing it. All the Key fields seem to be text > > fields......that's right...I said TEXT fields. Employee Number, > Project > > Number, etc. ......ALL TEXT. WHAT WERE THEY THINKING???? > > > > <:( > > > > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com > > > > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland > > 21231-3305 > > > > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com > > > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos > > Sent: Thursday, February 08, 2007 11:18 AM > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] Linked Table Limits? > > > > We have an Oracle table in our data warehouse that if we just link to > > and use the results of that link it works fine but if I put a criteria > > on anything in that table it blows up and causes an Oracle Error > > message. It's the Customer Location table and someone wanted all the > > sales to California. I tried to select by the ship to state and it > > blew up. I tried it again and it blew up again. And Again. I thought > > it was something I was doing wrong. Then I did it pulling all records > > into a temporary table including the state and then selecting only the > > california sales from there and it worked fine. Go figure. > > > > GK > > > > On 2/8/07, Keith Williamson <Kwilliamson at rtkl.com> wrote: > > > I suspect there must be a corrupted record in that particular table. > > I > > > know I have run queries before with just as many, if not more, > joined > > > tables (that are linked to sql database.) I was hoping someone had > > some > > > quick info about such a situation. But alas, I have to dump the > issue > > > on our MIS laps, to investigate the offending table. > > > > > > Thanks, > > > > > > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com > > > > > > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland > > > 21231-3305 > > > > > > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com > > > > > > -----Original Message----- > > > From: accessd-bounces at databaseadvisors.com > > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby > > > Sent: Thursday, February 08, 2007 10:29 AM > > > To: 'Access Developers discussion and problem solving' > > > Subject: Re: [AccessD] Linked Table Limits? > > > > > > Hmmm... I think something else is going on. AFAIK you can have > every > > > table > > > in your database be out in SQL Server and thus you could have dozens > > of > > > SQL > > > Server tables in a query. I have to admit that I do not have many > > > "mixed" > > > databases, and the ones I do have tend to use just one or two views > > from > > > SQL > > > Server. > > > > > > If you have OTHER SQL Server tables, I would try to build queries > > using > > > those tables to see how they function. > > > > > > > > > John W. Colby > > > Colby Consulting > > > www.ColbyConsulting.com > > > > > > -----Original Message----- > > > From: accessd-bounces at databaseadvisors.com > > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith > > > Williamson > > > Sent: Thursday, February 08, 2007 10:16 AM > > > To: Access Developers discussion and problem solving > > > Subject: [AccessD] Linked Table Limits? > > > > > > Hi all, > > > > > > > > > > > > Does anyone know if there are limits to the number of linked tables > > (to > > > a > > > SQL database) that can be included in an Access query? I have a > query > > > that > > > joins (2) linked tables and (2) local Access tables. That runs very > > > fast. > > > However, if I try to add one more linked table to the query.....it > > > simply > > > crashes the database. I've even tried using a sql select statement > in > > > the > > > query, to get the data from that last linked table......crashes the > > > database. IF I copy the entire linked table to a local table, and > > then > > > select/join to that table......the query runs fast. When I try > > > exporting > > > the query, with the select statement to the linked table.....my > > > resultant > > > export (incomplete due to the crash) is corrupted. > > > > > > > > > > > > So, my question is....is there some sort of limit to the number of > sql > > > tables that I can join, in a query? There doesn't seem to be > anything > > > wrong > > > with the sql table I am trying to join. Just very odd. > > > > > > > > > > > > Thanks, > > > > > > > > > > > > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com > > > > > > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland > > > 21231-3305 > > > > > > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com > > > <http://www.rtkl.com/> > > > > > > > > > > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > > > > -- > > Gary Kjos > > garykjos at gmail.com > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > -- > Gary Kjos > garykjos at gmail.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Gary Kjos garykjos at gmail.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com