form to just display a few counted totals? 1

[Junior1544]

I need to create a form that will just show some totals...

i know how to show the data in a query, last time i just made the query for each number my boss needed and told her the total records... how can I automate this into a form with unbound text box's??? all i need on it is the 6 totals... she's going to be running this pretty often... being that the data is housed in different tables, i would like there to be a prompt for the time frame the report is suposed to represent... i have no idea where to start...

i've looked at crosstab queries, but that just confuses me... i would be greatfull for any and all help... thanks

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Dreamwalker]

Give me some more informations

And I will try to help ...

your post confuses me more than anything

Thanks
Vince

 

[Junior1544]

sorry, but that's how i feel... i wish i had more knoldge, because my lack of it realy confuses me some times... i will simplify my problem as much as i can.

i have a table, and i need to get a total count of all records that have a range of dates in a certain field.

My boss wants to know how many records were added between certain dates. i already have fields in the table that automaticly get filled with the current date on entry, and well as another field that get's change with current date for any modifacations. and i will need to make another report that has the number of modifacations within the same time period. i also need similare counts from other tables...

let's just work with one count then i can probably modify every thing for the other tables, and fields i need to work with.

let's say the date field of when entered was [DateEnt]

i need to have a prompt of a starting date, and an end date. and the information i need back will just be the total number of records that the field [DateEnt] is between the dates entered...

i hope this is enough information... what i realy need to get done is the same thing but between 3 tables, and 7 fields... thanks for the help...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Dreamwalker]

ok

Now I understand a lot better

for only a table ...
you have to create a query that will select the date column from the desired table WHERE DateEnt Between(StartDate and EndDate)

That's for the query to select the desired dates ...

To get the record you will simply open a recordset

Dim rst as Recordset

Set rst = Currentdb.OpenRecordset("SELECT SQL QUERY&quot

rst.recordcount == YOUR number of records Selected with the SQL statement

For many tables and many columns it is also simple the only thing that changes is Columns and also the JOIN statements

Example
Select Table1.Column, Table2.Column From table1 INNER JOIN Table1.PrimaryKey = table2.ForeignKey WHERE Date Between ...

Hope this is usefull enough

Vince

IE : I know that the joins statements are a bit hard but once you will understand INNER , LEFT , RIGHT ... you'll be allright

 

[Junior1544]

ok, i'm confused... i don't have any joined tables, so why would i have a join?? i also don't see how the total number of records in that recordset is attained...

if you can give me some sample code i can modify, it would help me greatly...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Dreamwalker]

hmmm

i hope this is enough information... what i realy need to get done is the same thing but between 3 tables, and 7 fields... thanks for the help...

between 3 tables ... means you have data in 3 different tables then it requires a join ... if I understood you correctly ...

For the rst.recordcount ... it will return the number of record found with the sql statement wich means the number of records that are between the START date and the END date ... like I told you earlier ...

Vince

 

[Junior1544]

not exacly, the information i need to pull from different tables, the tables arn't related... i just need to pull a count from another table, a seperate operation...

and the other tables that i need to count off of will have a little different criterea... as well as the date...

i'm sorry this is so confusing...

--Junior
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

i've tried using rst.RecordCount and no matter how i do it, it just give me an answer of 1... i'm tring to assing the output to a text box on the form and i have the code running on the press of a command button...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

oh yea, and i'm tring to just get one to work, once i get one to work, i can make the other things i need to work done...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Dreamwalker]

Allright

for other tables non related
then just do a Dcount

Function Dcount("[SearchedColumn]","

","[WhereCondition]=&quot

The where should be the date between like said earlier ...

Vince

IE blah = Dcount .... will put the results in the var

 

[Junior1544]

there we go, that's what i think i need... i'll check into it this after noon...
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

here's a question with the dcount... in stead of a table, can i use a query there?? JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Dreamwalker]

It can be a TABLe or an Access Query Name

Vince