I it a crosstab query ?

[Guest]

Hi all,
I have an Excel sheet that looks like the below table,i need very urgently
to make a table in MS Access to link it to this sheet.
Note that the sheet contains vertical titles and horizontal ones
Will i use a crosstab query ? how?
Please advise !
name Route 27-Aug 28-Aug 29-Aug 30-Aug 31-Aug
F R1 Off A/L A/L A/L A/L
b R3 Off 9:00 9:00 9:00 9:00
j R3 TR TR TR TR TR
g R3 Off 10:00 10:00 10:00 10:00
y R2 Off 7:00 7:00 7:00 7:00
u R3 Maternity Maternity Maternity 9:00 9:00

 

[John Vinson]

Hi all,
I have an Excel sheet that looks like the below table,i need very urgently
to make a table in MS Access to link it to this sheet.
Note that the sheet contains vertical titles and horizontal ones
Will i use a crosstab query ? how?
Please advise !
name Route 27-Aug 28-Aug 29-Aug 30-Aug 31-Aug
F R1 Off A/L A/L A/L A/L
b R3 Off 9:00 9:00 9:00 9:00
j R3 TR TR TR TR TR
g R3 Off 10:00 10:00 10:00 10:00
y R2 Off 7:00 7:00 7:00 7:00
u R3 Maternity Maternity Maternity 9:00 9:00

You could use a crosstab query to CREATE such a report, but not to
import one. Since the data structure here is emphatically NOT
normalized, it's going to be difficult! You can use File... Get
External Data... Link to link to this mess, but you'll end up with
fieldnames [27-Aug] and the like; you will also have times stored in
Text fields (since Off and A/L are not valid date/time values), which
will not be readily searchable as times.

What do you intend to *do* with this data once you get it into Access?
Nothing will be very easy since it's such heterogenous, non-normalized
data - but if we had an idea of where you want to go we might be able
to suggest some possible ways to mine this lode...

John W. Vinson[MVP]

 

[Guest]

Well,
Thank you for your answer,
I wanna use this data to to look up something,
Example : in this database i have to put the "name" and select the date so
the "shift" and the "route" will appear according to these to pieces of
informatoion.

Hi all,
I have an Excel sheet that looks like the below table,i need very urgently
to make a table in MS Access to link it to this sheet.
Note that the sheet contains vertical titles and horizontal ones
Will i use a crosstab query ? how?
Please advise !
name Route 27-Aug 28-Aug 29-Aug 30-Aug 31-Aug
F R1 Off A/L A/L A/L A/L
b R3 Off 9:00 9:00 9:00 9:00
j R3 TR TR TR TR TR
g R3 Off 10:00 10:00 10:00 10:00
y R2 Off 7:00 7:00 7:00 7:00
u R3 Maternity Maternity Maternity 9:00 9:00

You could use a crosstab query to CREATE such a report, but not to
import one. Since the data structure here is emphatically NOT
normalized, it's going to be difficult! You can use File... Get
External Data... Link to link to this mess, but you'll end up with
fieldnames [27-Aug] and the like; you will also have times stored in
Text fields (since Off and A/L are not valid date/time values), which
will not be readily searchable as times.

What do you intend to *do* with this data once you get it into Access?
Nothing will be very easy since it's such heterogenous, non-normalized
data - but if we had an idea of where you want to go we might be able
to suggest some possible ways to mine this lode...

John W. Vinson[MVP]

 

[John Vinson]

Well,
Thank you for your answer,
I wanna use this data to to look up something,
Example : in this database i have to put the "name" and select the date so
the "shift" and the "route" will appear according to these to pieces of
informatoion.

Is there ANY way that you can get this information into a properly
normalized structure, with (e.g.) fields [Name] (though that's a
reserved word and a very bad choice of a fieldname), WorkDate, Shift
and ShiftTime?

With your current structure it's going to be *very difficult*. You
cannot (at all easily) search for dates if those dates are stored in
fieldnames.

John W. Vinson[MVP]