Edit data in some type of Crosstab

faunnab
faunnab used Ask the Experts™
on
I have tried this in Visual Studio 2008 but since my knowledge in this is limited.  I reverted back to Access.

I have been reading all of the posts on cross tabs. . .queries and forms.  I understand you cannot edit a crosstab query (although all of my data is perfect in that format - with no subtotals or groupings. . .ugh)  I have read posts on making a cross tab form, but nope, I cannot seem to edit that either (still trying though with a db sample someone posted.)

Here's what I have. . . thinking outside the box would be great!

I have an ERP system (SQL database) that has a resource scheduler that does not work the way we want it to.  So, I want to export the data, and let my scheduler person edit it, and then push it back into my ERP system.

This is what the existig table looks like . . . .

Part     Resource     StartDate     Hours
123      mach1         6/28/10        19.5
123      mach2         6/30/10        51.0
123      mach3         7/6/10          51.0
456      mach1         6/28/10        6.0
456      mach2         6/29/10        16.0
456      mach3         7/1/10          12.0

What I would REALLY like is a form that she can edit (drag and drop would be perfect - cut and paste is fine) to make the schedule as we want it, and then update my ERP.

Part     Resource     6/28/10   6/2910       6/30/10    7/1/10     7/6/10
123      mach1         19.5      
123      mach2                                        51.0
123      mach3                                                                      51.0
456      mach1          6      
456      mach2                       16.0
456      mach3                                                      12.0

What would be ideal . . is if she moves hours from one date to another, the entire part backwards schedules. . .but that will be another question!  I have the crosstab query (not editable), and I have a crosstab form (I thought I could get it to edit based on what I read, but no-go. . . )

TIA
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I am not sure what your question is.  What is it that you want to know?

Author

Commented:
Sorry. . .
How do I make my first table look like the second table, but the second table needs to be editable.
Top Expert 2016

Commented:
you can create another table based on the existing records using VBA codes. is this an option?
HI there.

I've wrestled with this in the past, and ended up implementing cap's idea.  It works like this:

1. Create a crosstab query with parameters to set your date range.
2. Create a make table query with the crosstab as a source.  Have it create a table with a name like tblDataCrosstab or something
3. Run the make table query once so you have and example of the table.
4. Create a form.  Drop the new table onto the form so it's as a subform.  Set it up to only ever display as a datasheet.  Add controls to the header and footer of the form as required.
5. Create a second form.  On this form put your date selectors to supply the date range parameters to the crosstab query, and a command button that will run the code to get this to work.
6. In the On_Click event of this button, write the following code (this is air-code, you'll have to do the work yourself!)

On Error Resume Next  'This should only apply to the next line
DoCmd.DeleteObject actable, tblDataCrosstab  

On Error GoTo MyUsualErrorHandler   'Work this out for yourself!
CurrentDB.Execute qryMakeTableQuery  'Use the name of your own make table query

DoCmd.OpenForm frmMyCrosstabForm 'Use the name of your own form

7. Back on the Crosstabe form, now you've got to write some fancy code too.  Create a couple of buttons, something like "Update" and "Cancel".  In the On_Click event for cancel simply put

DoCmd.Close Me.Name

to close the form without doing anything else.

In the On_Click event of the other button, you'll need to do something a bit subtler.  This is going to be even airier air-code!

Grab the form's recordset.
For Each Record
   For Each Column
      Build  a SQL UPDATE string that out of field values AND if the field's name isn't one of the non-crosstab row names, then use the field's name as the WHERE clause  (it'll look like "UPDATE tblData SET Part = "321", Resource = "456789", Hours = 44 WHERE (Date)=#7/2/2010#" and you'll have grabbed PArt, Resource, and Hours from the value, but #7/2/2010# from the field name.  This will take some fiddling, ok.
   Next Column
   CurrentDB.Execute strSQL_Update
Next Record

If I had the real code in front of me, I'd post that, but I don't.  I warn you, that bit where you have to mess with the values/field names is tricky.  You'll have to test if the field name that looks like a date has a value, so you know that you can use that field name in the WHERE clause.  But if you've got more than one date field with a value, then you'll have to do more than one update per row, and you'll have to track that.

Damn, I wish I had my original code with me.

Anyhow, you can do this, but it is a fiddle.  Good luck.  

Author

Commented:
Thsi is great!  Thank you.  Yes Capicorn, vb is an option and Andrew, I think you have given me a great deal to work with. . .THANK YOU!!  

I started what you mentioned above, but I couldn't make a table from a crosstab query, but I your instructions told me what I was looking for. .  I think I combined too many things. . .

I found a sample database that sort of does what you explain above, so I have some code to work with, along with what I have built. . .

Thank you to both of you!  I will keep you posted.  I know it's a big project, but I'm up for the challenge.

Faunna

Author

Commented:
I'm not sure if you will see this message since I accepted a solution, but I created my crosstab query, and it looks great!  When I make table from this query, the results are completely different.  it does not look like the crosstab query results.  I also created the subform, and it does not show up as the cross tab query results either. . .

What am I doing wrong?  I'm going to keep following your insrtuctions incase it is somewhere down the line that the results show up as a cross tab.

Thanks for your help!

Author

Commented:
I figured it out.  I combined two steps. . .thanks!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial