Crosstab?

[BobRice]

I am trying to create a display of information in the form of a crosstab
query in Access 2007.

DATE1 DATE2 DATE3 DATE4 DATE5
PREP Joe
CLEAN Bill Bill
FILL Joe Pete Pete

I am at a total loss.
And does Access support moving the records by drag and drop? ie Moving Pete
to a different date and/or task?

Any help is appreciated.

 

[Allen Browne]

Crosstab queries are read-only, so you can't drag'n'drop.

It may be possible to do it programmatically, by binding the crosstab to a
form in Continuous or Datasheet view, and using the control events to
execute an UPDATE query to modify the data in the tables and then requery
the form. But it's not a simple task.

 

[BobRice]

Thanks. I still need to get the information into a form for display in the
format:

DATE1 DATE2 DATE3 DATE4 DATE5
Is a crosstab query the solution, and if so how should I approach it?

 

[Allen Browne]

It's quite difficult to tell what your skill level is from a few lines of
text in the question. You will need good coding skills in VBA and an
understanding of SQL to achieve the crosstab-based form you describe.

If you can't get that working, another option would be to create a
de-normalized table as the source for this form. Then write code to populate
it from the real table when the from opens, and go the other direction when
the form closes. This will still require good coding skills in VBA and SQL.
It may be simpler for you, but it suffers from the disadvantage of not being
multi-user.

 

[BobRice]

I've got a good coding skills, I just don't see where and how to begin on
this one.

 

[Allen Browne]

Okay, I don't have an example to hand. I suspect one of the MVPs did, but I
can't recall whether it was Duane or Albert or one of the others.

If we assume you have a form based on a crosstab (so you've sorted out the
issues associated with the changing field names that the crosstab may
generate), you have a Continuous form. When you click on a text box and drag
it somewhere else, its mouse events fire (so you know the source box.) Its
Mouse Up event will give you the offesets (X and Y) in twips (1440 twips = 1
inch), so based on that you can calculate what control the mouse was over
when the user completed the drag. (It's actually a bit more complex than
that, as the user may not have clicked on the current record row.)

Now you want to respond to that by moving the value from the source into the
target. Execute 2 update queries: one to update the target with he source
value, and the other to update the source to Null. Then requery the form so
it shows the update. (You'll need to save enough info to identify the
current record first, requery, and then FindFirst. This may cause the
continuous form to scroll in an undesirable manner.)

As I say, that's off the top of my head, but hopefully an idea you can
develop.