Pull Previous Record

[spatzengr]

Hello all, I have one for ya

I have a water usage billing Access database. This database bills for water usage for 50 homes.

It basically contains Two (2) tables. The first table is for bill information and is indexed by Invoice Numbers (autonumber format). The second table is for property infomation (name, address, zip...ect.) and is indexed by PropertyID Numbers (autonumber format).

A Form is used to create a new invoice. The user is prompted to enter a PropertyID Number for each new invoice created. Once entered, the Invoice Info. and Property Info. are cross-referenced, through a Query.

ANYWAYS, what I am looking for is a way to have the Form pull-up the previous invoice that is associated with that property when creating a new invoice. The invoice it needs to pull-up will be exactly 50 invoices (rows) ahead of the current invoice being created because there are 50 properties. I know there is code to due this with a query (which I could a refresher on) but I need it to be bullet proof. (IE: when a new property/home is added to the database it needs to automatically work without having to update the Query or VB code.)

Any help would be greatly appreciated. Please let me know if you have any question or comments, I can offer pic's, code, or more explanation.

 

[SydneyGeek]

You can do it with a set of queries that build on each other.
1. First query -- Invoice # from the Invoices table, filter by current Customer #. Sort Descending, filter to show Top 2

2. Second query -- based on the first. Invoice #, click the Totals button (sigma on the Toolbar), select Min in the Totals row. Now you have the previous Invoice # for this Customer.

3. Third query -- Second query and Invoices table, joined on Invoice #. That shoudl give you all the fields for the relevant query. Grab what you need and place it on the report.

Denis

 

[spatzengr]

I don't think that technique will work. I would need to make three (3) queries for each property ID (150 queries) and in addition when a new property is added by the user it would require me to add three (3) new queries to the Access file.

I need a running solution that will handle itself as the Access file expands (more properties and invoices are added).

 

[SydneyGeek]

Sounds like you need to re-think the data structure, or base the queries that I mentioned on Property. Do you have one Invoice # across 50 Properties?
My guess is that you need a join table -- you won't get a sensible many-to-many join directly in Access or any other database. [If one Invoice related to many Properties, and one Property cna have many Invoices, you need a join table to resolve all the possibilities].
So... Invoices has Invoice # as key
Properties has Property ID as key
Join table has both Invoice # and Property ID as Number fields. Relate the join table to the other two, and set up your data entry / reporting to operate off this table.

Denis

 

[spatzengr]

I remember at some point there was a Query code that I found that would look back say 12,24, or 163 records by changeing a number in the code. Say you wanted to look back 163 records from the current you would type 163 in the query code and it would display that record.

You by chance know that code?

 

[bat17]

Denis method should work assuming that you have the property ID stored in the invoice. You don't need to set up individual queris for each criteria but you pull the criteria from your form.
If you add a cumbo-box to your form you could use that to list all the properties and that would then act as your criteria.

When using Autonumbers in Access it is possible to lose numbers in the sequence, if someone cancels a record or the PC crashes while a record is being created, so it is not a reliable method to pick up a record number 50 previously from.

Peter

 

[spatzengr]

If you add a cumbo-box to your form you could use that to list all the properties and that would then act as your criteria.

I see what you are saying bat17, and yes the property ID and Invoce numbers are in the same table. These leads to another question.

How do you pull a filter criteria from a user form?

 

[SydneyGeek]

I see what you are saying bat17, and yes the property ID and Invoce numbers are in the same table. These leads to another question.

How do you pull a filter criteria from a user form?

In Design view in the query, go to the Criteria row of the field that you want to filter on. Type
[Forms]![MyFormName]![MyControlName] -- adjust the form and control names to suit.

Note:
The form needs to be open when the query is run, or you will get a parameter prompt. To do this, go to Design view in the form. Drag a command Button onto the form, with wizards ON -- you should see a wizard launch after a few seconds. Follow the prompts to open a query (or better still, the Report that is based on teh query) and give the button a caption.

Denis