Controlling how data is manipulated

[deeconsult]

I want to manage data in one table that has several fields with the same
information. Example: Fields with 1 didgit differences "Qty" and "Qty2"
I've learned how to do sub-forms and I want to do a "loop" through the old
table and copy all the "Qty2" to the "Qty" field of the new table that will
be used for the sub-form.
I want to TELL access to: WHILE InvoiceNo=InvoiceNo
REPLACE QTY with QTY2>0 .AND. REPLACE
DateofService with DateofService2 (etc)

Then I want it to skip to the next record and do the same all over with Qty3.
I hope this makes sense. I just need help I can understand.
Please NOTE...... I'M A SELF TAUGHT NOVICE W/ACCESS

 

[John Vinson]

I want to manage data in one table that has several fields with the same
information.

Ummm... No. You probably need to redesign your table so that you don't
have multiple fields with redundant information.

You're using a relational database. Use it relationally!

Example: Fields with 1 didgit differences "Qty" and "Qty2"

If you have fields named Qty and Qty2 and Qty3 - YOUR TABLE STRUCTURE
IS WRONG.

You have a one (invoice?) to many quantities relationship. The correct
structure for this is to have TWO TABLES in a one-to-many
relationship. "Fields are expensive, records are cheap" - if an
invoice involves multiple quantities, you should add *a new record* to
the related table for each quantity.

I've learned how to do sub-forms and I want to do a "loop" through the old
table and copy all the "Qty2" to the "Qty" field of the new table that will
be used for the sub-form.
I want to TELL access to: WHILE InvoiceNo=InvoiceNo
REPLACE QTY with QTY2>0 .AND. REPLACE
DateofService with DateofService2 (etc)

This would not be necessary with a properly structured set of tables.
If you're bound and determined to use a deeply flawed, non-database
spreadsheet structure, then I'll hold my nose and tell you to look at
Update Query in the online help. Looping and Replace and While are
programming concepts that do not apply to Queries.

Then I want it to skip to the next record and do the same all over with Qty3.
I hope this makes sense. I just need help I can understand.
Please NOTE...... I'M A SELF TAUGHT NOVICE W/ACCESS

You may want to stop, unlearn some of what you've taught yourself, and
get a good book on Access, one which covers relational database
design. You're painting yourself into a corner and making your job
MUCH more difficult!

John W. Vinson[MVP]

 

[Duane Hookom]

It seems that you are attempting to normalize your table structure. Since
you are possibly not real comfortable with code, I suggest you create a
union query like:

SELECT InvoiceNo, Qty1 as Qty, DateOfService1 as DateOfService
From tblOldTable
UNION ALL
SELECT InvoiceNo, Qty2, DateOfService2
From tblOldTable
WHERE Qty2 >0

UNION ALL
SELECT InvoiceNo, Qty3, DateOfService3
From tblOldTable
WHERE Qty3 >0;

You can then use this query as the source for an append query.