Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query Access using Excel (Office 2000)

    Hi Lounge.

    This relates to both access and excel, but figured its a better question here;

    I am attempting to link an excel workbook to query an access database called db1. located Catabases

    The database has one table called chemicals and I might have to change this?

    I have added about 11 records that contain data for 2 blend systems...This will grow of course.

    see attached excel worksheet which represents my access table.

    Question 1.
    Should I build a table for each Blend System or 1 table with all systems which will probably contain 25 in the future?

    Question 2
    I find Excel is easy in doing big calculations,Thats why I am using Excel as a user Input form.
    So i would like to query the Chem table in access and populate the worksheet with a result. Has any one built anything out there that can question access
    and get a result returned into specified cells?

    More insight on what im trying to do.

    In my attached (access wanna be table) sheet "Access Chemical Table" I have highlighted (in blue) the condition columns that will find
    the correct result row.

    When the result row or record is found I would like to report the additives > header name and the loading if > 0 or not null on the recod row
    then paste the header and loading for each chemical that has a not null value. There will never be more then 8 to added to the excel table.

    I am guessing that I might have to design the access database differently, so any help or input would be gladly accepted.

    I have also made a example of my workbook on the attached in a worksheet which will imitate the form with inputs that must be filled out to get the results
    from the chemical Table in access.

    Thank you

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Access using Excel (Office 2000)

    Question 1: There's no point in creating lots of tables with exactly the same structure. But you should not have a separate field for each chemical. Instead, store the general info in one table, and create a second table that looks like this:

    <table border=1><td>ID</td><td>Chemical</td><td>Stage</td><td>Conc</td><td align=right>1</td><td>W311</td><td align=right>1</td><td align=right>7</td><td align=right>1</td><td>W311</td><td align=right>2</td><td align=right>7</td><td align=right>1</td><td>W311</td><td align=right>3</td><td align=right>7</td><td align=right>1</td><td>W311</td><td align=right>4</td><td align=right>7</td><td align=right>1</td><td>W313</td><td align=right>1</td><td align=right>5</td><td align=right>...</td><td>...</td><td align=right>...</td><td align=right>...</td></table>
    This is much easier to analyze later on. The table is linked to the other table on ID.

    Question 2: I don't understand why Excel's calculation features should make data entry in Excel preferable. I'd do the data entry in Access, and the calculations in Excel. You can use Data | Get External Data | New Database Query to link to an Access table or query. You can then perform all kinds of calculations on the data in Excel.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Access using Excel (Office 2000)

    Thank you for the reply.

    1. Thank you this makes sense.

    2. I agree that access data entry and using Data | Get External Data | New Database Query would make a whole lot more sense. My situation is the workbook I created
    works very good other then its 3mb in size and I am saving about 300 every month.

    As a side project I am learning Access and have been looking for good books on the subject of how to create an access database. Im new at this.

    Although, If it was possible to send my workbook to you, you could see that it would be a major project to create a database that works the way my excel workbook does.
    lots of calculations.

    Question,

    Is woodys lounge build on a database system?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Access using Excel (Office 2000)

    If you search this forum for access beginner books or something similar, you'll find several threads mentionung books and other resources for learning Access.

    The Lounge uses a MySQL database; the software is written in Perl.

Posting Permissions

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