Trying to create a count query

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top