Count query

[ms_access_00]

I am trying to write a query to count the number of entries across 4 different fields for each table record. For example:

Project Code------Date1----Date2----Date3----Date4
RS1234-----------2Jun-----8Jun------9Jul------19Jul
DF1345-----------7Apr-----9May--------------------
BB3434-----------1Apr------------------------------

The query should return 4 for project RS1234, 2 for DF1345, and 1 for BB3434.

I would be looking to produce an update query to populate an appropriate field in the table.

Any ideas? The count function does'nt seem to work for me.

 

[YNWA]

IIs the table set up right?

I usually dont store dates as field 1, 2, 3 etc.. rather have a project table then assign dates through a relationship of 1 to many.

As you are in effect counting across a row rather than down a column.

Or maybe you can do a count in a field at after Date4.??

More experienced guys will verify this

 

[ms_access_00]

Yes it is set up correctly.

I am looking to count across a row, that is correct. Once it counts across certain feilds in a row it then populates another fields with the count number on the same row.

 

[YNWA]

Yes it is set up correctly.

I am looking to count across a row, that is correct. Once it counts across certain feilds in a row it then populates another fields with the count number on the same row.

If this was Excel you'd simply put a field at the end and Count(A1:E1) for example.

Not sure. I am pretty certain your table set up is wrong.

You should have Product and dates in different tables.

 

[mahenkj2]

In any case you decide to do so, try this:

Me.YourTextBox = CurrentDb.TableDefs("Your table Name").Fields.Count

Use some event of a Textbox and put above code. It will count total columns of the table and then try deducting number of not to be included columns.

Else,

You should uses dates in rows, so they will be treated as records and Project ID should go in column.

Edit: I am sorry, I have mistaken this. The above code shall only count total number of columns in table, you need filled columns.

 

[ms_access_00]

I was hoping to do the query itself in a MS Access query using the Query Design.

Is the above VBA code?

 

[YNWA]

I was hoping to do the query itself in a MS Access query using the Query Design.

Is the above VBA code?

Your table set up is wrong mate.

You need to rethink that first.

 

[MSAccessRookie]

Your table set up is wrong mate.

You need to rethink that first.

YNWA could be correct in saying that your DataSet is set up incorrectly.

• If the DataSet represents an Excel Spreadsheet, then the setup would be acceptable.
• If the DataSet represents an Access Table, then the setup would not be acceptable.

In the case of an Access Table, a more appropriate setup would include a Table that allows multiple Dates for each Project Code.

tblProjects:
ProjectID (PK)
.
Other Project Related Fields (not including Dates)
.

tblProjectDate
ProjectDateID (PK)
ProjectID (FK From tblProjects)
ProjectDate

You would then select all Projects from tblProjects and Link them with Project Dates from tblProjectDates. If you need the Spreadsheet Format, then a report that Formats the data can be created.

 

[Kasher]

nothing wrong with your table design. here is the solution;

in query grid create a field

total:count([Date1])+count([Date2])+count([Date3])+count([Date4])

and in group by field select "Expression"

-----------------------------------------------------
if there is a will, there is a way...

 

[MSAccessRookie]

nothing wrong with your table design. here is the solution;

in query grid create a field

total:count([Date1])+count([Date2])+count([Date3])+count([Date4])

and in group by field select "Expression"

-----------------------------------------------------
if there is a will, there is a way...

I have to admit that your solution will fulfill the needs of the OP at this time.

It does not, however, allow any room for growth. For instance, what happens down the road when the need come up to have Date5 and Date6 (or perhaps even more dates)? This solution will require additional coding of any VBA Code or Query that uses the Table. A properly formatted database could grow as the need grows. Sometimes the easiest solution may not be the best one.

-- Rookie