Northwind Database

[RibGnaw]

Long, long ago when Access 97 first hit the streets a sample database
called Northwind was shipped with it. At that time a number of
articles had been posted on the correct procedure to remove all the
sample data. Does anyone out there still have this information?

[fredg]

Re: Northwind Database

On 12 Sep 2004 13:24:58 -0700, RibGnaw wrote:
[color=blue]
> Long, long ago when Access 97 first hit the streets a sample database
> called Northwind was shipped with it. At that time a number of
> articles had been posted on the correct procedure to remove all the
> sample data. Does anyone out there still have this information?[/color]

Create and name a brand new database.
Click on File + Get External Data + Import.
Navigate to the Northwind database.
Select it.
When the Import Dialog opens ...
On each tab, click on Select All.
Then click on Options.
In the Import area click on Relationships.
In the Import Table area click on Definition Only.
Click OK.
Save the new database.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[RibGnaw]

Re: Northwind Database

fredg <fgutkind@examp le.invalid> wrote in message news:<di5nybnd9 wa6.34uuvuyehfx r$.dlg@40tude.n et>...[color=blue]
> On 12 Sep 2004 13:24:58 -0700, RibGnaw wrote:
>[color=green]
> > Long, long ago when Access 97 first hit the streets a sample database
> > called Northwind was shipped with it. At that time a number of
> > articles had been posted on the correct procedure to remove all the
> > sample data. Does anyone out there still have this information?[/color]
>
> Create and name a brand new database.
> Click on File + Get External Data + Import.
> Navigate to the Northwind database.
> Select it.
> When the Import Dialog opens ...
> On each tab, click on Select All.
> Then click on Options.
> In the Import area click on Relationships.
> In the Import Table area click on Definition Only.
> Click OK.
> Save the new database.[/color]

Many thanks Fred...

[RibGnaw]

Re: Northwind Database

mark.bradwell@f reenet.co.uk (RibGnaw) wrote in message news:<26bab8dd. 0409141246.4fb9 9f66@posting.go ogle.com>...[color=blue]
> fredg <fgutkind@examp le.invalid> wrote in message news:<di5nybnd9 wa6.34uuvuyehfx r$.dlg@40tude.n et>...[color=green]
> > On 12 Sep 2004 13:24:58 -0700, RibGnaw wrote:
> >[color=darkred]
> > > Long, long ago when Access 97 first hit the streets a sample database
> > > called Northwind was shipped with it. At that time a number of
> > > articles had been posted on the correct procedure to remove all the
> > > sample data. Does anyone out there still have this information?[/color]
> >
> > Create and name a brand new database.
> > Click on File + Get External Data + Import.
> > Navigate to the Northwind database.
> > Select it.
> > When the Import Dialog opens ...
> > On each tab, click on Select All.
> > Then click on Options.
> > In the Import area click on Relationships.
> > In the Import Table area click on Definition Only.
> > Click OK.
> > Save the new database.[/color]
>
> Many thanks Fred...[/color]

Umm, after trying the method above I now get the following error
whenever the ORDERS button is pressed from the main switchboard " The
Microsoft Jet database engine cannot find the input table or query
'Orders Qry' Make sure it exist and that its name is spelt correctly.

I have checked that all the tables and queries etc that are present in
Nwind are also in the new database, but there does not appear to be a
query named 'Orders'.

Anyone got any ideas on what may be causing this, or alternatively
anyone got the Nwind database without the data that is fully
functional?

[fredg]

Re: Northwind Database

On 15 Sep 2004 15:52:44 -0700, RibGnaw wrote:
[color=blue]
> mark.bradwell@f reenet.co.uk (RibGnaw) wrote in message news:<26bab8dd. 0409141246.4fb9 9f66@posting.go ogle.com>...[color=green]
>> fredg <fgutkind@examp le.invalid> wrote in message news:<di5nybnd9 wa6.34uuvuyehfx r$.dlg@40tude.n et>...[color=darkred]
>>> On 12 Sep 2004 13:24:58 -0700, RibGnaw wrote:
>>>
>>> > Long, long ago when Access 97 first hit the streets a sample database
>>> > called Northwind was shipped with it. At that time a number of
>>> > articles had been posted on the correct procedure to remove all the
>>> > sample data. Does anyone out there still have this information?
>>>
>>> Create and name a brand new database.
>>> Click on File + Get External Data + Import.
>>> Navigate to the Northwind database.
>>> Select it.
>>> When the Import Dialog opens ...
>>> On each tab, click on Select All.
>>> Then click on Options.
>>> In the Import area click on Relationships.
>>> In the Import Table area click on Definition Only.
>>> Click OK.
>>> Save the new database.[/color]
>>
>> Many thanks Fred...[/color]
>
> Umm, after trying the method above I now get the following error
> whenever the ORDERS button is pressed from the main switchboard " The
> Microsoft Jet database engine cannot find the input table or query
> 'Orders Qry' Make sure it exist and that its name is spelt correctly.
>
> I have checked that all the tables and queries etc that are present in
> Nwind are also in the new database, but there does not appear to be a
> query named 'Orders'.
>
> Anyone got any ideas on what may be causing this, or alternatively
> anyone got the Nwind database without the data that is fully
> functional?[/color]

I followed my instructions above and the Orders Form does have as it's
record source "Orders Qry".

There is no actual "Orders Qry" query as such.
There is the record source SQL, used in the Orders form, that has been
named "Orders Qry".

Open the Orders form in design view.
Look at the the record source property. It may or may not actually say
'Orders Qry'. If it does, click on the button with the 3 dots that
appears on that line.
You should have a Query grid with the Customer table and the Order
Table showing. Click on the bang and all of the order records should
appear (of course after you have entered some records).

If there is nothing there, write Orders Qry on the Record Source line.
Then click on the button with the 3 dots.
Next add the 2 tables. Make sure the relationship is Customer Table
CustomerID 1 side to Order Table CustomerID many side.

Drag all of the Order table fields onto the grid.
Drag all of the Customer table fields onto the grid EXCEPT Fax, Phone,
ContactName, ContactID, and CompanyID.

Save the query.
The record source should now say Order Qry and the query is as above.

Save the form.
Enter sample data in the other tables as needed.
Then open the Orders Form.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[RibGnaw]

Re: Northwind Database

fredg <fgutkind@examp le.invalid> wrote in message news:<ucunku82l r3a$.qe5k5zr8kw 83$.dlg@40tude. net>...[color=blue]
> On 15 Sep 2004 15:52:44 -0700, RibGnaw wrote:
>[color=green]
> > mark.bradwell@f reenet.co.uk (RibGnaw) wrote in message news:<26bab8dd. 0409141246.4fb9 9f66@posting.go ogle.com>...[color=darkred]
> >> fredg <fgutkind@examp le.invalid> wrote in message news:<di5nybnd9 wa6.34uuvuyehfx r$.dlg@40tude.n et>...
> >>> On 12 Sep 2004 13:24:58 -0700, RibGnaw wrote:
> >>>
> >>> > Long, long ago when Access 97 first hit the streets a sample database
> >>> > called Northwind was shipped with it. At that time a number of
> >>> > articles had been posted on the correct procedure to remove all the
> >>> > sample data. Does anyone out there still have this information?
> >>>
> >>> Create and name a brand new database.
> >>> Click on File + Get External Data + Import.
> >>> Navigate to the Northwind database.
> >>> Select it.
> >>> When the Import Dialog opens ...
> >>> On each tab, click on Select All.
> >>> Then click on Options.
> >>> In the Import area click on Relationships.
> >>> In the Import Table area click on Definition Only.
> >>> Click OK.
> >>> Save the new database.
> >>
> >> Many thanks Fred...[/color]
> >
> > Umm, after trying the method above I now get the following error
> > whenever the ORDERS button is pressed from the main switchboard " The
> > Microsoft Jet database engine cannot find the input table or query
> > 'Orders Qry' Make sure it exist and that its name is spelt correctly.
> >
> > I have checked that all the tables and queries etc that are present in
> > Nwind are also in the new database, but there does not appear to be a
> > query named 'Orders'.
> >
> > Anyone got any ideas on what may be causing this, or alternatively
> > anyone got the Nwind database without the data that is fully
> > functional?[/color]
>
> I followed my instructions above and the Orders Form does have as it's
> record source "Orders Qry".
>
> There is no actual "Orders Qry" query as such.
> There is the record source SQL, used in the Orders form, that has been
> named "Orders Qry".
>
> Open the Orders form in design view.
> Look at the the record source property. It may or may not actually say
> 'Orders Qry'. If it does, click on the button with the 3 dots that
> appears on that line.
> You should have a Query grid with the Customer table and the Order
> Table showing. Click on the bang and all of the order records should
> appear (of course after you have entered some records).
>
> If there is nothing there, write Orders Qry on the Record Source line.
> Then click on the button with the 3 dots.
> Next add the 2 tables. Make sure the relationship is Customer Table
> CustomerID 1 side to Order Table CustomerID many side.
>
> Drag all of the Order table fields onto the grid.
> Drag all of the Customer table fields onto the grid EXCEPT Fax, Phone,
> ContactName, ContactID, and CompanyID.
>
> Save the query.
> The record source should now say Order Qry and the query is as above.
>
> Save the form.
> Enter sample data in the other tables as needed.
> Then open the Orders Form.[/color]

