Re: Link table alternative
From: MGFoster (me_at_privacy.com)
Date: 04/29/04
- Next message: Alan Nicoll: "Re: Splitting a record in two"
- Previous message: Rachel: "Record Ranking"
- In reply to: B Nieds: "Re: Link table alternative"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 29 Apr 2004 21:54:20 GMT
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I've found that it is better to import large text files rather than to
link to them. When linked Text Files, wierd errors appear that can't be
solved, except by importing the data to a temp table.
If your query is an actual query & not an example: the query can be
simplified as:
SELECT phsmgr, phscde, SUM(phscst) As Total
FROM tbl1
GROUP BY phsmgr, phscde
The subquery in the SELECT clause will return a record for each phscde
in tbl1, 'cuz you GROUPed BY phscde. That was the cause of the error
"only one record can be returned," which, confusingly, means that the
subquery should return ONLY one record, but is, in fact, returning more
than one record.
I included phscde in the SELECT clause, because to GROUP BY a column &
SUM() on each instance of that columns value w/o showing the column
value can be confusing. E.g.:
Include column value:
phscde Total
- --------- -----
1 25
2 30
3 1
4 5
When phscde is not shown the result is:
Total
- -----
25
30
1
5
Which can be meaningless when the associated column value (phscde) is
missing.
-- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQJF5joechKqOuFEgEQJdvgCaAkp/FG9M10MwXe82BR888/C4QHQAoKuC tOWjfbgYaDt+UXHXSPERgNm5 =EFUe -----END PGP SIGNATURE----- B Nieds wrote: > Hi: > > Simple query really: > > Select tbl1.phsmgr, (Select Sum(tbl1.phscst) as TCost from tbl1 Group by > tbl1.phscde;) as Total from Tbl1; > > Now I know I could use grouping in the above example but I wanted to show > the simplicity which would cause the error. The above query will when run > return the error "database engine could not lock table 'tbl1' because it is > already in use by another person or process". If I run the above query > against an imported table then I get the error that only one record can be > returned but at least it runs. > > Normally tbl1 has a join to tbl2 which is joined to tbl3 which combine to > provide the information that I want. I can circumvnent the problem by > importing the data rather than linking but as I mentioned earlier the data > is only as current as the import. > > Thanks > Bill > "MGFoster" <me@privacy.com> wrote in message > news:4qdkc.17579$e4.572@newsread2.news.pas.earthlink.net... > >>Post your query's SQL statement. Maybe something there we can analyze. >> >>-- >>MGFoster:::mgf00 <at> earthlink <decimal-point> net >>Oakland, CA (USA) >> >> >>B Nieds wrote: >> >> >>>Hi: >>> >>>I am using Access to report on data maintained outside of Access. I have >>>created links to the ourside files and have then created Queries, Forms, > > and > >>>Reports using these links. All of that is working reasonably ok. >>> >>>The problem I encounter is when I need to get more complex. Access will > > then > >>>complain that the linked table is in use (or opened exclusively) by > > another > >>>user. I tested with a copy of the files locally on my computer with the > > same > >>>results. Example is a subquery use the same table as the main query. >>> >>>Is this a limitation of linked tables? Is there a workaround or > > alternative > >>>method for getting the data? I know I can import the data but seems to >>>defeat the purpose of linked tables. >> > >
- Next message: Alan Nicoll: "Re: Splitting a record in two"
- Previous message: Rachel: "Record Ranking"
- In reply to: B Nieds: "Re: Link table alternative"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
