Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Problem with subforms...Can this be done

Discussion in 'Information Technology' started by munchkin, Oct 27, 2005.

  1. munchkin

    munchkin
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 26 Oct 2005 21:43:02 -0700, "munchkin"
    <munchkin@discussions.microsoft.com> wrote:

    >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]
     
  4. munchkin

    munchkin
    Expand Collapse
    Guest

    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.



    "John Vinson" wrote:

    > On Wed, 26 Oct 2005 21:43:02 -0700, "munchkin"
    > <munchkin@discussions.microsoft.com> wrote:
    >
    > >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]
    >
     
  5. BruceM

    BruceM
    Expand Collapse
    Guest

    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.

    "munchkin" <munchkin@discussions.microsoft.com> wrote in message
    news:37AF42B6-5B5F-40F8-8B50-2C59C57C61E0@microsoft.com...
    > 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.
    >
    >
    >
    > "John Vinson" wrote:
    >
    >> On Wed, 26 Oct 2005 21:43:02 -0700, "munchkin"
    >> <munchkin@discussions.microsoft.com> wrote:
    >>
    >> >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]
    >>
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 27 Oct 2005 02:24:05 -0700, "munchkin"
    <munchkin@discussions.microsoft.com> wrote:

    >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]
     

Share This Page