This is an Excel workbook
On each sheet are orders for custom buildings in 2018.
the day of the month the order was taken,
who ordered it,
what they ordered,
and its price.
Hi, this is crystal
Each month is on a different sheet.
All the sheets appear to have the same columns.
But some months appear to be missing
The company has a workbook like this
for each year they've been in business,
although the columns weren't always
the same as they are now.
To make the data more flexible,
we will put this information into Access.
Here is an empty Access database,
ready for data.
Let's import a spreadsheet from Excel.
The workbook has to be closed to import,
but before it is closed, let's copy the path and filename
so its quicker when we pick the file later in Access.
Press Alt-F11 to switch to the VBE, Visual Basic Editor.
Then press Ctl-G to Goto the debuG window, and type
? ActiveWorkbook.Fullname
if Intellisense doesn't pop up,
press Ctl-Spacebar.
As you type, the Intellisense list moves.
You can also move it by clicking,
or pressing the up/down arrow keys.
When what you want is highlighted,
press TAB to accept it and then continue typing.
After pressing . (dot)
to separate the object from the property,
Intellisense prompts us with many of the valid choices.
That makes it easy to find Fullname if we just remember 'full'
In the debug window,
the line starts with a question mark
because we want information back,
Now press ENTER to execute this command
Here is the path and filename of the workbook that is active.
We can select it, and press Ctl-C to copy it.
Now we close everything in Excel.
Back in Access,
we don't have anything in the database yet.
To import, click on the EXTERNAL DATA ribbon tab,
and then the Excel icon.
If you don't see the Excel icon
your ribbons may be a little different.
Instead of this More icon,
you may have something called New Data Source.
Click on the dropdown and choose whatever is
not specifically listed on the ribbon from there.
When the Get External Data dialog box pops up,
select what is in the filename box and
paste the path and filename
of the Excel spreadsheet you want.
That's what we copied from the debug window in Excel.
Click OK to advance to the next dialog box.
On the top is a list of the sheet names,
or you can choose to list range names.
We will import the first sheet.
The bottom pane shows the columns of data.
This what we want.
Click Next.
"First Row Contains Column Headings" is checked,
and that is what we want, so click Next.
Now we can change data types.
Choose Integer for the day of the month.
We change this because the bad thing about double precision numbers,
aside from taking more space to store than what we need,
is that they are stored in scientific notation,
also called exponential format.
They have a floating decimal point and are not exact,
but you can put some really big,
and small, numbers in there.
You generally don't want to store any whole number as Double,
unless it's too big for Integer, Long Integer, or Currency,
which can be bigger and is also a precise data type,
even though it does have 4 decimal places.
Integers can be plus or minus 32K.
Since the longest month only has 31 days,
that's a good choice for data type.
If data is text, notice it lines up on the left side of the column.
Even though dates are stored internally as numbers,
the formatting shows it on the left like text.
Then comes the suggested retail price,
which is currency, and then a note.
You can change more data types,
but the rest look okay.
What we don't have is the ability
to set other properties here,
like Size and Format
that also need to be considered.
Click Next.
Now you can name the table.
By default, it will be the sheet name.
Preface the sheet name with "import_" and click Finish.
We won't save these steps, so click Close.
Now let's see what we've got.
The import_Jan table comes in with all the columns being equal width.
So first let's select the columns and best-fit.
Also, we'll get rid of the Add New Field column on the end.
I have an icon on my quick Access toolbar to make this quick.
You can also right-click on a column label,
and choose Unhide Fields... from the shortcut menu,
even though what we might want to do is hide fields.
This shows them all.
Then you can check what you want to see,
and uncheck what you don't.
Click close when you're done.
Now let's look at the columns.
ID is an AutoNumber assigned by Access during the import.
By default, it is sequential, starting with 1.
Dy is the day part of a date.
It's an integer ... remember we changed that
from Double Precision in the wizard when data was imported?
The rest of the date is in the sheet name that the data came from,
and the year is in file name for the Excel workbook.
We'll have to add a real date field, and fill in the data.
Code is the customer code that corresponds to the customer name.
If the customer is a company or organization, the Type field is filled out.
A metal plate with the serial number is attached to each product.
Price is what the customer paid.
Next is the manufacture date,
and then the manufacturer's suggested price, MSP
which we will be calling SRP for suggested retail price,
as we may change from what the manufacturer suggests.
The last column has notes.
We need to turn the day of the month into a date.
There is a function we can use called DateSerial,
which constructs a date from
numeric year, month, and day values.
We need a place to put it, so let's create another field.
Right-click on the window title
and choose Design view from the shortcut menu.
We'll put the new field right above the day.
Right-click on the record selector
in front of the day field
and choose Insert Rows.
The fieldname is dtOrder.
Press TAB to choose the data type.
Typing D chooses Date/Time,
then press TAB to go to the description.
It is good to fill the descriptions out.
that is what will be used for the
Status Bar Text that shows up in the lower left
when you're on a form,
and also if you look at the datasheet view of the table.
Now we save the changes and switch back to the data sheet view.
When you add new fields, they appear at the end of the layout
if you have already made some layout changes
such as resizing or moving columns,
and saved the layout.
That's ok. We'll move the column.
Click in the column header
to select the column, and let go of the mouse.
Then click in the column header again
and drag the column to its new location.
We'll put date next to day.
You can look at the day,
the table name,
and remember that the year is 2018,
and start manually filling the dates,
... but there is a faster way.
Let's make an update query
Click on the CREATE ribbon tab
and choose Query Design.
The Show Table dialog box pops up.
You could pick the table there,
but I generally close this,
and drag what I want from the Navigation Pane.
This database only has one table,
so its pretty easy to pick the table.
But if you had a lot of tables,
you might have the navigation pane filtered to
objects that just have something particular in their name,
to make them easier to see and quicker to pick.
Once the field list is showing, I like to resize it to show all its fields,
if there aren't a ton of them.
Double-click the day field to put it on the grid.
Now let's make the second column wider
since we will write a formula to calculate a real date.
The name will be dtOrder,
so we type that and then a colon.
Picking fields will be easier with the expression builder,
so press Ctl-F2.
You can click where you write text,
press the Ctrl key on your keyboard,
and then twirl the Mouse wheel to resize text.
I'm making it bigger.
We'll use the DateSerial Function.
Just as in the immediate window,
or the debug window,
we have Intellisense here too.
Type the function name and then open parenthesis.
First it wants a numeric year.
That will be 2018.
Then type a comma.
The month is 1 for January.
Then type a comma
The day will come from the field called Dy.
The square brackets denote that it's a field name.
and now, close parenthesis, and then OK.
Look at the data sheet view.
The equation is working good,
It turns our day of the month into a real date.
Back in design view,
let's add the order date to the grid,
so that it only shows if it Is Null,
or needs to be filled out.
Our calculated field shouldn't have the same name
as one of the other fields,
so let's add "Calc" to the end of that calculated field name.
the datasheet view shows there are
5 records with no order date.
Looking at the import table,
we can confirm this the correct.
Now back to the query design.
We've selected what we want,
we've tested our formula, and now
let's change this into an update query.
The DESIGN ribbon tab shows the Query Type group.
In there, you see the Update query icon.
This is called an action query since it changes data.
There are other types of action queries, and queries:
you can use the results of a query to make a table,
you can append records to a table,
and more.
The most common type of a query is a Select query,
where you select the data you want to see.
When the query type changes,
what is collected on the grid changes too.
Now there is a row called Update To
Copy the expression to calculate the date
and paste it in the Update To cell under the order date.
We are already using criteria to filter out the ones that are already filled.
Now we execute the query
by clicking the DESIGN ribbon tab
and choosing Run!
5 rows will be updated.
When we look at the import table again,
we can see that the dates are now all filled out.
We will continue importing from Excel in another lesson.
We will also discuss how the data should be structured in Access
To summarize,
you learned how to import data from Excel using the wizard,
how to change data types,
add another field,
and use an update query to fill it in.
Thanks for joining me.
through sharing, we will all get better.