Discuss "From excel to database/web app" in the Database Design Help forum.

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!

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
If you agree with me... click the icon!
If my post solved your problem, click the button in the lower right-hand corner of the post.

If you like it here...throw us a few bones to help support us.

Join our Folding team: DeveloperBarn Folding

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?

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?

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?

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.

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

Test.zip (4.7 KB, 5 views)

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!