Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pepig
Starting Member
10 Posts |
 Posted - 2007-08-29 : 13:15:27
|
General question: Can anyone give me information as to how to create a query that still allows me to edit the underlying data?Specific question:First some info about my data. I have various items. An item comes from an origin. That's in the 'Items' Table. I need to send items at various destinations so I have an 'ItemDest' table. (you understand that there is a 'One-to-Many' relationship here: to a record in the 'Items' table is a parent for various records in the 'ItemDest' table). additionally, the 'ItemDest' table also contains info on where the item must first stop before it is sent to its end destination.Here what the 2 table contain (simplified):(stars are just ther to help align because the editor suppresses spaces)items -------- itemID (PK) origin itemID| origin |------------------item1 | loc1 |item2 | loc2 |ItemDest ---------- itemdestID (PK,Autonumber) itemID(FK) dest stop itemdestID | itemID | dest | stop |------------------------------------****** 1 | item1 | loc3 | loc4 |****** 2 | item1 | loc5 | loc4 |****** 3 | item1 | loc6 | loc7 |****** 4 | item2 | loc8 | loc7 |I'd like to be able to have a form that displays the info like this: ** "destination** locations" ***** / ***** **** || loc3 | loc5 | loc6 | loc8 |----------------------------------*** "stop-over item1 || loc4 | loc4 | loc7 | *** | __/ locations" item2 || *** | *** | *** | loc7 |/ "itemIDs"I managed to have that view using a CROSSTAB-query.Now I'd like to be able to edit the stop-over locations e.g.: for case(item1,loc6) change "loc7" to "loc4"But it is impossible to edit, Access won't let me do it ! WHY ?I know a way to edit that info would be to create a simple SELECT-query and change the row corresponding to itemdestID #3 right? It works, but when you have 1400 different items and 20 different end-locations, it isn't very handy to scroll down a huge datasheet.So I my question is: Is there any way to edit data when viewing a CROSSTAB-query ?Thanks a lot for reading this long post. I tried to make it look nice so you could easily understand. (At least I hope you will!  ) |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-08-29 : 14:19:45
|
| >>Is there any way to edit data when viewing a CROSSTAB-query ?No. A cross tab query is by definition a summary, and you cannot edit summarized query results. You'd have to copy your cross tab results into another "working" table first, let the user edit the values in that working table, and then update your underlying data based on the working table's values.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
pepig
Starting Member
10 Posts |
Posted - 2007-08-29 : 14:58:39
|
quote:
You'd have to copy your cross tab results into another "working" table first, let the user edit the values in that working table, and then update your underlying data based on the working table's values.- Jeffhttp://weblogs.sqlteam.com/JeffS
Thank's ! Let me try that out.Copying the cross tab result into a working table: create table query, right?But then, going back from the working table to the crosstab... That's where it gets tricky for me... I don't really see how to do that. Use a Update table query with Dlookup() functions to "fetch" the data from the working table?--Pierre |
|
|
|
|
|
|
|
|
"Editable" Queries