Import Excel columns to Access datasheet

[RealGomer]

I have an Excel spreadsheet that has 92 columns I use for recroding
inspection data. The first 18 columns identify the record. The next 74
columns contain the inspection results. Some of the 92 columns have no data
in them. The 74 results columns are made up of 37 pairs of data. The first
column of the pair is posted data, the second column of the pair is the
actual data.
What I want to do is import this data into Access so I can create keep the
actual inspection results paired and be able to create a form or report with
the data segregated between the 18 columns of identification and the 74
paired columns of results.
Clear as stained glass, I'm sure.

 

[KARL DEWEY]

WOW! How can you sort on 18 columns? Or do you use a column as a location
identifier instead of just putting an identifier for the location in only one
column?

Tell me more. Can you post about two rows of your data?

 

[RealGomer]

18 columns is nothing. We have one database that can be sorted on about about
50 fields. Anyway, here are a couple rows from the spreadsheet. Hopefully you
can sort out the mess.

ID# Sector Date Store Address Phone Area ZIP Signer/Mgr Inspector Items Total $ # + # - $ + $ - Comments Error % Shelf Scan

1593 DS 9/16/2005 Walgreen #2120 7135 Becchmont Av. 231-8600 Anderson
Twp. 45230 Sandy Dykes Richardson 100 $644.98 7 0 $15.09 $0.00
Fails 7% $2.39 $2.59
3398 AP 9/19/2005 Auto Zone # 1748 Seymour
Av. 351-8600 Roselawn 45237 George Alexander Richardson 100 $1,108.25
19 2 $32.20 $1.20 Fails 21% $3.99 $4.29

I did not include all of the 74 results columns, only the first pair. If you
look at the record for Auto Zone, you should see the #+ = 19 and #- = 2. For
that one location, there would be 21 column pairs with the test results,
SHELF & SCAN. The first pair then read SHELF price of $3.99 and a SCAN price
of $4.29. There are 20 more column pairs that total to $+ = $32.20 and $- =
$1.20. The ERROR % = 21% is caluclated as (#+ + #-) / ITEMS, or (19 + 2) /
100.
When we sort the results, we look trends, repeats, and errors. So if we want
to see all of the tests in SECTOR AP (Auto Parts stores) we sort by SECTOR.
We look at this data pretty much by any of the first 18 columns, except for
SIGNER/MGR.

 

[John Nurick]

A couple of questions:

1) Which of the first 18 fields are actually required to identify an
individual row (rather than just being useful for sorting and grouping
the data)? For example, each store presumably keeps its address, Zip
code, and area. Does the ID number provide this unique identifier?

2) It sounds as if the 37 pairs of results columns all refer to the same
kind of result but on different tills, or different sections of the
store, or something like that. Do these things have names, or are they
referred to by numbers 1 to 37?

What I'm thinking is that you need to normalise the data into three,
four or more Access tables. Note that I've used field names with no
special characters:

