Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    6

    Unanswered: General Access Question

    I am a new programmer and I have been assigned to write an Access program. I need to select items out of a purchase table based on a request number entered into a filed in a Access Form. The table I need to access is on an SQL server and is not part of an Access Database. This database stores each item on the request as in seperate row. Meaning if there are 15 orders for the same item on the same request number, it stores it in 15 separate rows. When I pull this information into a form in Access, I want it to display the same items in one line with the quanity of 15, instead of displaying it in 15 different rows. I hope this made sense but before I can start development I need to know how I can handle this problem or if I will be able to handle it at all.

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    Re: General Access Question

    Originally posted by khill
    I am a new programmer and I have been assigned to write an Access program. I need to select items out of a purchase table based on a request number entered into a filed in a Access Form. The table I need to access is on an SQL server and is not part of an Access Database. This database stores each item on the request as in seperate row. Meaning if there are 15 orders for the same item on the same request number, it stores it in 15 separate rows. When I pull this information into a form in Access, I want it to display the same items in one line with the quanity of 15, instead of displaying it in 15 different rows. I hope this made sense but before I can start development I need to know how I can handle this problem or if I will be able to handle it at all.
    dont understant so clearly but i think this can only be done by VB codes. can you give me a specific example of data?
    qha_vn

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    main form has the ID and a subform built on a view of the data (15 records) the link between the two is based on the id
    subform then refreshes or requi\eries its data based on the current id on the main form (- the ONCurrent event of the main form

  4. #4
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Originally posted by axsprog
    main form has the ID and a subform built on a view of the data (15 records) the link between the two is based on the id
    subform then refreshes or requi\eries its data based on the current id on the main form (- the ONCurrent event of the main form
    well, what i understand is the subform will contains only one row that looks like: Item_ID, Item_Name, Total_quantity_ordered

    if thats true, the only thing you need to do is to adjust the subform query so that it counts the total number of records. (use GROUP BY on Item_ID, Item_Name, and COUNT on Item_ID)

    hope this helps
    qha_vn

  5. #5
    Join Date
    May 2003
    Posts
    6

    Thank you

    Thank you for your suggestion. I will give this a try. My line of thinking was more complex. I was thinking I would have to send the query results to a function to count the same items in the result. This sounds much easier. Thanx again.

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Re: General Access Question

    You could create a view in SQL, this would create a temporary table.
    That you could use for pulling the summarised data from.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •