Results 1 to 11 of 11
  1. #1
    AmericaninCanada is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    2

    Newb- Manipulating single table data into Form, but multiple forms are made

    Hello,

    First of I am extremely new to Access so if you respond please try to speak plainly because Access is definitely not in my wheelhouse. And I apologize in advance if my terminology is off.

    I am creating a form from a single table which is simple enough, but for some reason when the form is created each row in the table seems to have it's own record.

    Since the data I am working with is sensitive I have attached a small example car data set and form to show about where I am at. Cars Example.zip As you see in the form there seems to be a form for each row of records. I just want one form for all the data that can be filtered. My final goal is to have something pretty similar to the attached excel file with the pivot table. So I am looking to filter off of Brands of cars and then have the vehicle number be the column header and all the information within that row to stack below it.



    Please help if you can. Thank you.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I cannot look at your zip, when you say it created a new form for each record, I think it is just showing the records in single form? In your object window you just have 1 form right?

    Basically you create a new form, in the Record Source property, select the table. Now you can add all the fields to the form. You can either show 1 record(all the fields) on the form and scroll through those by the navigation buttons at the bottom, or change the form to be a Continuous or datasheet display which will show all the records at once on the form. In the property sheet on the form, look for Default View.

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Change the form property "Default View" to Continuous. That will give you one line per record.

    Make this a subform and create a main form, add a combobox to select a car and link the subform to the main form on the ID.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe you should explain why you think you should use Access, which is a relational database tool and not a spreadsheet tool. One outshines the other - depending on the need. Same as a screwdriver excels over a hammer if you're driving screws. Access doesn't do charts well, and AFAIC, is not the tool for pivot table output. Database data is row based, whereas Excel is column based. As such, this requires you to forget any spreadsheet paradigm that you may have and learn normalization instead.

    I'll look at your file next, but in the meantime, maybe you should Google "is Access better than Excel" or something similar if you don't already have a good answer.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    AmericaninCanada is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    2
    Micron,

    I would love to excel because it does exactly what I want, but unfortunately when I suggested linking excel to the database because it would be easier to use my boss shut it down. So I am stuck with attempting to manipulate Access into what we need. Thanks.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If Vehicle Number is an number that ID's a vehicle in a fleet (as in it actually has that number on it) then fine. If it's a count of how many Chev's you have in your records, then NO. This would make it a calculation, which in 99% of the time, should not be stored. If this doesn't reflect your actual situation, then no problem. However, you should avoid special characters (save for possibly the underscore) and spaces in names. Maybe check this out - https://access-programmers.co.uk/for...d.php?t=225837

    Your form is based on a sql statement that references a field that the table doesn't have, but I suppose that's not an issue given that this isn't reflective of the real thing.
    "a form for each row of records" - you only have one form that displays all records in a 'single record' view. I suppose you want to change to a continuous or datasheet view as already mentioned. However, I'm a bigger fan of basing a form on a query and not a table since the query is more powerful. In fact, if more than one table is involved, it's almost requisite. If two or more tables will never be involved, you likely have a normalization issue.

    "Since the data I am working with is sensitive ..."
    Hopefully this doesn't impede getting the right answers. Too often I've seen posts where solutions are proposed based on examples given, only to find the solution doesn't work for the real situation. You can always replace sensitive info with bogus data if need be.
    " the attached excel file with the pivot table" - I see no pivot table, just a spreadsheet. Probably doesn't matter, but if you post here that you want a pivot table view of db data when what you really want is row/column records, you'll be off on the wrong path.
    Hope we've been able to help. Do consider whether or not Access is the right tool for the job at hand.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by AmericaninCanada View Post
    Micron,

    I would love to excel because it does exactly what I want, but unfortunately when I suggested linking excel to the database because it would be easier to use my boss shut it down. So I am stuck with attempting to manipulate Access into what we need. Thanks.
    Hopefully he knows enough to know why this is the right tool for the job.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron
    I see no pivot table, just a spreadsheet.
    Looks like the pivot table is the cyan area of the spreadsheet....

    but not really sure what the "pivot table" is supposed to report.


    @AmericaninCanada,
    maybe you would describe (like you are talking to a 10 year old) what you want to do/what is supposed to be reported. No computer jargon (if possible). You understand the spreadsheet/requirements - we don't.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Crap! Window opened to Restore size, not full. I didn't even see it. It transposes rows to columns, but other than that, it's not clear to me either since the data is limited. I suspect we both think that could be a whole lot of columns when the data comes from properly normalized Access tables.

    Sorry for the over-sight, AmericanInCanada.
    (Can't help but wonder if your forum handle is saying you're happy or home sick. )

  10. #10
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by AmericaninCanada View Post

    I am creating a form from a single table which is simple enough, but for some reason when the form is created each row in the table seems to have it's own record.
    No one has actually come out and said this, but the above is exactly how it works! Each row is a Record!

    As has been stated, to see all Records at once, in a Form, set its Default View to either Continuous or Datasheet View.

    BTW, you don't have 'a form for each row of records,' you simply have, as you've stated, a Record for each row of the Table, but only a single Form.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No one has actually come out and said this, but the above is exactly how it works! Each row is a Record!
    I figured Bulzie had that covered in post #2. Maybe that's why we all skipped over that part.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 02-02-2017, 10:31 AM
  2. Replies: 1
    Last Post: 06-24-2016, 02:34 PM
  3. Replies: 2
    Last Post: 12-15-2014, 04:53 AM
  4. Replies: 10
    Last Post: 08-09-2012, 01:07 PM
  5. Replies: 3
    Last Post: 11-16-2011, 11:53 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums