Results 1 to 5 of 5
  1. #1
    schemadyn is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Aug 2017
    Posts
    4

    Need an Antibiogram with automated queries

    I need an antibiogram using MS Access which will create results similar to the attached excel file (in zip). Any help (even if paid) will be highly appreciated. Thank you.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i would make a macro,
    in it would be a series of queries to get the data you need, (in a 'temp' table)
    export it into excel and run a pivot table to get your end result.

    Access has a 'pivot' query (crosstab query) tho not as robust as excel pivot table.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would suggest that you stop any Access development and learn HOW to design relational tables. Currently, you are "committing Spreadsheet". This means that you have designed tables like a spreadsheet. This will cause you lots of problems later on.


    For example, the table "newtblA" has (as do other tables) antibiotic names as field names; the antibiotic names are actually data.

    I would have maybe 6 fields:

    newtblA
    -------------
    newtblAID_PK (autonumber)
    speciesID_FK (Long Integer - link to tblSpecies )
    cultureLocationID_FK (Long Integer -link to tblLocation)
    organismCulturedID_FK (Long Integer -link to tblOrganism)
    AntibioticID_FK (Long Integer -link totblAntibiotic)
    Reading (Integer)

  4. #4
    schemadyn is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Aug 2017
    Posts
    4
    Thank you for your kind advice. Please have a look at the excel file which is the required output. The antibiotic names are fields and data is numeric percentage values for those fields.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yes, I looked at the spreadsheet. What the output looks like ensures that you capture all of the data you need/require, but is not the same as how the data is stored in a relational database.

    Again:
    You're committing spreadsheet. Access is a relational database, not a spreadsheet. Tables are tall and narrow, spreadsheets are short and wide. What happens when you decide to add more antibiotics?
    - With a spreadsheet, it's a simple matter to put another antibiotic name in the first cell of the next column and off you go.
    - With a database, adding an extra field is just the start of the work. You now have the task of modifying every query that retrieves data from this table, every form and report, every bit of code that writes data into it. Silly!


    Perhaps you can explain what you are trying to do. Explain like I am a 10 year old that doesn't understand what you do.


    Question: What is the difference between tables newtbl, newtblA, newtblB and newtblC?




    You might want to read about ACCESS BASICS by Crystal

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

Similar Threads

  1. Automated Reporting
    By TIG_Slayer in forum Reports
    Replies: 2
    Last Post: 08-11-2014, 09:44 AM
  2. Can this be automated on access
    By montypython in forum Database Design
    Replies: 1
    Last Post: 05-23-2012, 10:22 AM
  3. Automated E-mail
    By imintrouble in forum Access
    Replies: 4
    Last Post: 01-11-2012, 10:28 AM
  4. Automated import
    By salisbut in forum Import/Export Data
    Replies: 2
    Last Post: 07-16-2010, 12:56 PM
  5. Automated many Rpts from one??
    By techexpressinc in forum Reports
    Replies: 0
    Last Post: 07-21-2009, 08:20 AM

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