By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 3,102 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Selecting only X number of records in query

P: 5
Hello, I am having trouble with an SQL query which is used as a source on a report. The database is being used to keep track of billing information. I have made an sql query, which has 2 tables. table A contains customer information. table B contains all the bills for each customer. Table B has fields: customerID, Rate, and the Date. I have a field in the customer table (table A) which contains how many months that they still owe for. I need a way to print out only the last records (Date Descending) for which the customer still owes. Some customers may owe for 2 months, while others may owe for 6 months. Each month is kept as a new record in Table B. Example if a customer owe's for 4 months then I would like only the last 4 records in Table B for the customer to be selected (Date Descending). Any Help or ideas.
Feb 7 '07 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,072
Hmm, normally you should have e.g. a DatePayed or IndicatorPayed in the TableB.
Now you can simply create a report/subreport.
Create the report for the Customer and link the subreport by the CustomerID.
Use a query for the subreport that filters for a DatePayed being Null or an IndicatorPayed being False.

Getting the idea ?

Feb 7 '07 #2

P: 5
I thought that that might be the best approach, but the code is already in place for what it is now. I might end up doing it that way, but are there any other ideas?
Feb 7 '07 #3

Expert 2.5K+
P: 3,072
Having the number of rows in a field in a row won't be possible to use in a query.
(It's against normalization rules anyway)
The only option would be to use recordset processing in VBA code to issue a SELECT TOP # query with the datefield (or incremented ID) descending sorted.

Feb 8 '07 #4

Expert Mod 15k+
P: 31,307
Nico is right here.
The reason that SQL can't handle it is that any subquery runs only once. If you tried to build the subquery depending on a value found in the outer query (you can't anyway as there is no syntax for this), then it would, by definition have to run differently (changed design) for every row in the outer query. That's not how SQL engines work. The SQL needs to be optimised and 'prepared' before it even starts executing.
Feb 9 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.