Grouped data

[jigax]

Is there a way to make grouped data editable? When i execute an sql statement with groupd by i get a message on the lower right stating this table is read only. i would like to edit one record and have all records within that groupd update itself. Is this even possible?

 

[cjard]

not really.. think about it; with grouped data the database has no way of identifying a single row to update, or what it should be updated to..

suppose in the tried and tested customers and orders example we have a grouping of

customer ID, sum(orderItemPrice) - discountThisMonth as owing


now you want to update that.. WHAT would you have the database update? suppose you edit the Owing to a thousand dollars.. how should the database update the underlying data to make up that 1000? edit the discount? edit one item? edit all items?

it cant be done

Instead you should write another update query separately and run it separately.

update Orders set price = 1 where customerId = 123


thats how you update multiple rows for one grouped ID... you edit the non grouped data directly

 

[jigax]

Well here’s what I’m trying to accomplish. I import a CSV file into access, the data is work orders for customers but the problem is that each work order has multiple lines depending on the amount of equipment being installed. So I figure I have a datagridview showing me just one line per work for the fields that are the same e.g. tech#, customer name, address, phone. The equipment files which are the ones that are different I will leave the same. I want to see all work orders on a datagridview but insted of showing alll the lines for each work order i want to show one and be able to change the tech number and somehow have it changed on all lines for that work order.

Thanks.

 

[cjard]

what you mention is a standard master/detail relationship

you require two datatables, one with the customer details, one with the work details for that customer. a relation will exist between the two tables because the work items will have a customer ID. if you plan on doing multiple sessions of work for a customer, then each job block might also need a group id that you set

CustomerTable
---------------
CustomerID
CustomerName
CustomerAddress
etc

WorkTable
----------
CustomerID
WorkGroupID
WorkItemID
TaskDescription


so you can have customer 123 who wants the light bulbs replacing and the carpets cleaning as part of springmaintenance:

CUstomerTable
--------------
123
Acme COmpany Ltd
1, ACME Road Blah

WorkTable
----------
123
SPRINGMAINT1
1
replace lightblubs

123
SPRINGMAINT1
2
clean carpets



if an updating event occurs that changes CustomerTable.CustomerID from 123 to 456, and a relation exists that is enforcing integrity, then all instances of 123 in worktable will become 456.

Is this what you require?
If so, have a read of the DATA WALKTHROUGHS (google that) on MSDN

 

[jigax]

Thanks for your help I'll definitely do some reading on data walkthroughs; however all the data is in one table one database. For example if a job has 3 items to be completed by the technician then a work order will have 3 lines. Some fields within those three lines repeat themselves like customer name, address, phone, tech number, job number ect. So I’m trying to group these repeating fields so that instead of showing multiple lines per work order I only show one in a datagridview and really all I look forward to change is the tech number then everything else will remain the same. But I was told it can’t happen because it's grouped data. But i think there has to be a way due to the fact that the grouped data is identical. Thanks for the help.

 

[cjard]

You may not have heard of the concept of normalisation, but that is what needs to happen to your database. Relational Database Management Systems operate on the concept of relations.. i.e. deciding what data is related and giving related data its own table. A simple rule for rudimentary normalisation is that no significant number of data columns should be repeated in a table.

See the notes I wrote above, as regards splitting the data into 3 tables organised thus.. It is a first step towards achieving BCNF level of normalisation. For more information, google for DATABASE NORMALISATION

As regards your particular problem: you split the data as per expected, you can then write a view that makes the 3 tables appear as one. Separate to this, you write an update query that will edit the relevant data in whichever table. It's a longer route, but it's more correct and will give you a better insight into how RDBMS work. If you really want to persist with the un-normalised data then you will need to write a paarameterised update query that changes all the fields you want to change

 

[jigax]

Thanks cjard your help is really appreciated I will most definite read on what you’ve discussed here. Thank you much.