Getting nearer,
The Order now works and the customer information is taken from the
customer table.
Now I have another problem, in the Products form, when you press
either the Preview Product List or Output to HTML buttons the error
appears that it can't find the 'Alphabetical list of Products'I tried
the method of ensuring that it pointed to the report, after checking
what the original Nwind database pointed to and they are the same.
Did the import action miss something important that I can copy from
the original?
Thanks.

[fredg]

Re: Northwind Database

On 16 Sep 2004 15:05:40 -0700, RibGnaw wrote:
[color=blue]
> fredg <fgutkind@examp le.invalid> wrote in message news:<ucunku82l r3a$.qe5k5zr8kw 83$.dlg@40tude. net>...[color=green]
>> On 15 Sep 2004 15:52:44 -0700, RibGnaw wrote:
>>[color=darkred]
>>> mark.bradwell@f reenet.co.uk (RibGnaw) wrote in message news:<26bab8dd. 0409141246.4fb9 9f66@posting.go ogle.com>...
>>>> fredg <fgutkind@examp le.invalid> wrote in message news:<di5nybnd9 wa6.34uuvuyehfx r$.dlg@40tude.n et>...
>>>>> On 12 Sep 2004 13:24:58 -0700, RibGnaw wrote:
>>>>>
>>>>> > Long, long ago when Access 97 first hit the streets a sample database
>>>>> > called Northwind was shipped with it. At that time a number of
>>>>> > articles had been posted on the correct procedure to remove all the
>>>>> > sample data. Does anyone out there still have this information?
>>>>>
>>>>> Create and name a brand new database.
>>>>> Click on File + Get External Data + Import.
>>>>> Navigate to the Northwind database.
>>>>> Select it.
>>>>> When the Import Dialog opens ...
>>>>> On each tab, click on Select All.
>>>>> Then click on Options.
>>>>> In the Import area click on Relationships.
>>>>> In the Import Table area click on Definition Only.
>>>>> Click OK.
>>>>> Save the new database.
>>>>
>>>> Many thanks Fred...
>>>
>>> Umm, after trying the method above I now get the following error
>>> whenever the ORDERS button is pressed from the main switchboard " The
>>> Microsoft Jet database engine cannot find the input table or query
>>> 'Orders Qry' Make sure it exist and that its name is spelt correctly.
>>>
>>> I have checked that all the tables and queries etc that are present in
>>> Nwind are also in the new database, but there does not appear to be a
>>> query named 'Orders'.
>>>
>>> Anyone got any ideas on what may be causing this, or alternatively
>>> anyone got the Nwind database without the data that is fully
>>> functional?[/color]
>>
>> I followed my instructions above and the Orders Form does have as it's
>> record source "Orders Qry".
>>
>> There is no actual "Orders Qry" query as such.
>> There is the record source SQL, used in the Orders form, that has been
>> named "Orders Qry".
>>
>> Open the Orders form in design view.
>> Look at the the record source property. It may or may not actually say
>> 'Orders Qry'. If it does, click on the button with the 3 dots that
>> appears on that line.
>> You should have a Query grid with the Customer table and the Order
>> Table showing. Click on the bang and all of the order records should
>> appear (of course after you have entered some records).
>>
>> If there is nothing there, write Orders Qry on the Record Source line.
>> Then click on the button with the 3 dots.
>> Next add the 2 tables. Make sure the relationship is Customer Table
>> CustomerID 1 side to Order Table CustomerID many side.
>>
>> Drag all of the Order table fields onto the grid.
>> Drag all of the Customer table fields onto the grid EXCEPT Fax, Phone,
>> ContactName, ContactID, and CompanyID.
>>
>> Save the query.
>> The record source should now say Order Qry and the query is as above.
>>
>> Save the form.
>> Enter sample data in the other tables as needed.
>> Then open the Orders Form.[/color]
>
> Getting nearer,
> The Order now works and the customer information is taken from the
> customer table.
> Now I have another problem, in the Products form, when you press
> either the Preview Product List or Output to HTML buttons the error
> appears that it can't find the 'Alphabetical list of Products'I tried
> the method of ensuring that it pointed to the report, after checking
> what the original Nwind database pointed to and they are the same.
> Did the import action miss something important that I can copy from
> the original?
> Thanks.[/color]

