Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    5

    Unanswered: building query...

    i have a prolem with a query, i have attached the sample database.
    the problem is as following;

    i have a table including the sales information of 3 business areas, A, B and C.
    Each business area has 4 products (50000, 60000, 70000, 80000 codes) in sale with their sales throughout the months Jan, Feb, MAr, Apr and MAy.

    how can i build a query to produce the sales value, for instance, for the product "50000" of Business A in March?

    i have attached a sample. i hope anyone can help me in this.

    thank you in advance.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Are the products a single product or a series?
    Ryan
    My Blog

  3. #3
    Join Date
    Jul 2007
    Posts
    5

    products are not series. they are simple, independent products from each other

    products are not series. they are simple, independent products from each other.

    you can easily see from the table in the sample database attached.

  4. #4
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    SELECT Sales.Code, Sales.Mar
    FROM Sales
    WHERE (((Sales.Business)="A") AND ((Sales.Code)=50000));
    Ryan
    My Blog

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I re-designed your table in a more streamlined manner. Months of sales can be managed differently than making 12 columns out of them. Besides, there was no room for a "year" in the old table.

    I gave you a form (frmSelector) to select the query inputs, but I deliberately didn't program the SQL. 99% of the fun of programming is in the experimentation, so I'll give you the honor. I designed the list boxes so you can choose multiple businesses, multiple months, multiple codes, and multiple years, so make sure you take advantage of that feature in your SQL.

    Enjoy,

    Sam
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2007
    Posts
    5

    thank you, but...

    thank you Sam for the fantastic file you sent.
    i had also considered re-organising the file with the same way you did, but there is a problem with that. i will link those files from excel and the original formats of the excel files cannot be re-organised in this way. besides, it has to be updated as the excel file updated, so it is going to be a linked table.

    do you think there is another way of doing what you have done without changing this form of the table?

    thank you.

  7. #7
    Join Date
    Jul 2007
    Posts
    5

    rguy 84, thank you, but...

    rguy84

    thank you for the sql statement you have sent, but i understand that the query build on the coloumn "Mar". However, in our example, the month coloumns are also going to be one of the parameters to be selected together with "product code" and the "business area".

    so we need to add months coloumn as one more paramenter.. but how?

    thank you.

  8. #8
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    You then have to either redo your structure. Or have a table of months that are somehow linked to the fields on your main table. Which would be ugly, IF it can be done at all
    Ryan
    My Blog

  9. #9
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by enthusiastic99
    thank you Sam for the fantastic file you sent.
    i had also considered re-organising the file with the same way you did, but there is a problem with that. i will link those files from excel and the original formats of the excel files cannot be re-organised in this way. besides, it has to be updated as the excel file updated, so it is going to be a linked table.

    do you think there is another way of doing what you have done without changing this form of the table?

    thank you.
    Not that I know of. There is a way of building the SQL in a loop... Let me think about it overnight, if you have time for that, and I'll get back to it tomorrow. I'm outta here.

    Good Night!
    Sam

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Having duly thought about it overnight, in the light of day (it's actually quite dark outside, with thunderstorms, but who's keeping track!) I think that you should consider using a different approach. I would use the re-designed table as the main table.

    To explain. What you uploaded wasn't, strictly speaking, a database at all; it's a spreadsheet in database clothing. What I returned was a pure database. The differences are manifold, and not what I want to address now. There's no reason you can't use a linked file, if you wish, to start off with - read, when you open the application - but for actual work I would use the new table. I would provide queries to modify the one into the other. However, using the linked file in its present form is extremely restricting.

    In other words, I would link to the spreadsheet, build a SQL string and run it as an append query into the Access table as redesigned, change columns (for different months), and rebuild a SQL string. When closing the application, I would do the reverse, in case there were changes to the data. It's not difficult to do, although admittedly time consuming, and if you want the versatility of a true database, that's my way of skinning that cat.

    After all, what's a database worth if you can't query it easily?

    Sam

Posting Permissions

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