crosstab or pivottable or ?

[ktdid]

Hi everyone to whom I would say "I'm not worthy..."

I have data in a table in the following fields:
Item, customer, quantity, start date, end date, date designation.

The data looks like:
12345, smith, 100, 10/01/09, 10/01/09, day
12345, jones, 200, 10/01/09, 10/07/09, week
22222, smith, 50, 10/02/09, 10/02/09, day
22222, jones, 400, 10/02/09, 10/31/09, month
etc.

I need to export a summary of the data that looks like this:
10/01/09 10/01/09
10/02/09 10/07/09
Day Week
12345 Smith 100
12345 Jones 200

A crosstab query seemed ideal but I can only pick one of those columns. If
I concantenate these three fields the dates don't go in order (01/01/10 is
before 10/02/09.)

I looked at a pivot table and am a newbie here. At first I thought this
would work but I have to specifically pick the pivottable view, can't rid of
the total columns and can't export with a macro, only the export button from
the pivot table toolbar.

Can someone help me come up with a format/method I can use to export to
Excel in the format above? And, sorry, but I don't know how to write code -
queries and macros have been my friends up until now.

Thank you very much for your expertise!

 

[ktdid]

Hi Ken -
Thanks for your prompt and easy to read reply. I am confused, however -
don't worry "it's me, not you." First, where does the IN statement go (I
don't see a lookup option in the date field property in the query? Second,
my dates keep changing/advancing and I could have up to 40 at a time. Am I
having to enter many, many dates in the IN statement? Any thoughts are
appreciated. Thanks so much.

If you know what the column headings will be then you can order them by means
of an IN clause in a crosstab query. This is the equivalent to entering a
value list as the ColumnHeadings property of the query via its properties
sheet in design view.

The value list must exactly match the column headings so if you've
concatenated the three columns like this for instance:

[start date] & " " & [end date] & " " & [date designation]

The IN clause would be:

IN("10/01/09 10/01/09 day","10/01/09 10/07/09 week","10/02/09 10/02/09 day",
<and so on>)

The ColumnHeadings property would be just the value list from within the
parentheses.

The value list does not need to be restricted to column headings which will
be returned. If you include days and weeks for which there is no data the
column will contain Nulls. This is useful if the query is used as the
RecordSource of a report as you can include a control in the report bound to
a column and the report will work fine even if there is no data for that
column. Otherwise an error would occur as the control's ControlSource
property would be the name of a column not in the underlying recordset.

Conversely, if you omit any days or weeks for which there is data that data
won't be returned.

Ken Sheridan
Stafford, England

Hi everyone to whom I would say "I'm not worthy..."

I have data in a table in the following fields:
Item, customer, quantity, start date, end date, date designation.

The data looks like:
12345, smith, 100, 10/01/09, 10/01/09, day
12345, jones, 200, 10/01/09, 10/07/09, week
22222, smith, 50, 10/02/09, 10/02/09, day
22222, jones, 400, 10/02/09, 10/31/09, month
etc.

I need to export a summary of the data that looks like this:
10/01/09 10/01/09
10/02/09 10/07/09
Day Week
12345 Smith 100
12345 Jones 200

A crosstab query seemed ideal but I can only pick one of those columns. If
I concantenate these three fields the dates don't go in order (01/01/10 is
before 10/02/09.)

I looked at a pivot table and am a newbie here. At first I thought this
would work but I have to specifically pick the pivottable view, can't rid of
the total columns and can't export with a macro, only the export button from
the pivot table toolbar.

Can someone help me come up with a format/method I can use to export to
Excel in the format above? And, sorry, but I don't know how to write code -
queries and macros have been my friends up until now.

Thank you very much for your expertise!