Trying to create a count query

[Mr930]

I have 2 tables, one-to-many. The main table are properties, each unique, identified by a PROPNUM, which is the link to the many table. The many table has monthly numbers for each property, no duplicates on dates within a property.

What I want to do is to be able to create a query that shows the first X (specified) monthly records for each property, the kicker being that the first month for each property is a different date than another property.

I want the result set to be the X first records for each property, without showing the other months as zeroes, I would prefer they not show up at all.

I have done this in report software, where I set up a running total (which is a counter), but I do not know how to do this in Access.

Any help is appreciated!
Fred Emmerich

 

[Joe4]

Access is not really good at doing counter or running sums - that's really not what it was designed for. In a relational database, their is supposed to be independence between the records in a table.
I have seen stuff like that done in Reports, and there is a way to do it with Queries, but it can get a little flaky.

However, I would like to see what you are trying to do actually looks like (I am a very visual person). Can you post some small samples of your data and what your expected results should look like?

 

[Mr930]

I am not averse to a VBA solution either... the data is simple, but the query I want to set up is used by a graphics program to display (by property) the monthly numbers. I want to be able to exclude trailing numbers so they do not show up in the graphs (there is no way to do this in the graph itself), so I can do an analysis of the numbers without being biased by the subsequent numbers. The third party graphics program plots the data from the 2 tables mentioned, but since a query is equivalent to a table, I can point the graphics program to the query instead.

 

[Joe4]

OK, you really lost me in your last post.
I am afraid that without seeing any examples, your description does not make very much sense to me.
All I was looking for was a small sample of your two tables and what you want your expected query to look like (based on that sample).

 

[xenou]

a Top (N) query might work (you may need to first write a subquery that sorts/order by's so that the right records are at the top (not sure - SQL Server will take the ordered results without a subquery but I don't know how MSAccess behaves).