Designing A Database To Organize Department's Research

[jag.excel]

To Excel users:

I am trying to develop a database to organize our department's research articles and book chapters. I would like to organize this with the following fields/columns: author, citation (of article), publication - month and year (together).

There are some articles in which 2+ people in the dept have collaborated. I have to be able to quickly count/sum how many unique articles the department has written. A design that would allow me to collapse articles under either the month and year, or by author (not just filter) would be a plus.

Is this best to complete in Excel, or in Access? I've read a bit about Pivot Tables - would I use one here? Any advice would be greatly appreciated!

[:) Sixthsense :)]

You can easily achieve it using excel.

Yes exactly pivot will be the easy one for your task since you can arrive the expected result in 5 to 6 clicks

[jag.excel]

Thanks, Sixthsense. I am a bit of a novice at these things. Right now I have the following columns: 1) contributor, 2) citation: peer reviewed journal articles, 3) citation: published books/chapters, 4) publication date, 5) status (published, accepted, etc).

How would a pivot table benefit me as opposed to just making a table? Also, a few other challenges:

1) I really don't like how the database currently looks with the 2 citation columns since half of the table ends up being empty (a citation is EITHER a journal or book chapter, so one column will be empty when one citation is written). I thought Access was set up so I could type in an author's name and all the citations associated with them would pop up and just show it was tagged as a book or journal article.

Also, I want to count how many citations per author, and then how many total. This just means looking at how many rows are associated with each author. Can you please walk me through this?

[jag.excel]

As an added note, I was asked to do this because the university is requesting a report for the academic year, so I want to be able to quickly generate reports on how many UNIQUE articles the department has published that academic year. This should keep in mind duplicates (or weed out similar ones with slight typos/differences). Help?

[:) Sixthsense :)]

It will be easy to explain and show if you attach a sample file with xyz (Not your original data) of information.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[jag.excel]

Can I e-mail it to you? I am not seeing the "attach" button here (I am really NOT good with computers).

[:) Sixthsense :)]

After clicking reply button You will GO ADVANCED and use the paperclip icon to open the upload window.

Refer the below image:-

View Pic

[jag.excel]

Sample Excel Research Database.xlsx

Hope this works.

[jag.excel]

Sixthsense, were you able to view my attachment? What are your thoughts?

Is there a way to quickly type in "Smith, John" and see that he has X number of publications, but then also to see the whole department has 75 between (this date and this date)? Let me know what you are thinking please! If you do not want to work on that attached spreadsheet, then you can send instructions?