Jeff said:
Creating dummy records isn't usually necessary. If you are trying to
generate data for a report (printed out), you can use a cross-tab query to
"fill in" the blanks, rather than all the coding/handling needed to dummy up
records (plus the inconvenience of not being sure if the record you are
looking at means zero items or is a bogus/dummy record.
I agree with Jeff Boyce about the cross-tab query, but thought that you
might like to see a specific example. (Plus, I had some other comments
about your design.)
I have devised some Tables and Queries that track this information in a
way that I think may be able to save you a bunch of work (except that it
will take a little work to set up -- but I think it will pay off later).
Access isn't necessarily more powerful; there are some things that Excel
does better. In your case, you're probably doing the right thing --
Access is likely to be able to do what you want and give you better
insight into the information you have -- but you might still want to
consider using Excel for such tasks as optimizing your operations, doing
numerical solutions of differential equations, etc.
Not understanding the relationships among the quantities you listed in
your example, for the moment I considered all the numbers to be
independent data. That's probably not true, of course, and usually it's
not a great idea to store in Tables data that you could compute via a
Query. But to the extent that you can calculate some of the data, you
can remove them from your Tables and reduce the amount of maintenance
work you'll need to do.
Example: If you know both the total before sales tax (stored in a Table)
and the tax rate, you don't need also to store the total after tax in a
Table. Access won't give you an error message, but storing both values
doesn't tell you anything you don't already know with just the first value.
In your example, it looks as if "Rate" is always equal to 3 and thus
doesn't need to be stored. But for now I've stored it along with the
other values, just to give an idea of what can be done.
Usually, in an Access database, if you find that you have several Tables
that all contain pretty much the same kinds of information, that's a
signal that they should be combined into one Table, with maybe an
additinoal field to distinguish the records.
What I propose is to set up some Tables that may look quite different
from what you're used to, but which will hold your information without
duplicating it unnecessarily. (As I said earlier, you can go further
than I did because you know how the fields are supposed to interact.)
Of course, before you do anything to your database, you should create a
backup copy of the file.
To begin with, I imported your given data into a Table that I called
[Spreadsheet]. In this Table, several of the fields, such as [WK3] and
[WK7], contain identical types of data, each representing a number of
parts, and I want to put those all into one field.
[Spreadsheet] Table Datasheet View:
Type WK1 WK2 WK3 WK4 WK5 WK6 WK7 WK8
---- --- --- --- --- --- --- --- ---
Received 5 7 2
Delivered 2 4 3 2 2
Stock 15 100
Rate 3 3 3 3 3 3 3 3
New Stock 13 14 111 116 113 113 110 107
You maintain additional information about parts, and I put that into a
Table called [Parts]. I added a [Parts_ID] field (random Autonumber
data type) to be used to refer to each record. For now, there is only
one record, but I assume you'd have others for other LOBs.
[Parts] Table Datasheet View:
Parts_ID Part Name Description Supplier
--------- --------- ----------- ---------------
-39783494 PartX 30mm Bolt Joe Bloggs & Co
There were several rows (records) in [Spreadsheet], the first field
indicating type of datum in each of the [WK1] ... [WK8] fields in the
record. I listed them in a [Types] Table, to use in classifying the
data once they are split out into another Table that I shall call [Stock].
[Types] Table Datasheet View:
Types_ID Type
-------- ---------
10 Received
20 Delivered
30 Stock
40 Rate
50 New Stock
The [Stock] Table, initially empty, we set up with the following fields:
Stock_ID Autonumber (long integer, random) Identifier of this record
Types_ID Number Type of datum; pointer to [Types]
WK Number Week number, 1-8
Qty Number Stock level, &c. (value from spreadsheet)
Parts_ID Number Part being described; pointer to [Parts]
This is enough information to let us break out the fields from
[Spreadsheet] into individual records in the [Stock] Table. The
following Query will let us begin:
[Q_Populate_StockTable] SQL:
INSERT INTO Stock ( Types_ID, WK, Qty, Parts_ID )
SELECT Types.Types_ID,
1 AS WK, Spreadsheet.WK1,
-39783494 AS BloggsID
FROM Spreadsheet INNER JOIN Types
ON Spreadsheet.Type = Types.Type
WHERE (((Spreadsheet.WK1) Is Not Null));
In this Query, the number -39783494 is a reference to a record in the
[Parts] Table, and it will need to be set to match the part for each
LOB. Instead of using numbers like these, you will probably prefer to
use catalog numbers, or maybe incremental (instead of random)
Autonumbers. (I use random ones to help me avoid mistaking them for
other data, such as week numbers.)
Running this Query will insert the [WK1] values from [Spreadsheet] into
[Stock]. To insert the [WK2] values, we change the line
1 AS WK, Spreadsheet.WK1,
in the Query's SQL to
2 AS WK, Spreadsheet.WK2,
and run it again. We do the same for the rest of the weeks.
Having finished all that, we take a look at the [Stock] Table. There
are about 24 records, but they're not easy to read.
[Stock] Table Datasheet View:
Stock_ID Types_ID WK Qty Parts_ID
-------- -------- -- --- --------
-2114863119 20 6 2 -39783494
-1961996575 50 2 14 -39783494
-1263852112 10 6 2 -39783494
...
Tables usually aren't edited directly, so you may not care much what it
looks like, but if you need to read the contents you might want to set
up Lookup Queries to display the [Types_ID] and [Parts_ID] fields. I
did it this way, so instead of seeing a [Types_ID] value of 20, I'll see
the word "Delivered" (but that's just what's displayed; the value 20 is
what is stored in the Table):
[QL_Types] SQL:
SELECT Types.Types_ID, Types.Type
FROM Types
ORDER BY Types.Types_ID;
Here are the names corresponding to the [Types_ID] values.
[QL_Types] Query Datasheet View:
Types_ID Type
-------- ----
10 Received
20 Delivered
30 Stock
40 Rate
50 New Stock
I can similarly set up a Query for part numbers. I kept it short (to
try to avoid wrapping lines in this message), but you might want to
change it to add part of the description field. You'll probably want to
include enough information here about the part to make all the names tht
you display unique, to avoid confusion later, but without making them
too long. For now, I included the [Part Name] field and the beginning
of the supplier's name.
[QL_Parts] SQL, lookup Query for [Parts_ID]:
SELECT Parts.Parts_ID, [Parts]![Part Name]
& " " & Left$([Parts]![Supplier],10) AS Name
FROM Parts;
Well, we have only one part so far, and its short name looks like this:
[QL_Parts] Query Datasheet View:
Parts_ID Name
-------- ----
-39783494 PartX Joe Bloggs
To use these, we open [Stock] in Table Design View, select [Types_ID],
and in its Lookup tab we change the following properties:
Display Control: List Box (instead of Text Box)
Row Source: QL_Types
Column Count: 2
Column Widths: 0;1
Same for [Parts_ID], except that its Row Source is QL_Parts.
Now it's easier to read the contents of [Stock], which looks as follows.
Each of these records contains the information from one of the cells
in your original spreadsheet. This may seem wasteful of space, but each
of the 5 fields takes only 4 bytes, and you can save time in searching
and summarizing your data because all of your Excel tables of this type
can be combined into this one Access Table.
[Stock] Table Datasheet View:
Stock_ID Types_ID WK Qty Parts_ID
-------- -------- -- --- --------
-2114863119 Delivered 6 2 PartX Joe Bloggs
-1961996575 New Stock 2 14 PartX Joe Bloggs
-1263852112 Received 6 2 PartX Joe Bloggs
-1213311670 Received 4 7 PartX Joe Bloggs
-981221898 Delivered 3 3 PartX Joe Bloggs
-911258745 New Stock 6 113 PartX Joe Bloggs
-370058995 New Stock 5 113 PartX Joe Bloggs
-64211977 Stock 1 15 PartX Joe Bloggs
118615316 Rate 3 3 PartX Joe Bloggs
288441182 Rate 6 3 PartX Joe Bloggs
378117097 New Stock 4 116 PartX Joe Bloggs
663917115 Delivered 2 4 PartX Joe Bloggs
780033074 Rate 7 3 PartX Joe Bloggs
953837922 Received 2 5 PartX Joe Bloggs
975611013 New Stock 3 111 PartX Joe Bloggs
1108681864 Rate 4 3 PartX Joe Bloggs
1115436797 New Stock 1 13 PartX Joe Bloggs
1146316863 Stock 3 100 PartX Joe Bloggs
1166745803 New Stock 7 110 PartX Joe Bloggs
1254455811 Delivered 4 2 PartX Joe Bloggs
1273280096 Rate 2 3 PartX Joe Bloggs
1587311765 New Stock 8 107 PartX Joe Bloggs
1604453262 Delivered 1 2 PartX Joe Bloggs
1886097596 Rate 5 3 PartX Joe Bloggs
1949734756 Rate 8 3 PartX Joe Bloggs
1966410860 Rate 1 3 PartX Joe Bloggs
At this point, you're probably wondering how you could use this Table to
do anything useful. To begin with, you can use the following Query to
display the contents of [Stock] in a datasheet similar to your original
Excel spreadsheet.
[Stock_Crosstab] SQL:
TRANSFORM First(Stock.Qty) AS FirstOfValue
SELECT Stock.Parts_ID, Stock.Types_ID
FROM Types INNER JOIN Stock
ON Types.Types_ID = Stock.Types_ID
GROUP BY Stock.Parts_ID, Stock.Types_ID, Types.Types_ID
PIVOT "WK" & [WK];
I included the [Parts_ID] field here because, in a Report, you're likely
to want to know what kind of part you're reporting on, and your Reports
can be based on Queries like this one. (To avoid having the newsreader
wrap the lines, I put the last 4 fields in a separate section at the end.)
[Stock_Crosstab] Table Datasheet View:
Parts_ID Types_ID WK1 WK2 WK3 WK4 ...
---------------- -------- --- --- --- ---
PartX Joe Bloggs Received 5 7
PartX Joe Bloggs Delivered 2 4 3 2
PartX Joe Bloggs Stock 15 100
PartX Joe Bloggs Rate 3 3 3 3
PartX Joe Bloggs New Stock 13 14 111 116
(the remaining fields follow
WK5 WK6 WK7 WK8
--- --- --- ---
2
2
3 3 3 3
113 113 110 107
Notice that, with your data in the [Stock] Table, you no longer need to
worry about what to do with empty fields such as those listed under
[WK5]. Also, searching for the highest or lowest [New Stock] value in
your database is far easier than if your data are organized as in your
original spreadsheet layout; only one field in [Stock] would need to be
searched, instead of several in [Spreadsheet].
When you are satisfied that your data are correctly imported into
[Stock], you can erase the [Spreadsheet] Table and the
[Q_Populate_StockTable] Query.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
