+ Reply to Thread
Results 1 to 8 of 8

Thread: From excel to database/web app

  1. #1
    Rebelle
    Rebelle is offline
    Barn Legend Rebelle will become famous soon enough Rebelle's Avatar
    Join Date
    Mar 2008
    Posts
    1,522
    Rep Power
    5

    From excel to database/web app

    Hi All,

    I have an excel spreadsheet, which is used a template and sent out to many users and they input their data and email it back. Once back everything is combine into one.

    I've created some tables from the data in the spreadsheet but trying to figure out how to put everything together .... meaning....there should only be one recordset for each district. so would it be better to populate my details table for all district with blanks or how can i create a form to enter the data but keep them from entering twice for the same district?

    I haven't created the details table yet....I only have the below setup so far.

    tblRegion
    RegID
    Region

    tblDistrict
    DistID
    District

    tblCategory
    CatID
    Category

    tblGroup
    GrpID
    Group

    tblItem
    ItemID
    Item
    ItemCost
    GrpID

    I think the details table will look like:
    tblDetails
    RegID
    DistID
    ItemID
    FieldData1
    FieldData2
    FieldData3
    FieldDate4

    Hope this makes a little sense....Thanks!
    Rebelle, June 6th, 2008 11:57 AM
    Reply With Quote Reply With Quote
  2. #2
    jmurrayhead
    jmurrayhead is offline
    The Barnfather jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead's Avatar
    Join Date
    Mar 2008
    Location
    Reston, VA
    Posts
    4,547
    Blog Entries
    9
    Real Name
    Jason
    Rep Power
    22

    So you're saying that the details table should only have one record for each district? If a record exists, do you just want them to update the existing record with their data or abort the operation?
    jmurrayhead, June 6th, 2008 12:16 PM
    jmurrayhead
    If you agree, give me rep.
    If you like it here...throw us a few bones to help support us.

    Reply With Quote Reply With Quote
  3. #3
    Rebelle
    Rebelle is offline
    Barn Legend Rebelle will become famous soon enough Rebelle's Avatar
    Join Date
    Mar 2008
    Posts
    1,522
    Rep Power
    5

    no the details table will have a record for each item/district. so if there are 30 items, then the district should have 30 records in the details table.

    yes, if they have already filled out the form once, then it should have populated the table with 30 records for their district then if they return, can i have something that says, sorry but you can't add, would you like to edit?

    do you think having them fill out blank form to populate the table is better than just populating the table myself and just say fill in the blanks?
    Rebelle, June 6th, 2008 12:25 PM
    Reply With Quote Reply With Quote
  4. #4
    jmurrayhead
    jmurrayhead is offline
    The Barnfather jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead's Avatar
    Join Date
    Mar 2008
    Location
    Reston, VA
    Posts
    4,547
    Blog Entries
    9
    Real Name
    Jason
    Rep Power
    22

    Okay, let me see if I understand correctly. Let's say you have the following:

    District A
    District B
    District C

    Item 1
    Item 2
    Item 3
    Item 4
    Item 5

    In the details table, District A, for example, would only be allowed 1 record for each item?
    jmurrayhead, June 6th, 2008 12:31 PM
    jmurrayhead
    If you agree, give me rep.
    If you like it here...throw us a few bones to help support us.

    Reply With Quote Reply With Quote
  5. #5
    Rebelle
    Rebelle is offline
    Barn Legend Rebelle will become famous soon enough Rebelle's Avatar
    Join Date
    Mar 2008
    Posts
    1,522
    Rep Power
    5

    correct...something like this....

    DistrictA - Item1 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictA - Item2 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictA - Item3 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictA - Item4 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictA - Item5 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictB - Item1 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictB - Item2 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictB - Item3 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictB - Item4 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictB - Item5 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictC - Item1 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictC - Item2 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictC - Item3 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictC - Item4 - FieldData1 - FieldData2 - FieldData3 - FieldData4
    DistrictC - Item5 - FieldData1 - FieldData2 - FieldData3 - FieldData4


    do you think this is ok?
    Rebelle, June 6th, 2008 12:40 PM
    Reply With Quote Reply With Quote
  6. #6
    jmurrayhead
    jmurrayhead is offline
    The Barnfather jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead's Avatar
    Join Date
    Mar 2008
    Location
    Reston, VA
    Posts
    4,547
    Blog Entries
    9
    Real Name
    Jason
    Rep Power
    22

    I don't see anything wrong with that.

    I would design your application to handle if they can insert or update.

    For example, for each district provide a list of items via a query. This query would only return items that the district does not already have in the details table. These are the items that are allowed for insertion.

    Then, have a list of items that they DO have in the details table. These items will be allowed for updating.
    jmurrayhead, June 6th, 2008 12:46 PM
    jmurrayhead
    If you agree, give me rep.
    If you like it here...throw us a few bones to help support us.

    Reply With Quote Reply With Quote
  7. #7
    Rebelle
    Rebelle is offline
    Barn Legend Rebelle will become famous soon enough Rebelle's Avatar
    Join Date
    Mar 2008
    Posts
    1,522
    Rep Power
    5

    Ok....well, I think it would be nice just to show them what they haven't entered but i think in this case, I will need to create data for each region/district in the table with blanks for the qtrs because they will want to see their results (all the items) then be able to change the qtr info as needed.

    i've attached a sample spreadsheet, everything highlighted in pink is stuff they can change (only misc item desc. and those have $0 so they will need to enter a price). you can see two tabs (sus and opp) because they will have a sus and opp for each location, then i will need to make a summed one of both of those. well, i have the layout in asp (with mehere's help in other asp posts) now but haven't work in the region/district in yet (will do using variable in asp)....but totally forgot about them having to be able to change the desc. on the misc. items .... the last tab on the spreadsheet is the details table where most of the data is being pulled from but I guess I need to add desc. to that. Do you see any problems with doing everything that's in the spreadsheet? i'm thinking maybe i should have some buttons on the top of my asp page with Sus, Opp, and Sum...I'll need to also have one to show all regions too.../sigh.
    Attached Files
    Rebelle, July 7th, 2008 03:11 PM
    Last edited by Rebelle; July 7th, 2008 at 03:21 PM. Reason: added .xls but .zip now :)
    Reply With Quote Reply With Quote
  8. #8
    Rebelle
    Rebelle is offline
    Barn Legend Rebelle will become famous soon enough Rebelle's Avatar
    Join Date
    Mar 2008
    Posts
    1,522
    Rep Power
    5

    Ok, I think I'm going to add "Description" to the detail table since they can be able to modify/change all the Misc items....and hope it works out.

    Wish me luck!
    Rebelle, July 10th, 2008 02:36 PM
    Reply With Quote Reply With Quote
+ Reply to Thread
« Previous Thread | Next Thread »

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


SEO by vBSEO