0

I currently have a database with several "waves" of data. Some data is static and is not updated while other data is continuously coming in on a daily basis.

I'm trying to figure out a way to structure my database so that I can have a form where end-users can view all of the measures captured at a certain visit#. So, basically, I currently have a field for ID# and visit #, but the visit # keeps changing to the most recent #. I want to make it so that a new record is created for each visit # so they can search by visit # AND ID. I hope this makes sense? Ask me clarifying questions if not. Thank you!

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Chris Ray
  • 51
  • 1
  • 1
  • 14
  • 1
    You need two tables, one for static data and one for visits. You can then have a form and subform. – Fionnuala Jun 29 '15 at 13:28
  • Can you supply some sample data? – Gene Jun 29 '15 at 13:44
  • Once data is entered, is it altered? – Gene Jun 29 '15 at 13:45
  • @Gene data is not altered once entered, unless entered incorrectly. It is occasionally deleted, but only in rare circumstances. – Chris Ray Jun 29 '15 at 13:48
  • My biggest issue is being able to run queries pulling data from both static and visit-related data. In other words, has this patient EVER undergone a psych eval either from closed studies from 2 years ago or in their last annual appointment? – Chris Ray Jun 29 '15 at 13:50
  • 1
    With a join on the related field(s), such a query is not difficult. Nor is getting the most recent visit. – Fionnuala Jun 29 '15 at 13:55
  • So actually, @Fionnuala if I go your route, I don't want to house ALL study measures in one table. There are a lot of measures that I'd like to keep in separate tables. How can I link data together on BOTH ID# and Visit#? – Chris Ray Jun 29 '15 at 13:56
  • You can do this through the relationship window and also through a query design window, the sql is approximately `SELECT thisfield, thatfield FROM Tablea INNER JOIN tableb ON Tablea.ID = Tableb.ID` You do not want visit# in both tables. You do not want lots of tables. Please see http://r937.com/relational.html – Fionnuala Jun 29 '15 at 14:00
  • 1
    Here are some additional notes that may or may not help http://stackoverflow.com/questions/12131211/create-form-to-add-records-in-multiple-tables/12132196#12132196 – Fionnuala Jun 29 '15 at 14:05
  • @Fionnuala than you for the resources. Update: I'm adding visit # to each of my measure tables (still only one central table for demographics which holds the ID#). How can I make sure that there are no duplicates (two entries for ID#001 visit # 2?) – Chris Ray Jun 29 '15 at 14:51
  • 1
    Add an index that takes in both Id and Visit. Each related table must include the Demographic Id to get the join. – Fionnuala Jun 29 '15 at 15:20

0 Answers0