Loop

  • Thread starter Thread starter Vinny P
  • Start date Start date
V

Vinny P

I have a table with a Primary Acct and up to 20 subs. I want to compare
another table to this one. I want to do a one for one lookup for each acct
across all 21 fields for each record, but if a match is made stop and go to
the next account to compare.

Tbl1.AcctNum - find match on tbl2.Primary, sub1, sub2. If Match go to next
tbl1.AcctNum until all accounts have been compared.

Sorry for the confusion, I am having difficulty wording this properly.

Any help would be greatly appreciated.
 
I have a table with a Primary Acct and up to 20 subs. I want to compare
another table to this one. I want to do a one for one lookup for each acct
across all 21 fields for each record, but if a match is made stop and go to
the next account to compare.
Click to expand...

WHOA!!!!

You're "committing spreadsheet upon a database", a venial sin punishible by
being required to study Normalization.

"Fields are expensive, records are cheap". If you have a one (account) to many
(subs) relationship, you need *two tables* in a one to many relationship;
Accounts (primary key AcctNum), and SubAccounts (with AcctNum as a foreign key
linking to Accounts, and some other field as its primary key). If an account
has four subaccounts, just add four records to this related table; if it has
22, just add 22 records (or completely redesign all your tables, forms,
queries, reports, and so on if you stick with your current design).

Check some of the tutorials at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]
 
Thanks John. I apologize for my ignorance, but every now and again I need a
push! I can create the 2 tables and form a one to many relationship. That
makes perfect sense. Once I do that how do I get another table to
"cross-reference" and find a matching acct number?
 
Thanks John. I apologize for my ignorance, but every now and again I need a
push! I can create the 2 tables and form a one to many relationship. That
makes perfect sense. Once I do that how do I get another table to
"cross-reference" and find a matching acct number?
Click to expand...

That's what Queries are for.

Each Table should have a Primary Key - a field (or group of fields, up to ten
of them) which uniquely identifies each record.

Each related table should have a "foreign key" field - a field of the same
datatype as the primary table's Primary Key (use Long Integer if your primary
key is an Autonumber). You would use the "Relationships" window to define a
relationship between these two fields. You can then create a Query selecting
both tables, and including fields from both; if you've defined the
relationship Access will automatically join the two tables on these key
fields. You can also create a query and define your own join. You will then
have data from both tables combined.

Check out the tutorials posted in the previous message, and take a look at the
sample databases such as the Northwind database that comes with Access.

John W. Vinson [MVP]
 
Can you give an example of how your data looks once you've split it into the
two tables and what output you're looking for? I'm reasonably sure you'll
want to use a JOIN operation, but your wording makes it a little ambiguous
just what you're expecting out of this or what you're trying to actually do
once you've identified matches.


Rob
 
Back
Top