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.
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.
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.
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)
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.
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