from Excel to Access Large STRUCTURE's

  • Thread starter Thread starter chuck.streb
  • Start date Start date
C

chuck.streb

I have a structure that brings in excel data, Time Vs. Channels with
the cells holding measured values.

I have in the neighborhood of 750 channels ( 3-4 worksheets). And a ton
of records (1000's)

I decided to put it into this format.

ChannelID, ChannelName, Time, Value

The problem I have now is showing it to the user in the shape like
excel.
If the user wants 8 channels, I want to show Time,Ch1,Ch2,Ch3 ... Ch8,
for all records.

I haven't come up with a great idea yet. All idea's welcome.

IF there is a better structure for importing the data, I would like to
hear about that also.

Thanks in advance

Chuck Streb
 
for displaying the data, suggest you try a Cross-tab query. if you go to the
Queries section in the database window, and click the New button, the dialog
box will offer you a Cross-tab Query Wizard.

hth
 
What do you mean by "as a recordset"? You can create a recordset from a
crosstab query.

tina's answer fits your earlier specifications exactly. What do you need
that a crosstab won't supply?
 
Thanks Tina,

But I would like to have it as a recordset.
Click to expand...

Chuck Streb,

Just execute the Crosstab Query Object or SQL string from your VBA
code, and you'll have a recordset of it.

Look up TRANSFORM/PIVOT for the JET SQL syntax of a Crosstab query
if you're interesting in how to write it directly.


Sincerely,

Chris O.

PS While microsoft.public.access.queries was an appropriate
newsgroup for this question, I'm thinking that
microsoft.public.access.externaldata,
microsoft.public.access.tablesdbdesign, and
microsoft.public.access.importexportlin are all, perhaps, not the
best newsgroups to cross-post this into.
 
Duane said:
What do you mean by "as a recordset"? You can create a recordset from a
crosstab query.

tina's answer fits your earlier specifications exactly. What do you need
that a crosstab won't supply?
Click to expand...

Maybe I'm missing something, but in your original posting, what kinds of
values did you want to display for [Ch1], [Ch2], &c.? If they're the
same as the [Value] field in your current recordset, and the combination
is unique (no 2 records for the same [ChannelID] with the same [Time],
or whatever your row identifier will be), then no problem. If you do
have more than one [Value] for a given channel, then you'll need to
decide what to display, such as a maximum, or average, &c., to include
in your Crosstab Query.

Incidentally, you can use the Crosstab Query Wizard to set up your
Crosstab Query initially, then modify it if necessary in Query Design
View if you want to add filter criteria or sorting keys that you didn't
specify while running the wizard.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
The problem with the crosstab is that my number of channels exceeds
400.
I would like to limit it to the channels of interest (<10), But it
seems that VB code would be needed to extract the channel records
before I can do a cross query.

Maybe I need a better structure?!
 
Maybe it is just better to bring it in as a sheet. I was trying to make
the information dynamic.
I wanted to bring in anything. If I bring the data in as sheets, then
the tables will have dedicated column names.

Yes it would be easier to search through, but I won't have the
flexibility to bring in any data regardless of it existed or not.

I was hoping someone has gone through this and has come up with a novel
idea.

I am not asking for much am I ... LOL
 
Hi Chuck,

You can use a criterion in the crosstab query to control which channels
are shown, e.g.
WHERE ChannelID IN (5, 66, 101, 102)
or
WHERE (ChannelID >=5) AND (ChannelID <=10)

In SQL view this goes between the FROM clause and the GROUP BY clause;
in query design view you add a column for the field in question and
select WHERE in the "Total" cell.

Alternatively (or additionally) you can specify column headings for the
crosstab, in which case channels which you haven't listed in the column
headings (in SQL view, the PIVOT clause of the crosstab query) won't be
included in the query's results.

You may need to use VBA to build the appropriate SQL statement.
 
Maybe it is just better to bring it in as a sheet. I was trying to make
the information dynamic.
I wanted to bring in anything. If I bring the data in as sheets, then
the tables will have dedicated column names.

Yes it would be easier to search through, but I won't have the
flexibility to bring in any data regardless of it existed or not.

I was hoping someone has gone through this and has come up with a novel
idea.

I am not asking for much am I ... LOL
Click to expand...

Chuck Streb,

John Nurick, in an earlier post, pointed out the solution to your
need to limit the Channels.


Sincerely,

Chris O.
 
Back
Top