Problem with subforms...Can this be done

[munchkin]

I have a form I got at work. that I invoice my company. Scan the form. Put
it in a report and put the fields over it and print. Thats the Goal!

Okay the problem. I have 17 customers I can invoice at once and 16 pay
codes that are in colums with the customers going down the left side of the
page and the 16 pay coded spanning accross the top. I know you can't have
over 255 fields in a table. I first tried to put the customer field in then
the 16 codes in one record. Worked however couldnt format it correctly using
sorting and grouping in the report. So I then tried to split into two
tables. customer 1 through 8 on one table and customer 9 through 16 on the
other. Then had a form with 1 through 8 and a subform 9 through 16.
CustomerID being the primary key on the main form (autonumber) and customerid
on the second form being a (number). when I created the subform it linked
child and master to customerID. However when I went back to look at the form
it didnt syncronzie with the main. however, it did show the other 9-17
customer records in the table. Question: Can this be accomplished and if
so, how? or did I miss something on the "on format command" when trying to
do it the first way i mentioned. Thanks.

 

[John Vinson]

I have a form I got at work. that I invoice my company. Scan the form. Put
it in a report and put the fields over it and print. Thats the Goal!

Okay the problem. I have 17 customers I can invoice at once and 16 pay
codes that are in colums with the customers going down the left side of the
page and the 16 pay coded spanning accross the top. I know you can't have
over 255 fields in a table. I first tried to put the customer field in then
the 16 codes in one record. Worked however couldnt format it correctly using
sorting and grouping in the report. So I then tried to split into two
tables. customer 1 through 8 on one table and customer 9 through 16 on the
other. Then had a form with 1 through 8 and a subform 9 through 16.
CustomerID being the primary key on the main form (autonumber) and customerid
on the second form being a (number). when I created the subform it linked
child and master to customerID. However when I went back to look at the form
it didnt syncronzie with the main. however, it did show the other 9-17
customer records in the table. Question: Can this be accomplished and if
so, how? or did I miss something on the "on format command" when trying to
do it the first way i mentioned. Thanks.

Sorry... but this has absolutely nothing whatsoever to do with
subforms. It has to do with Reports... right?

A table structure with three fields - CustomerID, PayCode, and Amount
- should work just fine. Base your Report on a Crosstab query, using
CustomerID as the Row Header and PayCode as the Column Header.

You may be assuming that your table structure must match your report
appearance. Nothing could be further from the truth!

John W. Vinson[MVP]

 

[munchkin]

Yea it does have to do with forms, subforms. Yea the final result would be
the company form that I scanned in. If I could get the form customer 1
through 8 synchronized with customer 9 through 17 on the subform I shouldn't
have a problem with the report. Why I need the forms is because data
(numbers) have to be entered into each pay code for each customer. (max of
20) doing the math It comes out to 340 fields. Since Access tables are
limited to 255 that caused me to split into 2 tables. [invoice 1-8],[invoice
9-17] being the customer ID, Primary Key on the [invoice 1-8] table and
customer ID, autonumber on the [invoice 9-17] on the 2nd table. Once I had
the form and subform synchronized I was scanning the document as a picture,
then putting the fields over the document so when it was printed, the form
and the the data would be togeather. Your correct in one aspect that I only
needed to put in a table the customer name and 16 pay codes, the sort and
group and print the report being only 17 total customers. However, after
scanning the company document I couldnt line up the customers on the 17
blocks on the customer form to make it happen. It would however group the 17
customers on the report without scanning the company blank form. I hope I
clarified my question more clearly...thanks for any assistance or suggestions
that can be offered. However I know I can just take a bunch of blank forms
and feed the blank forms through the printer with the 2nd method I mentioned
but would rather print the report in whole.

 

[BruceM]

In Access a form is used for entering and working with data. While forms
can be printed, reports are the preferred method since they offer more
flexibility, and allow for sorting and grouping much more easily than do
forms.
To paraphrase something John Vinson has written in the past (with apologies
to John if I have mangled this), each table should contain data about a
single type of entity. I like to express the same idea by saying that a
table's function should be describable by a single sentence without using
"and". Customers should be together in one table, whatever else is in the
table. Pay codes should most likely be in another. If you are splitting
customers into two tables because of the 255 field limitation, you are
headed down the wrong path. It sounds like you have several customers in a
single record (table row). A customer table would typically contain
something like CustomerID (which would probably be the primary key field),
CustomerName, Street, City, State, Zip, Phone, Fax, and other information
specific to that customer. You could put pay codes into that same record, I
suppose, but you would still use another record (row) for the next customer.
You could also put pay codes into a separate table, related to the Customer
table by CustomerID. In this case you could build a form/subform (or
report/subreport), but in the first case you could just select and arrange
the fields in any way you choose. If you use Continuous as the form's
default view you can place the records one above the other on the screen.
In a report you would probably sort the records by CustomerName or whatever
you choose, but not group them. Again, they would appear one above the
other on the page.

Yea it does have to do with forms, subforms. Yea the final result would
be
the company form that I scanned in. If I could get the form customer 1
through 8 synchronized with customer 9 through 17 on the subform I
shouldn't
have a problem with the report. Why I need the forms is because data
(numbers) have to be entered into each pay code for each customer. (max of
20) doing the math It comes out to 340 fields. Since Access tables are
limited to 255 that caused me to split into 2 tables. [invoice
1-8],[invoice
9-17] being the customer ID, Primary Key on the [invoice 1-8] table and
customer ID, autonumber on the [invoice 9-17] on the 2nd table. Once I
had
the form and subform synchronized I was scanning the document as a
picture,
then putting the fields over the document so when it was printed, the form
and the the data would be togeather. Your correct in one aspect that I
only
needed to put in a table the customer name and 16 pay codes, the sort and
group and print the report being only 17 total customers. However, after
scanning the company document I couldnt line up the customers on the 17
blocks on the customer form to make it happen. It would however group the
17
customers on the report without scanning the company blank form. I hope I
clarified my question more clearly...thanks for any assistance or
suggestions
that can be offered. However I know I can just take a bunch of blank
forms
and feed the blank forms through the printer with the 2nd method I
mentioned
but would rather print the report in whole.

Sorry... but this has absolutely nothing whatsoever to do with
subforms. It has to do with Reports... right?

A table structure with three fields - CustomerID, PayCode, and Amount
- should work just fine. Base your Report on a Crosstab query, using
CustomerID as the Row Header and PayCode as the Column Header.

You may be assuming that your table structure must match your report
appearance. Nothing could be further from the truth!

John W. Vinson[MVP]

 

[John Vinson]

Yea it does have to do with forms, subforms. Yea the final result would be
the company form that I scanned in. If I could get the form customer 1
through 8 synchronized with customer 9 through 17 on the subform I shouldn't
have a problem with the report. Why I need the forms is because data
(numbers) have to be entered into each pay code for each customer. (max of
20) doing the math It comes out to 340 fields.

No. It doesn't.

It comes out to 340 * R E C O R D S * in a table.

Read Bruce's excellent reply in this thread; and consider that data
*storage*, data *presentation*, and data *entry* are three different
tasks with different requirements. You're letting this paper form
drive your table design. That is - as you have seen - a certain,
guaranteed way to get a badly flawed database structure.

Step back and come up with a logical way to *STORE* the data in
tables; arranging that data on a screen or on a sheet of paper is a
different task, which will become much easier once you have a proper
table structure.

John W. Vinson[MVP]