Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2006
    Posts
    6

    Unanswered: Database design help

    hey all,
    I'm rappidly running out of ideas as to help with this one,
    Basically I have 2 excel documents that contain data that needs to be shipped off external sources. What I'm doing at the moment is using PDF wizzard to generate PDF's of the specific parts of the excel documents I want to send and then emailing them out.

    What I would like to do is develop something, either in access, filemaker or whatever that I can click and import the excel document into have it split up the relevant data and then email the result out to the people who need specific data.

    Anyone have any ideas?

    Cheers

    Mathew

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You would have to:

    1. Design a set of database tables based on the excel spreadsheets called the operational data store
    2. Import the excel spreadsheets into two large tables in Access called the source data
    3. Extract the source data using queries into the operational data store
    4. Query the operational data store to generate the reports you need to send out as PDFs.

  3. #3
    Join Date
    Oct 2006
    Posts
    6

    hummm

    ok,
    based on that I dont think Access is going to be powerful enought,

    Im trying to make the system idiot and stupid proof and it seems that what you have described would complicate someone.

    I'm looking into something like Orcale at the moment, so that could be interesting.

    Mat

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would just write an Excel macro, but I'm lazy.

    -PatP

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Oracle would probably be a little heavy handed

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would say Certus's method is pretty well a standard answer to this sort of thing (plus the odd fancy phrase like operational data store - was that some added value?). I can't see how doing this is oracle rather than access will make this simple and idiot proof unless you are much more familair with Oracle.

    In any event, the problem is the same irrespective of the tool (unless you are Pat).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2006
    Posts
    6

    Scrap that Idea, I wrote it in VB instead

    Actually I gave up with both Access and Oracle.

    I decided to write the whole thing in VB inside excel.

    I am however a bit stuck on the logic now.

    I have the program open the selected excel document, filter down to the required data parts but this is where I get stuck.

    I need the program to use a filter to filter and select data concerning a company, copy the data to a new sheet, save the sheet then go back at repeat all that again for the next company.

    The bit I cant get to do is the selection of a company, (I can get it to select the 1st company and copy etc), but how do I then go back and select the next company and the next and the next and so forth?

    Any ideas?

    Cheers

    Mat.

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Have you tried recording what you need with the macro recorder.
    If you know how to do things manually then the recorder is a good start to getting the correct piece of code you need. Just remember to tidy up the code that comes out of it as its usually a bit of a mess although it will be correct.

  9. #9
    Join Date
    Feb 2006
    Posts
    113
    Mat,

    One way is to use an advanced filter on the company field, selecting unique entries only, and copy that to a new location (maybe a spare or temporary worksheet).

    Then (for speed) save that list to an array and loop through the array doing what you need to do for each company name; or, leave the newly filtered list of unique names in a worksheet range and loop through those cells.

    OK?
    Fazza

  10. #10
    Join Date
    Oct 2006
    Posts
    6
    Quote Originally Posted by Fazza
    Mat,

    One way is to use an advanced filter on the company field, selecting unique entries only, and copy that to a new location (maybe a spare or temporary worksheet).

    Then (for speed) save that list to an array and loop through the array doing what you need to do for each company name; or, leave the newly filtered list of unique names in a worksheet range and loop through those cells.

    OK?
    Fazza

    That sounds a bit too complex for what I want,

    Is their not a simple command that I can write into a loop that selects a specific data set from a field (a company name for example), then when the loop is completeted it goes back and selects the next company from the list and starts again?

    Cheers

    Mat.

  11. #11
    Join Date
    Feb 2006
    Posts
    113
    Hi Mat,

    What about something like
    Code:
    dim cel as range
    for each cel in wks_Company_Names.Range("Companies")
       'do whatever with cel.value, the company name
    next cel
    Or in an array, loop through from the LBound to the UBound.

    The hurdle is setting up the list of unique names. Excel has built-in functionality do that - the advanced filter.

    Another way is a data table query on the "Company" field. Such as to a separate worksheet and give the result a defined name. Each time the query is refreshed the named range will automatically adjust. Just be aware that the header will be included in the named range, so, exclude it if looping like posted above. The query would have SQL of the form "SELECT DISTINCT Company FROM table_whatever" and could be set up once outside VBA and then VBA would simply "wks_Whatever.QueryTables(1).Refresh BackgroundQuery:=False", or, otherwise create the query on the fly each time. I've found if doing this second way, better to create a new worksheet each time and delete it after use.

    Thinking further, if you don't want to create a unique list, build one as you go. Such as start with the company name in the first record, store it in an array "UsedNames", do the processing, LOOP STARTS, go to the company name in the next record, check if it is in array "UsedNames", if not add name to the list, do the processing, LOOP

    OK?
    Fazza

  12. #12
    Join Date
    Oct 2006
    Posts
    6

    thanks

    Thanks for this.

    I just got into work now, so ill spend all day playing on it.

    Cheers

    Mat

Posting Permissions

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