M
Michael San Filippo
Can you make dlookup in an sql statment instead of making a query?
example:
If DCount("WCID", "WoundCount") > 0 Then
example:
If DCount("WCID", "WoundCount") > 0 Then
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
make dlookup in an sql statment instead of making a query
W
Wayne Morgan
I don't fully understand the question the way it is worded, but the example
you give should work. It will return the number of records that don't have a
Null value in the WCID field.
you give should work. It will return the number of records that don't have a
Null value in the WCID field.
M
Michael San Filippo
Yeah, it works that way, but thats using a query named WordCount
I want to be able to put in an SQL string instead so that I don't have to
make a query and save it
I want to be able to put in an SQL string instead so that I don't have to
make a query and save it
G
Guest
As in..
SELECT yourtable.yourfield FROM yourtabl
WHERE (((DCount("[WCID]", "WoundCount") > 0))
You can use the DCount function in an SQL statement just like this. If you aren't so great at writing SQL then just build the query in design view in Access. Then, instead of saving it as a query, go to View/SQL View to see the equivalent SQL statement (which you can copy and paste as needed)
SELECT yourtable.yourfield FROM yourtabl
WHERE (((DCount("[WCID]", "WoundCount") > 0))
You can use the DCount function in an SQL statement just like this. If you aren't so great at writing SQL then just build the query in design view in Access. Then, instead of saving it as a query, go to View/SQL View to see the equivalent SQL statement (which you can copy and paste as needed)
W
Wayne Morgan
Can you post the SQL from the WoundCount query that you currently have?
M
Michael San Filippo
sure can -
SELECT WoundCare.*, TreatmentTracking.TTvisitdate,
TreatmentTracking.TTvisittime
FROM TreatmentTracking INNER JOIN WoundCare ON TreatmentTracking.TTid =
WoundCare.WCvisitID
WHERE (((TreatmentTracking.TTinitID)=[Forms]![ClinicalMain]![TTinitID]) AND
((WoundCare.WCnum)=[Forms]![ClinicalMain]![WoundNum]))
ORDER BY WoundCare.WCid DESC;
SELECT WoundCare.*, TreatmentTracking.TTvisitdate,
TreatmentTracking.TTvisittime
FROM TreatmentTracking INNER JOIN WoundCare ON TreatmentTracking.TTid =
WoundCare.WCvisitID
WHERE (((TreatmentTracking.TTinitID)=[Forms]![ClinicalMain]![TTinitID]) AND
((WoundCare.WCnum)=[Forms]![ClinicalMain]![WoundNum]))
ORDER BY WoundCare.WCid DESC;
W
Wayne Morgan
While the query isn't complicated, I would still leave it as you have it and
call the query in the DCount statement. This will take advantage of Access
optimizing the query's performance. If it was just a matter of the WHERE
clause, this could be incorporated, but you are also limiting your records
with the join to another table.
If you explain further what you are trying to accomplish by getting rid of
the query, I may be able to think of something else.
call the query in the DCount statement. This will take advantage of Access
optimizing the query's performance. If it was just a matter of the WHERE
clause, this could be incorporated, but you are also limiting your records
with the join to another table.
If you explain further what you are trying to accomplish by getting rid of
the query, I may be able to think of something else.
M
Michael San Filippo
I was just curious on if there was a way to do it. I hate having tons of
queries in a DB. makes it messy ;-)
Plus then I have to remember to export it heh..
Ohh well, thanks anyways.
Any idea when Access is going to come up with a feature so you can see how
big a table is in the database. like a size field for tables/queries/modules
thanks again
Mike
queries in a DB. makes it messy ;-)
Plus then I have to remember to export it heh..
Ohh well, thanks anyways.
Any idea when Access is going to come up with a feature so you can see how
big a table is in the database. like a size field for tables/queries/modules
thanks again
Mike
Wayne Morgan said:While the query isn't complicated, I would still leave it as you have it and
call the query in the DCount statement. This will take advantage of Access
optimizing the query's performance. If it was just a matter of the WHERE
clause, this could be incorporated, but you are also limiting your records
with the join to another table.
If you explain further what you are trying to accomplish by getting rid of
the query, I may be able to think of something else.
--
Wayne Morgan
MS Access MVP
sure can -
SELECT WoundCare.*, TreatmentTracking.TTvisitdate,
TreatmentTracking.TTvisittime
FROM TreatmentTracking INNER JOIN WoundCare ON TreatmentTracking.TTid =
WoundCare.WCvisitID
WHERE (((TreatmentTracking.TTinitID)=[Forms]![ClinicalMain]![TTinitID]) AND
((WoundCare.WCnum)=[Forms]![ClinicalMain]![WoundNum]))
ORDER BY WoundCare.WCid DESC;
W
Wayne Morgan
Any idea when Access is going to come up with a feature so you can see how
No I don't, sorry. But it sounds useful.
--
Wayne Morgan
MS Access MVP
tables/queries/modulesbig a table is in the database. like a size field for
No I don't, sorry. But it sounds useful.
--
Wayne Morgan
MS Access MVP
tables/queries/modulesMichael San Filippo said:I was just curious on if there was a way to do it. I hate having tons of
queries in a DB. makes it messy ;-)
Plus then I have to remember to export it heh..
Ohh well, thanks anyways.
Any idea when Access is going to come up with a feature so you can see how
big a table is in the database. like a size field for
T
Tim Ferguson
PMFJI
Just the opposite: a normal database has a bunch of tables and _hundreds_
of queries -- just look at the number of SPs and Views that come in a SQL
Server setup.
The main advantage is that all data access is concentrated in one place so
they can be maintained easily if the table structure has to be modified. A
weakness of Access is the way that little bits of SQL get ferretted away in
RowSource and RecordSource properties. Another advantage is that the query
optimiser already knows how to run predefined queries, so that they will
run (alright, only a tiny bit) quicker.
Why would anybody care? The only thing that matters is the .mdb file size;
and you can get that from Windows Explorer.
B wishes
Tim F
I was just curious on if there was a way to do it. I hate having tons
of queries in a DB. makes it messy ;-)
Just the opposite: a normal database has a bunch of tables and _hundreds_
of queries -- just look at the number of SPs and Views that come in a SQL
Server setup.
The main advantage is that all data access is concentrated in one place so
they can be maintained easily if the table structure has to be modified. A
weakness of Access is the way that little bits of SQL get ferretted away in
RowSource and RecordSource properties. Another advantage is that the query
optimiser already knows how to run predefined queries, so that they will
run (alright, only a tiny bit) quicker.
Any idea when Access is going to come up with a feature so you can see
how big a table is in the database. like a size field for
tables/queries/modules
Why would anybody care? The only thing that matters is the .mdb file size;
and you can get that from Windows Explorer.
B wishes
Tim F
M
Michael San Filippo
I wanted to know the size of a table so that I know if the table is static
enough to export it to the frontend.
Also, Backup tables, when I change some information i normally make a backup
of the table, for example
Table Intial (main table that drives everything)
If i change it to be more Third Teir type of database, with just unique ID's
instaead of for example the claimant name/patient name/name of account
Want to know if this is what is bloating my db, so i can delete it, normally
i make a copy, delete it, then compact and look at the diff, but this is
alot of work ya know?
jsut a thought
enough to export it to the frontend.
Also, Backup tables, when I change some information i normally make a backup
of the table, for example
Table Intial (main table that drives everything)
If i change it to be more Third Teir type of database, with just unique ID's
instaead of for example the claimant name/patient name/name of account
Want to know if this is what is bloating my db, so i can delete it, normally
i make a copy, delete it, then compact and look at the diff, but this is
alot of work ya know?
jsut a thought
W
Wayne Morgan
To see if a table is "static" I would include a date/time field and keep
track of when the last changes were.
track of when the last changes were.
T
Tim Ferguson
I wanted to know the size of a table so that I know if the table is
static enough to export it to the frontend.
This is a _logical design_ decision, not an implementation tweak. What is
the business cost of registering a patient with a non existent doctor, or
deleting an office where people are still employed?
Also, Backup tables, when I change some information i normally make a
backup of the table, for example
Already answered: it's simplest and safest just to copy the mdb file as a
whole. Trying to copy individual db objects is pointless because of the
loss of ref integrity, and asking for data corruption.
Table Intial (main table that drives everything)
Didn't understand this.
Want to know if this is what is bloating my db, so i can delete it,
normally i make a copy, delete it, then compact and look at the diff,
but this is alot of work ya know?
Again; as long as you are comfortably below the 2 GB limit, why care? And
if you are getting close to it, then perhaps you should be looking at a
real DBMS in any case.
B Wishes
Tim F