The record source for the Preview Product List is done the same way as
the orders form.
Name the record source property of the "Alphabetic al List of Products"
report
Alphabetical List of Products

Here is the actual SQL:

SELECT DISTINCTROW Products.*, Categories.Cate goryName
FROM Categories INNER JOIN Products ON Categories.Cate goryID =
Products.Catego ryID
WHERE (((Products.Dis continued)=No)) ;

I have no idea why these didn't copy over when you imported the
objects from the Northwind database.

As you practice a bit more, if you find this kind of error again,
check that the record source of the report is there. You can compare
it against your full Northwind database for the actual SQL.

If it was me, I would actually create a named query using that above
SQL and simply set the report record source to that query, as is done
in other reports.

Remember, Northwind is an example database, not necessarily an
efficient fully functional one. It uses different methods to
accomplish the same goal. For instance, the Output to HTML report
button uses DoCmd.OpenRepor t in it's click event, while the Preview
button uses a Hyperlink SubAddress to open the same report in a
different view.

By the way, Access ships with several other database templates (File +
New + General Database + Databases tab), as well as a Solutions.mdb
and an Orders.mdb. It would be worth your while to examine these
others as well as Northwind.
They should all be on your computer (or on your program CD).
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Larry Linson]

Re: Northwind Database

"fredg" wrote
[color=blue]
> By the way, Access ships with several
> other database templates (File +
> New + General Database + Databases
> tab), as well as a Solutions.mdb
> and an Orders.mdb. It would be worth
> your while to examine these others as
> well as Northwind.
> They should all be on your computer
> (or on your program CD).[/color]

Fred, since Access 2000, it has been necessary to download the
"Solutions. MDB" -- and then it disappeared from Microsoft's site during some
"redecorati ng" there. It is currently, temporarily housed at
http://www.mvps.org/access/downloads/bapp2000.exe.

I concur that it is very, very useful as a source of "how do I..."
information.

Larry Linson
Microsoft Access MVP

[RibGnaw]

Re: Northwind Database

"Larry Linson" <bouncer@localh ost.not> wrote in message news:<L8N2d.391 $Co1.272@trnddc 02>...[color=blue]
> "fredg" wrote
>[color=green]
> > By the way, Access ships with several
> > other database templates (File +
> > New + General Database + Databases
> > tab), as well as a Solutions.mdb
> > and an Orders.mdb. It would be worth
> > your while to examine these others as
> > well as Northwind.
> > They should all be on your computer
> > (or on your program CD).[/color]
>
> Fred, since Access 2000, it has been necessary to download the
> "Solutions. MDB" -- and then it disappeared from Microsoft's site during some
> "redecorati ng" there. It is currently, temporarily housed at
> http://www.mvps.org/access/downloads/bapp2000.exe.
>
> I concur that it is very, very useful as a source of "how do I..."
> information.
>
> Larry Linson
> Microsoft Access MVP[/color]

Thanks for the help guys, not managed to sort the problem yet but will keep trying.

Mark.