Stores
(one record per store, with fields such as)
Store (e.g. Walgreeen #2120) - primary key
Sector
Address
Area
Zip
Phone

DataReturns
(one record for each row in the Excel data)
ID (same as ID# in the Excel data) - primary key
Store - foreign key into Stores table
DateOfReturn
Signer_Mgr
Inspector
Items
AmtTotal
NumberOver
NumberUnder
AmtOver
AmtUnder
Comments
ErrorPercentage

XXX
(I don't know what to call this table. It needs to
contain a list of the 37 different things to which the
37 Shelf/Scan pairs refer)
XXXName - primary key

DataItems
(one record for each Shelf/Scan pair in each DataReturn)
ID - foreign key into DataReturns table
XXXName - foreign key into DataItems table
(primary key consists of ID, XXXName)
Shelf - amount from the relevant Excel column
Scan - ditto

A structure like that will allow very sophisticated querying and
totalling of the data.

As for actually importing it, the general idea is to link to or import
the Excel data as is, and then use a series of append queries to move
the data into the Access tables. Life will be simpler if you can change
the Excel column headings into unique, legal Access field names.

 

[KARL DEWEY]

I think John is correct in his comments except I am making the assumption the
ID is a SampleID and not StoreID. Based on that I built some queries for you
but left your field names the same.
Link the Excel file and name it StoreSample.
Use the first query to build a table (Make Table Query), naming it
SampleInfo, and limit number of records to one (click on the window on the
icon tool bar that says “All†and change it to 1 (one). Open the table in
design view and make the ID a key field by clicking on the icon the looks
like a yellow key – save. Then append the data. Never mind the error message
that it did not append records due to key violation as they are duplicates
and are not used in this table.

StoreSampleAppend --
INSERT INTO SampleInfo ( [ID#], Sector, [Date], Store, Address, Phone, Area,
ZIP, [Signer/Mgr], Inspector, Items, [Total $], [# +], [# -], [$ +], [$ -],
Comments, [Error %] )
SELECT StoreSample.[ID#], StoreSample.Sector, StoreSample.Date,
StoreSample.Store, StoreSample.Address, StoreSample.Phone, StoreSample.Area,
StoreSample.ZIP, StoreSample.[Signer/Mgr], StoreSample.Inspector,
StoreSample.Items, StoreSample.[Total $], StoreSample.[# +], StoreSample.[#
-], StoreSample.[$ +], StoreSample.[$ -], StoreSample.Comments,
StoreSample.[Error %]
FROM StoreSample;

SampleDataTable --
SELECT TOP 1 StoreSample.[ID#], 1 AS Item, StoreSample.[Shelf-1] AS Shelf,
StoreSample.[Scan-1] AS Scan INTO SampleData
FROM StoreSample;

Open the SampleData table and delete all records. Open the SampleData table
in design view and hightlight ID# and Items, click the key icon and save.

Make this query --
SampleCount --
SELECT SampleData.[ID#], Max(SampleData.Item) AS MaxOfItem
FROM SampleData
GROUP BY SampleData.[ID#];

This query will append the first set of data.
SampleDataAppend --
INSERT INTO SampleData ( [ID#], Item, Shelf, Scan )
SELECT StoreSample.[ID#], 1 AS Item, StoreSample.[Shelf-1],
StoreSample.[Scan-1]
FROM StoreSample;

Make 37 queries like this. The difference is the Shelf/Scan column number.
That number also is calculated into the Item number.
(Int([MaxOfItem]/37)*37)+1
SampleDataAppend-1
INSERT INTO SampleData ( [ID#], Item, Shelf, Scan )
SELECT StoreSample.[ID#], (Int([MaxOfItem]/37)*37)+1 AS Item,
StoreSample.[Shelf-1], StoreSample.[Scan-1]
FROM StoreSample INNER JOIN SampleCount ON StoreSample.[ID#] =
SampleCount.[ID#];

SampleDataAppend-2
INSERT INTO SampleData ( [ID#], Item, Shelf, Scan )
SELECT StoreSample.[ID#], (Int([MaxOfItem]/37)*37)+2 AS Item,
StoreSample.[Shelf-2], StoreSample.[Scan-2]
FROM StoreSample INNER JOIN SampleCount ON StoreSample.[ID#] =
SampleCount.[ID#];

SampleDataAppend-3
INSERT INTO SampleData ( [ID#], Item, Shelf, Scan )
SELECT StoreSample.[ID#], (Int([MaxOfItem]/37)*37)+3 AS Item,
StoreSample.[Shelf-3], StoreSample.[Scan-3]
FROM StoreSample INNER JOIN SampleCount ON StoreSample.[ID#] =
SampleCount.[ID#];


Using 37 Shelf/Scan in each Excel row use two rows plus 26 on the third row
to equal 100 items.

The append queries will need to be run 3 times.

 

[John Nurick]

I think John is correct in his comments except I am making the assumption the
ID is a SampleID and not StoreID.

I think we're on the same wavelength there.

 

[RealGomer]

But what's the frequency, Kenneth?

Seriously, thanks guys for spending your weekend helping this tyro. I'll
give your ideas a shot and let you know how it works out. Maybe I'll even be
able to build a database for traking these inspections.

 

[RealGomer]

I jumped too quickly at your and Karl's suggestions. My response to your two
questions:

1) ID# is a unique field with no duplicates allowed. Therefore it would be
the Primary Key to identify each record, that is ID# 609 will ALWAYS identify
Walgreen #1502 at 1776 Seymour, and no other establishment.

2) The 37 pairs refer to item tested item per pair. So if we tested
software, the SHELF record would be $199.95 and the SCAN record would be
$209.95. (If there were just one error, this pair would equal the amount in
the field $+ or SCAN minus SHELF.)
For each establishment there is one test per date. For each test, there are
the related fields of #=, #-, $+, $-, TOTAL $, ERROR %, and then up to the
same number or SHELF/SCAN pairs as ITEMS. So if ITEMS = 100, then there could
be up to 100 SHELF/SCAN pairs. And if that ever happened, we'd shut down the
establishment in a heartbeat.

 

[John Nurick]

So how does one tell what "item"

I jumped too quickly at your and Karl's suggestions. My response to your two
questions:

1) ID# is a unique field with no duplicates allowed. Therefore it would be
the Primary Key to identify each record, that is ID# 609 will ALWAYS identify
Walgreen #1502 at 1776 Seymour, and no other establishment.

You also say these tests are repeated daily. Does 609 identify that
store, or that store on that date? If the ID just identifies the store,
you neeed ID plus date to identify one record out of all the others that
may eventually end up in the database.

2) The 37 pairs refer to item tested item per pair. So if we tested
software, the SHELF record would be $199.95 and the SCAN record would be
$209.95. (If there were just one error, this pair would equal the amount in
the field $+ or SCAN minus SHELF.)
For each establishment there is one test per date. For each test, there are
the related fields of #=, #-, $+, $-, TOTAL $, ERROR %, and then up to the
same number or SHELF/SCAN pairs as ITEMS. So if ITEMS = 100, then there could
be up to 100 SHELF/SCAN pairs. And if that ever happened, we'd shut down the
establishment in a heartbeat.

It sounds as if there are not 37 Shelf/Scan pairs, but a variable number
that could theoretically be as high as 100. In the Excel sheet, how do
you tell what item a Shelf-Scan pair refers to?

 

[RealGomer]

Well, let's see if I can answer your new questions.
I'm not sure what you mean by "So how does one tell what item?" The column /
field ITEMS is strictly a numberm the records how many items were checked. I
think I put a repeated word in my previous answer. We will check either 25,
50, 100 or 200 items per inspection.
Each SHELF/SCAN pair reflects the actual results of any item that fails the
inspection. So in my sample using software, the item on the inspection report
would read, "ITEM #34 Microsoft .NET Server" "SHELF $199.99" "SCAN $209.99",
where the CAPITALIZED words are the column heads.
In the Excel spreadsheet, each field name is a separate column, so for the
SHELF SCAN pairs, the spreadsheet currently has 74 columns, alternately
between SHELF and SCAN.
If you return to a previous posting, I believe I listed all of the column
names. ID# is the first column, DATE is the third, ITEMS is the ninth and the
SHELF SCAN pairs begin at column "Q". (For purpose of the database, I'm
ignoring the SIGNER and INSPECTOR fields as they are not needed for analysis.)

Each ID# ties to one establishment only. Each establishment may have
multiple inspection during the course of the year. The prices of the items
that fail the inspection are placed in the SHELF SCAN columns on the same row
as the inspection date.
I'm going to guess that there would need to be a minimum of three tables to
get things copacetic. The first to identify the establishments, the second to
identify the inspections by date, and the third to show the inspection
results.

 

[RealGomer]

I'm getting an error when I try to run the StoreSampleAppend query -
"The expression you entered contains invalid syntax."
"You may have entered an operand without an operator."

The word "INTO" after the word "INSERT" is then highlighted
--
I know enuff to be dangerous.

I think John is correct in his comments except I am making the assumption the
ID is a SampleID and not StoreID. Based on that I built some queries for you
but left your field names the same.
Link the Excel file and name it StoreSample.
Use the first query to build a table (Make Table Query), naming it
SampleInfo, and limit number of records to one (click on the window on the
icon tool bar that says “All†and change it to 1 (one). Open the table in
design view and make the ID a key field by clicking on the icon the looks
like a yellow key – save. Then append the data. Never mind the error message
that it did not append records due to key violation as they are duplicates
and are not used in this table.

StoreSampleAppend --
INSERT INTO SampleInfo ( [ID#], Sector, [Date], Store, Address, Phone, Area,
ZIP, [Signer/Mgr], Inspector, Items, [Total $], [# +], [# -], [$ +], [$ -],
Comments, [Error %] )
SELECT StoreSample.[ID#], StoreSample.Sector, StoreSample.Date,
StoreSample.Store, StoreSample.Address, StoreSample.Phone, StoreSample.Area,
StoreSample.ZIP, StoreSample.[Signer/Mgr], StoreSample.Inspector,
StoreSample.Items, StoreSample.[Total $], StoreSample.[# +], StoreSample.[#
-], StoreSample.[$ +], StoreSample.[$ -], StoreSample.Comments,
StoreSample.[Error %]
FROM StoreSample;

SampleDataTable --
SELECT TOP 1 StoreSample.[ID#], 1 AS Item, StoreSample.[Shelf-1] AS Shelf,
StoreSample.[Scan-1] AS Scan INTO SampleData
FROM StoreSample;

Open the SampleData table and delete all records. Open the SampleData table
in design view and hightlight ID# and Items, click the key icon and save.

Make this query --
SampleCount --
SELECT SampleData.[ID#], Max(SampleData.Item) AS MaxOfItem
FROM SampleData
GROUP BY SampleData.[ID#];

This query will append the first set of data.
SampleDataAppend --
INSERT INTO SampleData ( [ID#], Item, Shelf, Scan )
SELECT StoreSample.[ID#], 1 AS Item, StoreSample.[Shelf-1],
StoreSample.[Scan-1]
FROM StoreSample;

Make 37 queries like this. The difference is the Shelf/Scan column number.
That number also is calculated into the Item number.
(Int([MaxOfItem]/37)*37)+1
SampleDataAppend-1
INSERT INTO SampleData ( [ID#], Item, Shelf, Scan )
SELECT StoreSample.[ID#], (Int([MaxOfItem]/37)*37)+1 AS Item,
StoreSample.[Shelf-1], StoreSample.[Scan-1]
FROM StoreSample INNER JOIN SampleCount ON StoreSample.[ID#] =
SampleCount.[ID#];

SampleDataAppend-2
INSERT INTO SampleData ( [ID#], Item, Shelf, Scan )
SELECT StoreSample.[ID#], (Int([MaxOfItem]/37)*37)+2 AS Item,
StoreSample.[Shelf-2], StoreSample.[Scan-2]
FROM StoreSample INNER JOIN SampleCount ON StoreSample.[ID#] =
SampleCount.[ID#];

SampleDataAppend-3
INSERT INTO SampleData ( [ID#], Item, Shelf, Scan )
SELECT StoreSample.[ID#], (Int([MaxOfItem]/37)*37)+3 AS Item,
StoreSample.[Shelf-3], StoreSample.[Scan-3]
FROM StoreSample INNER JOIN SampleCount ON StoreSample.[ID#] =
SampleCount.[ID#];


Using 37 Shelf/Scan in each Excel row use two rows plus 26 on the third row
to equal 100 items.

The append queries will need to be run 3 times.

A couple of questions:

1) Which of the first 18 fields are actually required to identify an
individual row (rather than just being useful for sorting and grouping
the data)? For example, each store presumably keeps its address, Zip
code, and area. Does the ID number provide this unique identifier?

2) It sounds as if the 37 pairs of results columns all refer to the same
kind of result but on different tills, or different sections of the
store, or something like that. Do these things have names, or are they
referred to by numbers 1 to 37?

What I'm thinking is that you need to normalise the data into three,
four or more Access tables. Note that I've used field names with no
special characters:

Stores
(one record per store, with fields such as)
Store (e.g. Walgreeen #2120) - primary key
Sector
Address
Area
Zip
Phone

DataReturns
(one record for each row in the Excel data)
ID (same as ID# in the Excel data) - primary key
Store - foreign key into Stores table
DateOfReturn
Signer_Mgr
Inspector
Items
AmtTotal
NumberOver
NumberUnder
AmtOver
AmtUnder
Comments
ErrorPercentage

XXX
(I don't know what to call this table. It needs to
contain a list of the 37 different things to which the
37 Shelf/Scan pairs refer)
XXXName - primary key

DataItems
(one record for each Shelf/Scan pair in each DataReturn)
ID - foreign key into DataReturns table
XXXName - foreign key into DataItems table
(primary key consists of ID, XXXName)
Shelf - amount from the relevant Excel column
Scan - ditto

A structure like that will allow very sophisticated querying and
totalling of the data.

As for actually importing it, the general idea is to link to or import
the Excel data as is, and then use a series of append queries to move
the data into the Access tables. Life will be simpler if you can change
the Excel column headings into unique, legal Access field names.

 

[John Nurick]

That first "So how does one tell what item" is a fragment that I should
have deleted.

If I understand you right, the last 74 columns of the spreadsheet are
headed
SHELF SCAN SHELF SCAN ... SHELF SCAN
and there is no indication what item each pair may refer to. Is that
correct?

 

[RealGomer]

Each pair corresponds to an item on the inspection report that failed the
inspection. These items are NOT reflected in the spreadsheet, only the shelf
price and the scan price. The difference between these two amounts is a
number we also track to see if certain companies consistantly misprice items,
either by establishment, area, season, or the like.

 

[John Nurick]

I'm just a bit surprised by the fact that you want to get these numbers
into your database without being able to tell what items they refer to.

 

[KARL DEWEY]

Seems to me you would want to record the UPC of the mismatched items.

Well I assumed wrong – the ID was the store ID. So you need different tables.

StoreAppend –
SELECT StoreSample.[ID#], StoreSample.Sector, StoreSample.Store,
StoreSample.Address, StoreSample.Phone, StoreSample.Area, StoreSample.ZIP,
StoreSample.[Signer/Mgr] INTO StoreInfo
FROM StoreSample;

The ID# is the primary key.

StoreSampleAppend --
SELECT StoreSample.[ID#], StoreSample.Date, StoreSample.Inspector,
StoreSample.Items, StoreSample.[Total $], StoreSample.[# +], StoreSample.[#
-], StoreSample.[$ +], StoreSample.[$ -], StoreSample.Comments,
StoreSample.[Error %] INTO SampleInfo
FROM StoreSample;

The primary key is the ID# and Date combination.

SampleDataTable --
SELECT StoreSample.[ID#], StoreSample.Date, 1 AS Item, StoreSample.[Shelf-1]
AS Shelf, StoreSample.[Scan-1] AS Scan INTO SampleData
FROM StoreSample;


SampleDataAppend-2
INSERT INTO SampleData ( [ID#], Item, Shelf, Scan )
SELECT StoreSample.[ID#], 2 AS Item, StoreSample.[Shelf-2],
StoreSample.[Scan-2]
FROM StoreSample
WHERE (((StoreSample.[Shelf-2]) Is Not Null));


Continue through 37.

 

[RealGomer]

I want to publicly thank John & Krrl for their help. I was able to build a
decent database, import the data from Excel, and even get a couple good
queries built.
Thanks, guys.

 

[John Nurick]

I want to publicly thank John & Krrl for their help. I was able to build a
decent database, import the data from Excel, and even get a couple good
queries built.
Thanks, guys.

Thanks for the kind words - but as a matter of national security we need
to know: are we making you more or less dangerous?