Problem counting and storing records

[ande-s]

I have two tables in Access.

One table is of Projects, and the other table, is of components that belong to each project. The COMPONENTS table has a field Proj_ID which takes uses a drop down to the PROJECTS table as a lookup for the field.

I would like to write a query that will return the number of components for each Project but am struggling somewhat.

I have a query, which counts the number of records (Components) for each Project and displaed on it's own works fine.

The problem comes when I try to use the 'total' values in my DB.

Ideally, I would like a field in the Projects table that stores the number of components for that particular project, but no matter what I do it just doesn't seem to work.

Any suggestions?

 

[vbaInet]

If you can calculate it why save it?

http://allenbrowne.com/casu-14.html

 

[plog]

Yes, post your tables' structures, some sample data and what you would like the output of your totals query to look like.

 

[ande-s]

Yes, post your tables' structures, some sample data and what you would like the output of your totals query to look like.

OK here goes:

Table: Projects contains;

Project Name
Project description
Project Manager

Table: Components contains:

Comp ID
Comp Name
Comp Description
Project Name
Phase 1 Status (Finished, reviewed, Executing)
Phase 2 Status (Finished, reviewed, Executing)
Phase 3 Status (Finished, reviewed, Executing)

Components table is linked to the Projects table via the "Project Name" field

The queries I would like are:

The total number of components for each project as a number
The number of Phases that are (Finished, reviewed, Executing) for each component in each project.

Any ideas?

 

[vbaInet]

ande-s, both tables need a Project ID field. You link them up via this numeric field not via the Project Name field.

 

[ande-s]

The Project Name field is unique so is acting as the Project ID.

I have now managed to get the count values for the various fields using a Cross Tab query which is where I think I was going wrong. I now just need to calculate the % for each total which shouldn't be too hard

 

[vbaInet]

Project Name is not unique and is prone to human error. A numeric Project ID field is required. I would advise you read about Normalization.

 

[ande-s]

Ok, added a primary ID field to both tables and all is working well and as expected.

All I need now is to calculate the following:

In Projects Table I have two date fields.

"Start Date" and "End Date"

I need to count the days between them, and then work out how far through the project I am as a percentage as of the current date.

Is this possible?

 

[vbaInet]

Have a look into the DateDiff() and Date() functions.

 

[ande-s]

I have this working in a query now, and it is calculated when the query is run, and a field added at runtime, but the problem lies when I come to use that field in another form, Access doesn't recognise it as a valid feild.

 

[vbaInet]

I have this working in a query now, and it is calculated when the query is run, and a field added at runtime

How did you implement this?

 

[ande-s]

I use a query based on the Projects table. Using the "Proj_End" field and" Proj_Length"

The "Proj_Length" field is based on the difference of "Proj_Start" and "Proj_End"

In the query I have two fields I add;

Proj_Days_Expired: DateDiff("d",[Proj_Start],Now())
Proj_Completed: ([Proj_Days_Expired]/Proj_Length]*100)

 

[vbaInet]

The word "runtime" is used in the wrong context. I would advise you avoid using it when talking about queries as this causes more confusion.

...Access doesn't recognise it as a valid feild.

So how have you referenced this field?

 

[ande-s]

I added a text box onto a report, and tried to set the record source to that field in the query.

But the query is not recognised as a valid field

 

[vbaInet]

You mean Control Source of the textbox.

What did you set it as? Can I see the exact value.

 

[ande-s]

The control source is:

[qry_Project_Time]![Proj_Completed]

 

[vbaInet]

Did you select it from the drop down or did you type it in? I would suggest you select it from the drop down.

And what is the Record Source of your form?

 

[ande-s]

Ah I have it now, I was missing the field from the query that the form was using as a source.

The only problem I now have is that the number of decimal places is far too manu despite me setting it to 2 in the text box properties

 

[vbaInet]

Decimal Places property works with the Fixed Format property.

 

[ande-s]

Bingo, it's all slotted together nicely. Thanks for your help.

I just need to be able to fire off some reports via a macro and it will be complete