Union Fields?

K

Ken Snell \(MVP\)

We'll need an example from you that shows us what you mean by "union fields
within one table". That expression doesn't mean anything specific to me....
 
R

Renetta

well, here's the thing. Very new to Access and needed to create a form. I
couldn't figure out how the sub form/auto form (one to many) worked for muti
data assoicated with one client. So, I created several fields within one
table with like info to accomodate my needs. Now, as you have prob. figured
out I am in truble? I like to dump all the data in the like fields and link
to my client list as it should of been.

Detail:

FIELD 1 FIELD 2 FIELD 3 FIELD 4 FIELD 5 (NOTE: I copied all 5 fields in
my form 10 times instead of creating an auto form)

Ya, I'm a dork! Thougt it would be a quick fix and would fix it latter.
Now 2 years of data is built up and I'm now wanting to correct. Any
suggestions.
 
R

Renetta

actually, it more like this:

field 1 = name field 2 = account # field 3 = date = field 4 = amount
field 5 = name field 6 = account # field 6 = date = field 7 = amount
and so on and so on (10 rolls of like info in seperate fields)

It was my first attempt!
 
K

Ken Snell \(MVP\)

It definitely sounds as if you need a continuous forms view of either the
main form or of a subform within a main form. You don't tell us much about
the design, but let me assume that you want to select a client ID in the
main form, and then have the subform display the data records associated to
that client ID.

If this is correct, first, you will want to redesign the fields in the table
so that you don't have repetitive fields.

Then create a form (this will be the main form), put a combo box in the Form
Header section of this main form and have it allow the selection of a client
(make sure that the client ID field is the first field in the combo box's
Row Source query, and that the combo box's Bound Column value is 1). Let's
assume you name the combo box cboClientID.

Now, create a form that will show you all the records from the table that
has the "client-related" records. Be sure that the client ID field in that
table is on the form. Let's call this form sfrmClientData. Set its Default
View property to Continous Forms. Save it and close it.

Now, put a subform control on that main form in the Detail section. Set the
Source Object of this subform control to the sfrmClientData form. Set the
ChildLinkFields property of the subform control to the name of the field in
the subform that holds the client ID value. Set the MasterLinkFields
property of the subform control to the combo box name -- cboClientID.

Now, when you select a client in the combo box, the subform will show the
related records.

--

Ken Snell
<MS ACCESS MVP>
 
J

John W. Vinson

actually, it more like this:

field 1 = name field 2 = account # field 3 = date = field 4 = amount
field 5 = name field 6 = account # field 6 = date = field 7 = amount
and so on and so on (10 rolls of like info in seperate fields)

It was my first attempt!
Click to expand...

You can unravel this "spreadsheet" into - I'm guessing - two tables, using
what's called a "Normalizing Union Query". First create your properly
normalized tables: one for Accounts, with Account # as the primary key; and
one for transactions, with Account # as a foreign key (same datatype but *not*
the primary key. I presume the name goes with the account? Not sure what you
want to do if you have Account 341 linked in one place with Joe Doakes, and in
another place with Mary Smith!

That said: create two tables:

Accounts
AccountNo <don't use # in fieldnames>
LastName
FirstName
<other data about the account or customer>

Transactions
TransactionID <Autonumber Primary Key>
AccountNo <same datatype as Accounts.AccountNo>
TransDate <don't use Date as a fieldname, it's a reserved word>
Amount <currency datatype>

Then you'll need two UNION queries: you'll need to build these in the SQL
window, the query grid isn't capable of doing this. Using your fieldnames
above (you'll need to edit them):

SELECT yourtable.Field2 AS Account, Yourtable.Field1 AS LastName
FROM yourtable
WHERE yourtable.Field2 IS NOT NULL
UNION
SELECT yourtable.Field6 AS Account, Yourtable.Field5 AS LastName
FROM yourtable
WHERE yourtable.Field6 IS NOT NULL
UNION
SELECT yourtable.Field10 AS Account, Yourtable.Field9 AS LastName
FROM yourtable
WHERE yourtable.Field10 IS NOT NULL
UNION
SELECT yourtable.Field14 AS Account, Yourtable.Field13 AS LastName
FROM yourtable
WHERE yourtable.Field14 IS NOT NULL
<etc etc through all ten sets of name/account fields>

Save this as uniAllAccounts. It will automatically strip out duplicates
(though if you have 341... Mary Smith and 341... Joe Doakes they will both be
there).

Now do the same thing using

SELECT yourtable.Field2 AS Account, yourtable.Field3 AS TransDate,
yourtable.Field4 AS Amount
FROM yourtable
WHERE yourtable.Field4 IS NOT NULL
UNION ALL
SELECT yourtable.Field6 AS Account, yourtable.Field7 AS TransDate,
yourtable.Field8 AS Amount
FROM yourtable
WHERE yourtable.Field8 IS NOT NULL
UNION ALL
<etc for ten repeats>

The UNION ALL will include duplicates - assuming that you might have two
transactions for the same account on the same date for the same amount; if
that can never happen in real life, use UNION instead of UNION ALL. Save this
query as uniAllTransactions.

Finally, create an Append query appending uniAllAccounts to Accounts, and
uniAllTransactions to Transactions. Run them both.

You should now be able to create a form based on Accounts, with a subform
based on Transactions. Once you're comfortable that you have all the data,
delete your "spreadsheet" table and use the new form for data entry and
editing.

John W. Vinson [MVP]
 
Top