Help with VBA/SQL to loop through Table eliminating offsets

[EagleOne@microsoftdiscussiongroups]

2003


I have done this type of process in Excel but I am not sure of the best or
smartest way to do this in Access. (If Access would just permit the
recording of a VBA macro......)

Ok, I have i.e. three fields plus a money amount - so four fields in total.

I wish to sort (index) the file so that all Records the match the four
fields are next to one another. That part seems easy. What is the VBA code
to step through the file removing like items if their money amounts when
added equal to -0-?

Obviously, there is the issue of what happens if current record + 2, or +3
is the offset (especially if their is more that two records with A=A+1 B=B+1
C=C+1 and D-D+1 = 0?). I realize that this is like solving a simultaneous
equation. Help!

I have not done this before. Is their anyone who would be willing to share
this type of routine?

TIA EagleOne

 

[Arvin Meyer [MVP]]

I'd do it in the query. Create an alias column that adds the 4 columns,
like:

Expr1: [Field1]+[Field2]+[Field3]+[Field4]

In the criteria for that column use:

0

That's it!
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"EagleOne@microsoftdiscussiongroups"

 

[EagleOne]

Arvin,

I'll give it a shot. I already have a concatenated field upon which I compare two tables for
unmatched records between the two. Now I need to use that concept to delete offsetting entries
(records) in each Table before comparing the two?

My challenge is that I have offsetting records in each table (BTW, I have no control of what gets
into the main-frame files) which causes issues when I attempt matches (for offsets) among multiple
"solutions" with (I think) has the same index value.

Can I assume that the suggestion above will place all offsetting (or multiple similar records)
together? That is my concern, finding then deleting offsets with VBA. I know that I am in way over
my head!

In the suggestion, the query produces results. I think that human intervention would be required to
view those results and then manually delete? Sorry for being a newbie.

I had thought that I may have to use a Loop and compare next record to current record and A, B, C,
+- Amt; then delete the query results.

I guess that I may be making the task too complicated?

I'd do it in the query. Create an alias column that adds the 4 columns,
like:

Expr1: [Field1]+[Field2]+[Field3]+[Field4]

In the criteria for that column use:

0

That's it!

 

[Arvin Meyer [MVP]]

My challenge is that I have offsetting records in each table (BTW, I have
no control of what gets
into the main-frame files) which causes issues when I attempt matches (for
offsets) among multiple
"solutions" with (I think) has the same index value.

Can I assume that the suggestion above will place all offsetting (or
multiple similar records)
together? That is my concern, finding then deleting offsets with VBA. I
know that I am in way over
my head!

Records returned by the query will be sorted in the order that the Order By
clause is set in the query. The columns should be entered left to right in
the QBE grid.

 

[EagleOne]

Thanks Arvin