Results 1 to 4 of 4
  1. #1
    Ignace is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    5

    HEEELP: Mulitple data for same record

    HI,



    I need your help as i am struggling to figure out what is the best way to structure my DB for keeping records of the following:

    I am trying to store a monthly water usage for several locations. for instance, i will have locations (Chicago, New York, LA, ....) and for each of those i need to store a monthly water usage. i will therefore have the following data:
    - New York, June 2010, 30'000
    - New York, August 2010, 40'000
    - Chicago, June 2010, 25'000
    etc...

    I created a table with the list of location, what do i do for the monthly volume?
    what is the best way to strucutre this?

    I am fairly new at access as you can guess, so thanks for your precious help!

    I

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    If you want "proper" normalization of your db. I would go with 4 tables:

    tblLocation
    locationID
    locationName
    etc

    tblMonth
    monthID
    monthName

    tblYear
    yearID
    yearName

    tblUsage
    usageID
    locationID
    monthID
    yearID

    waterUsage

    -key-
    Bold - Table Name
    Underline - primary key
    italics - foreign key

    You would create many-to-many relationships because you have unique combinations of location, month, and year. Once you do this, you can query off your usage table by location and then if you want to get a "May 2010" for reporting, you can query monthName & " " & yearName.

    Let me know if that helps or if you need more explanation

  3. #3
    Ignace is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    5

    Thanks so much

    Hi TheShabz,

    Thank you very much for this.

    now the follow up questions are:

    1) how do i avoid double entry. i.e 2 sets of data for same date and same location. currently the table let me do this.

    2) what is the best way to create a for with a matrix format to simplify data entry (x axis: location, y-axis: date)

    thanks a lot in advance

    Ignace

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    1) how do i avoid double entry. i.e 2 sets of data for same date and same location. currently the table let me do this.
    For directly entering it into the table, you cant. On a form, however, you can do a DLookup to check for existing values. If on the even where the data will be entered you check for:
    IF me.CboLocation = DLookup("LocationID", "tblUsage", "monthID = " & Me.CboMonth " AND yearID = " & Me.CboYear) THEN
    Msgbox "This entry is duplicate", vbokonly
    ExitSub
    ELSE
    'whatever else needs to happen
    END IF

    2) what is the best way to create a for with a matrix format to simplify data entry (x axis: location, y-axis: date)
    No need to. Give the user 3 comboboxes that will contain location and month (year if you want flixibility) and then a textbox for the usage. A button that will run an Insert Into query after the check I mentioned above will make it pretty user friendly.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-05-2011, 01:31 PM
  2. Calculating the Sum of Mulitple Columns
    By ARickert in forum Queries
    Replies: 22
    Last Post: 12-27-2010, 09:06 PM
  3. handling nulls in mulitple parameter query
    By haggisns in forum Queries
    Replies: 5
    Last Post: 10-14-2010, 02:09 PM
  4. Query Can it be Done? Mulitple finds
    By Canadiangal in forum Queries
    Replies: 3
    Last Post: 02-28-2010, 03:45 PM
  5. Condense Mulitple Records
    By jquickuk in forum Queries
    Replies: 1
    Last Post: 08-10-2009, 08:43 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