Help Please -- Importing from excel to access

[jwrnana]

I have never tried this so please bear with me.

I have a database in access.
I will be receiving information via email in excel worksheet format.
I would like to import that information into my access database.

Can I import into a query? or does it HAVE to be into a table?

If only into a table, my information needs to be in more than one table, but
they are linked in my database. Can this be done? The information needs
to be "Control Number" specific. In otherwords, I will have many many
control numbers that contain the same fields that I am importing. The
information needs to go to the correct control number.

My reason for using this method is that I am trying to eliminate the chance
of errors that occur with key punching and cut and paste procedures, and
have the computer import the information for me.

I am using Access 2003, Excel 2003, and Windows XP Pro.

I probably did not explain myself very well. Please ask questions. Your
suggestions and assistance is greatly appreciated.

JR

 

[John Nurick]

You can only import into tables, because they are the only places where
data is stored. But you can use queries as you import to control just
what gets imported and where it the data goes.

Often the best way to work is to link to the Excel data rather than
import it (File|Get External Data|Link). This gives you a linked table
in Access that is connected to the Excel worksheet. You then use append
or update queries to move data from the linked table to your actual
tables.

For instance, it sounds as if you need to update existing records that
relate to particular "control numbers" (whatever they are). If so, you'd
create an update query that joins the linked table to another table on
the control number field, and updates fields in the other table with
values from the corresponding fields in the linked table.

Or if records in the linked table need to be added to the other table,
you'd use an append query.

 

[jwrnana]

John - Thank you for your prompt reply. May I give you a bit more
information so that I can be sure that I am doing this correctly?

My database is sales oriented. I have control numbers for each customer
(not the auto id that is assigned from the customer table). Each control
number relates to a specific purchase order for that customer. The order
detail contains the products and their description and the serial numbers
for each product. When I get a purchase order, I assign the control number,
enter the info into my database, run a query that contains all information
pertaining to that specific control number, analyze with Microsoft Excel and
email to my dealer who makes delivery from the info emailed to him. When
the dealer delivers, he emails back to me the items that I need to add to my
access database; i.e. his vendor invoice number, his cost of the products
delivered, delivery date, and serial numbers. At the present time, he
inserts the information into the emailed info, and sends back to me.

Since I have never done an update query nor an append query, I felt that the
above info would be useful to answer the balance of my questions.
The order may have many products on it. Each product will have all
the info I need specific to that particular control number.
The info sent to the dealer will say, for instance - 2 of a widget.
The dealer will reply with an invoice number and serial number for EACH
widget. My emailed excel info only has space for 1 set of his info. I have
not found a way to insert more spaces on an as needed basis other than
manually adding to the excel spreadsheet before I email to him.

Can you, again, guide me through the process of what I need to do? Many
thanks, JR

 

[jwrnana]

Sorry - I omitted a question.

There are 100 dealers that are sending separate emails back to me. Is there
a way to "save as" that info into the SAME worksheet that is linked to
access, or would I need to set up link each time? Once I use the info I
would not want to update/append over and over again and possibly get
duplicates.

 

[John Nurick]

This is raising more questions than answers. What tables do you have,
what are their primary keys, and how are they related?

Is the control number the unique identifier of a purchase order? At one
point it seems to be, when you say "each control number relates to a
specific purchase order for that customer" - but later you say "Each
product will have all the info I need specific to that particular
control number", which suggests that the control number relates to a
product rather than a purchase order.

About the query "that contains all information pertaining to [a]
specific control number"? Is it a parameter query? if not, where does it
get the control number from? Please paste the SQL statement (from the
SQL view of the query) into your next message.

As for the serial numbers: Does every product you order have a serial
number, or only some of them? Presumably you have (or need) a 1:M
relationship between "order detail" records and "products delivered".
This would allow you to track which individual items were delivered
against which purchase order.

Presumably you'd need something like that in any case to cover the
situation where (e.g.) you tell the dealer to deliver 10 x Acme Widgets
Model 99, but he only has two in stock. So he delivers those, and orders
8 from his supplier, who only has 4 in stock, which are delivered a few
days later, while the remaining 4 have to be back ordered.

 

[jwrnana]

Replies below. As I am answering you, I think that I am indeed raising more
questions than even the ones you asked.

This is raising more questions than answers. What tables do you have,
what are their primary keys, and how are they related?

Tablesealer -( Primary Key = DealerID) DealerID to Orders;
1 to many
Orders - ( Primary Key = OrdersID) CustomerID to
Customers
Customers-( Primary Key = CustomerID)
CustomerID to Order Detail; 1 to many
Order Details - (Primary Key = OrderDetailsID)
Product ID to Products
Products - (Primary Key = Products) Contract ID to
Contracts
Contracts - (Primary Key = ContractID) Contracts to
Products; 1 to many
Sin - (Primary Key = SINID) SinID to Products

The above relationships are the only ones showing up on my tables in the
query. When I look at all table relationships, I get the following:
Orders to Customer - not directly related
Order Details to Products - not directly related
Products to Contracts - not directly related
SIN to Products - not directly related

I have more tables that the ones listed above. The ones above are the ones
pertaining to this query.

Is the control number the unique identifier of a purchase order?

Yes, each purchase order is manually assigned a control number.

At one point it seems to be, when you say "each control number relates to
a

specific purchase order for that customer" - but later you say "Each
product will have all the info I need specific to that particular
control number", which suggests that the control number relates to a
product rather than a purchase order.

No - control number relates to a purchase order wherein the customer
purchases products under that control number. Sorry for my
inability to explain clearly.

About the query "that contains all information pertaining to [a]
specific control number"? Is it a parameter query? if not, where does it
get the control number from? Please paste the SQL statement (from the
SQL view of the query) into your next message.
My present query is a select query. The control number comes from

the customer table; this number is manually assigned in sequencial
order.

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv AS [JD Invoice #],
Dealer.[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));

Within this long SQL, you will see JD Invoice#, JDInv$,SerialNum,
ShipDate. This is the information that the dealer will insert into the
excel worksheet. At the present time, I have attempted to create a
template in excel that would put all of this information in legible format
since my forms and reports will not email and allow protection of all
but these 4 cells and keep the format. I have been trying to find a way to
do what we are talking about now. I must transmit all this information
to the dealer in order that he make delivery. I could omit these 4 fields
and have them in a separate worksheet to link with access.

As for the serial numbers: Does every product you order have a serial
number, or only some of them?

Some of them


Presumably you have (or need) a 1:M

 

[John Nurick]

Either I don't understand what you're doing, or there are some serious
problems with your data structure.

The SQL statement from your query shows that ControlNum is a field in
the Customers table, and not - as you stated - a unique identifier of a
purchase order. If PONumber stands for "purchase order", it's very odd
to have this field in the Customers table: normally it would go in the
Orders table to link your Order record to the purchase order issued by
the Customer. But maybe no one ever does business with you a second
time<g>.

By the way, when you use the term "purchase order" do you mean the
entity modelled by your Orders table, or are "purchase orders" and
"orders" different things?

Also, your JD Invoice Number and JD Invoice Amount fields are in the
Dealer table. This makes no sense, unless you never use a Dealer more
than once.

And there doesn't seem to be anywhere to store the serial numbers you
want the dealer to send you.

It's always best to get the data structure right before trying to do the
fancy stuff.

Replies below. As I am answering you, I think that I am indeed raising more
questions than even the ones you asked.

This is raising more questions than answers. What tables do you have,
what are their primary keys, and how are they related?

Tablesealer -( Primary Key = DealerID) DealerID to Orders;
1 to many
Orders - ( Primary Key = OrdersID) CustomerID to
Customers
Customers-( Primary Key = CustomerID)
CustomerID to Order Detail; 1 to many
Order Details - (Primary Key = OrderDetailsID)
Product ID to Products
Products - (Primary Key = Products) Contract ID to
Contracts
Contracts - (Primary Key = ContractID) Contracts to
Products; 1 to many
Sin - (Primary Key = SINID) SinID to Products

The above relationships are the only ones showing up on my tables in the
query. When I look at all table relationships, I get the following:
Orders to Customer - not directly related
Order Details to Products - not directly related
Products to Contracts - not directly related
SIN to Products - not directly related

I have more tables that the ones listed above. The ones above are the ones
pertaining to this query.

Is the control number the unique identifier of a purchase order?

Yes, each purchase order is manually assigned a control number.

At one point it seems to be, when you say "each control number relates to
a

specific purchase order for that customer" - but later you say "Each
product will have all the info I need specific to that particular
control number", which suggests that the control number relates to a
product rather than a purchase order.

No - control number relates to a purchase order wherein the customer
purchases products under that control number. Sorry for my
inability to explain clearly.

About the query "that contains all information pertaining to [a]
specific control number"? Is it a parameter query? if not, where does it
get the control number from? Please paste the SQL statement (from the
SQL view of the query) into your next message.
My present query is a select query. The control number comes from

the customer table; this number is manually assigned in sequencial
order.

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv AS [JD Invoice #],
Dealer.[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));

Within this long SQL, you will see JD Invoice#, JDInv$,SerialNum,
ShipDate. This is the information that the dealer will insert into the
excel worksheet. At the present time, I have attempted to create a
template in excel that would put all of this information in legible format
since my forms and reports will not email and allow protection of all
but these 4 cells and keep the format. I have been trying to find a way to
do what we are talking about now. I must transmit all this information
to the dealer in order that he make delivery. I could omit these 4 fields
and have them in a separate worksheet to link with access.

As for the serial numbers: Does every product you order have a serial
number, or only some of them?

Some of them


Presumably you have (or need) a 1:M

relationship between "order detail" records and "products delivered".
This would allow you to track which individual items were delivered
against which purchase order.
You are correct in your assumption.

Presumably you'd need something like that in any case to cover the
situation where (e.g.) you tell the dealer to deliver 10 x Acme Widgets
Model 99, but he only has two in stock. So he delivers those, and orders
8 from his supplier, who only has 4 in stock, which are delivered a few
days later, while the remaining 4 have to be back ordered.

 

[jwrnana]

Like I said -- I am a novice. Please see below.
Brief flow chart:
1. Purchase order received from customer.
2. Purchase order is assigned a control number and dealer.
3. Information is entered into my Orders form, which creates an OrderID
that I use for customer invoicing.
4. I transmit via email all of the information contained on the written
purchase order to the dealer for delivery.
5. When dealer delivers, he emails back to me -
a. Ship Date
b. Serial Numbers for that purchase order
c. His vendor invoice number(s)
d. His vendor cost(s) per invoice number
6. When I receive delivery info, I input, either manually or if
possible have computer link excel to access, the info in 5 above. This
information is forwarded via email (all of my SQL info) to corporate. That
is corporate's signal to pay dealer his share based on purchase order amount
and his costs.
7. I generate an invoice to the customer for payment.

Either I don't understand what you're doing, or there are some serious
problems with your data structure.

The SQL statement from your query shows that ControlNum is a field in
the Customers table, and not - as you stated - a unique identifier of a
purchase order. If PONumber stands for "purchase order", it's very odd
to have this field in the Customers table: normally it would go in the
Orders table to link your Order record to the purchase order issued by
the Customer. But maybe no one ever does business with you a second
time<g>.

I put the ControlNum in the Customers table because I did
not know how to do otherwise. It is possible to have a repeat customer; but
our business is with the government and, as a rule, is not the same
customer. The ControlNum is "my" main point of control. I receive a
purchase order from the government and assign that customer a control
number. Whenever I am searching for any information pertaining to the
purchase order, we use ControlNum rather than the government purchase order
number.

By the way, when you use the term "purchase order" do you mean the
entity modelled by your Orders table, or are "purchase orders" and
"orders" different things?

Purchase orders are received from the customer. Orders are
created when I enter the information from the purchase order.

Also, your JD Invoice Number and JD Invoice Amount fields are in the
Dealer table. This makes no sense, unless you never use a Dealer more
than once.

Again, I was uncertain as to where to put that information.
Yes, the dealer will be used many times. When I receive a "purchase order",
I assign a control number (unique to the purchase order) and dealer, and
email this via my excel template to the dealer. The dealer -- once delivery
is completed -- emails back either the form with my 4 fields completed -- or
can send info back in a separate worksheet. (This is where we began.)
When, and only when, the dealer lets me know that he has delivered AND sent
me the 4 pertinent items needed, can he receive payment for the delivery.
The way he receives payment is that I must transmit all of the info in the
SQL above - along with the 4 fields completed - to corporate and they issue
credit.

And there doesn't seem to be anywhere to store the serial numbers you
want the dealer to send you.

The only place that they will be visible is in my OrderDetails.
I will manually input the serial numbers sent back to me via dealer into the
original order entry form. I am using the Order ID as my invoice number
sent to the customer for payment.

It's always best to get the data structure right before trying to do the
fancy stuff.

I began this project with absolutely no idea of what or how this
was to be accomplished. Thus, my tables leave room for improvement.Again, thank you for your patience and willingness to help me. I
have suddenly run out of time in my quest to complete this in a good,
workable fashion. I am 2 weeks shy of needing to complete.

THANKS

Replies below. As I am answering you, I think that I am indeed raising more
questions than even the ones you asked.

This is raising more questions than answers. What tables do you have,
what are their primary keys, and how are they related?

Tablesealer -( Primary Key = DealerID) DealerID to Orders;
1 to many
Orders - ( Primary Key = OrdersID) CustomerID to
Customers
Customers-( Primary Key = CustomerID)
CustomerID to Order Detail; 1 to many
Order Details - (Primary Key = OrderDetailsID)
Product ID to Products
Products - (Primary Key = Products) Contract ID to
Contracts
Contracts - (Primary Key = ContractID) Contracts to
Products; 1 to many
Sin - (Primary Key = SINID) SinID to Products

The above relationships are the only ones showing up on my tables in the
query. When I look at all table relationships, I get the following:
Orders to Customer - not directly related
Order Details to Products - not directly related
Products to Contracts - not directly related
SIN to Products - not directly related

I have more tables that the ones listed above. The ones above are the ones
pertaining to this query.

Is the control number the unique identifier of a purchase order?

Yes, each purchase order is manually assigned a control number.

At one point it seems to be, when you say "each control number relates to
a

specific purchase order for that customer" - but later you say "Each
product will have all the info I need specific to that particular
control number", which suggests that the control number relates to a
product rather than a purchase order.

No - control number relates to a purchase order wherein the customer
purchases products under that control number. Sorry for my
inability to explain clearly.

About the query "that contains all information pertaining to [a]
specific control number"? Is it a parameter query? if not, where does it
get the control number from? Please paste the SQL statement (from the
SQL view of the query) into your next message.
My present query is a select query. The control number comes

from
the customer table; this number is manually assigned in sequencial
order.

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv AS [JD Invoice #],
Dealer.[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));

Within this long SQL, you will see JD Invoice#, JDInv$,SerialNum,
ShipDate. This is the information that the dealer will insert into the
excel worksheet. At the present time, I have attempted to create a
template in excel that would put all of this information in legible format
since my forms and reports will not email and allow protection of all
but these 4 cells and keep the format. I have been trying to find a way to
do what we are talking about now. I must transmit all this information
to the dealer in order that he make delivery. I could omit these 4 fields
and have them in a separate worksheet to link with access.

As for the serial numbers: Does every product you order have a serial
number, or only some of them?

Some of them


Presumably you have (or need) a 1:M

relationship between "order detail" records and "products delivered".
This would allow you to track which individual items were delivered
against which purchase order.
You are correct in your assumption.

Presumably you'd need something like that in any case to cover the
situation where (e.g.) you tell the dealer to deliver 10 x Acme Widgets
Model 99, but he only has two in stock. So he delivers those, and orders
8 from his supplier, who only has 4 in stock, which are delivered a few
days later, while the remaining 4 have to be back ordered.


John - Thank you for your prompt reply. May I give you a bit more
information so that I can be sure that I am doing this correctly?

My database is sales oriented. I have control numbers for each customer
(not the auto id that is assigned from the customer table). Each control
number relates to a specific purchase order for that customer. The order
detail contains the products and their description and the serial numbers
for each product. When I get a purchase order, I assign the control number,
enter the info into my database, run a query that contains all information
pertaining to that specific control number, analyze with Microsoft

Excel
and

email to my dealer who makes delivery from the info emailed to him. When
the dealer delivers, he emails back to me the items that I need to add

to
my

access database; i.e. his vendor invoice number, his cost of the products
delivered, delivery date, and serial numbers. At the present time, he
inserts the information into the emailed info, and sends back to me.

Since I have never done an update query nor an append query, I felt

that
the

above info would be useful to answer the balance of my questions.
The order may have many products on it. Each product will

have
all

the info I need specific to that particular control number.
The info sent to the dealer will say, for instance - 2 of a widget.
The dealer will reply with an invoice number and serial number for EACH
widget. My emailed excel info only has space for 1 set of his info.

I
have

not found a way to insert more spaces on an as needed basis other than
manually adding to the excel spreadsheet before I email to him.

Can you, again, guide me through the process of what I need to do? Many
thanks, JR
You can only import into tables, because they are the only places where
data is stored. But you can use queries as you import to control just
what gets imported and where it the data goes.

Often the best way to work is to link to the Excel data rather than
import it (File|Get External Data|Link). This gives you a linked table
in Access that is connected to the Excel worksheet. You then use append
or update queries to move data from the linked table to your actual
tables.

For instance, it sounds as if you need to update existing records that
relate to particular "control numbers" (whatever they are). If so, you'd
create an update query that joins the linked table to another table on
the control number field, and updates fields in the other table with
values from the corresponding fields in the linked table.

Or if records in the linked table need to be added to the other table,
you'd use an append query.

wrote:


I have never tried this so please bear with me.

I have a database in access.
I will be receiving information via email in excel worksheet format.
I would like to import that information into my access database.

Can I import into a query? or does it HAVE to be into a table?

If only into a table, my information needs to be in more than one table,
but
they are linked in my database. Can this be done? The information
needs
to be "Control Number" specific. In otherwords, I will have many many
control numbers that contain the same fields that I am importing. The
information needs to go to the correct control number.

My reason for using this method is that I am trying to eliminate the
chance
of errors that occur with key punching and cut and paste

procedures,
and

have the computer import the information for me.

I am using Access 2003, Excel 2003, and Windows XP Pro.

I probably did not explain myself very well. Please ask questions. Your
suggestions and assistance is greatly appreciated.

JR

 

[John Nurick]

From what you've said, it sounds as if you need at least the following
tables. PK means primary key, FK means foreign key.

Customers
CustomerID PK
Other fields for address and contact information

Dealers
DealerID PK
DealerName
DealerType
Other fields for address and contact information

Products
ProductID PK
Manufacturer
ProductCode
ProductName
UnitPrice (i.e. the price current this week or month or year)
Other fields (e.g. indicating the applicable tax or discount
regimes, handling charges, etc.
Your Products table seems to contain a foreign key
called ContractID: I guess that this refers to the contract(s)
under which the dealer has agreed to supply each products
to your order, in which case it would be appropriate here.)

Orders
OrderID PK
CustomerID FK
PONumber (the customer's purchase order number)
DealerID FK
DateReceived
Other fields specific to an individual order.
(For instance, if you receive one and only one
invoice from the dealer in respect of each
order, you would include fields here for the
dealer's invoice number and invoice total.
If on the other hand you may get more than one
invoice from the dealer in respect of one order,
or if one invoice from a dealer may include items
in more than one of your orders, you will need
a separate DealerInvoices table.)

OrderDetails
OrderID PK FK )these two fields together
ProductID PK FK )are the primary key
Quantity
UnitPrice (i.e. the unit price charged on the date
of the order)
Maybe other fields, e.g. relating to tax and discount.
This *may* be the right place to store serial numbers.
If you just want to have a record of the serial numbers
of the products supplied against each order, but don't
expect to have to search it or refer to it routinely,
you could just include a text or memo field in OrderDetails,
and type the serial numbers into it as a comma-delimited list,
e.g.
BA97654-32, BA97654-33, BA97654-34
But if you want to be able to search and report on serial
numbers as conveniently as any other data, they need a table
of their own:

SerialNumbers
OrderID PK FK )
ProductID PK FK ) all three fields form the primary key
SerialNumber PK )


I hope this makes sense.

Like I said -- I am a novice. Please see below.
Brief flow chart:
1. Purchase order received from customer.
2. Purchase order is assigned a control number and dealer.
3. Information is entered into my Orders form, which creates an OrderID
that I use for customer invoicing.
4. I transmit via email all of the information contained on the written
purchase order to the dealer for delivery.
5. When dealer delivers, he emails back to me -
a. Ship Date
b. Serial Numbers for that purchase order
c. His vendor invoice number(s)
d. His vendor cost(s) per invoice number
6. When I receive delivery info, I input, either manually or if
possible have computer link excel to access, the info in 5 above. This
information is forwarded via email (all of my SQL info) to corporate. That
is corporate's signal to pay dealer his share based on purchase order amount
and his costs.
7. I generate an invoice to the customer for payment.

Either I don't understand what you're doing, or there are some serious
problems with your data structure.

The SQL statement from your query shows that ControlNum is a field in
the Customers table, and not - as you stated - a unique identifier of a
purchase order. If PONumber stands for "purchase order", it's very odd
to have this field in the Customers table: normally it would go in the
Orders table to link your Order record to the purchase order issued by
the Customer. But maybe no one ever does business with you a second
time<g>.

I put the ControlNum in the Customers table because I did
not know how to do otherwise. It is possible to have a repeat customer; but
our business is with the government and, as a rule, is not the same
customer. The ControlNum is "my" main point of control. I receive a
purchase order from the government and assign that customer a control
number. Whenever I am searching for any information pertaining to the
purchase order, we use ControlNum rather than the government purchase order
number.

By the way, when you use the term "purchase order" do you mean the
entity modelled by your Orders table, or are "purchase orders" and
"orders" different things?

Purchase orders are received from the customer. Orders are
created when I enter the information from the purchase order.

Also, your JD Invoice Number and JD Invoice Amount fields are in the
Dealer table. This makes no sense, unless you never use a Dealer more
than once.

Again, I was uncertain as to where to put that information.
Yes, the dealer will be used many times. When I receive a "purchase order",
I assign a control number (unique to the purchase order) and dealer, and
email this via my excel template to the dealer. The dealer -- once delivery
is completed -- emails back either the form with my 4 fields completed -- or
can send info back in a separate worksheet. (This is where we began.)
When, and only when, the dealer lets me know that he has delivered AND sent
me the 4 pertinent items needed, can he receive payment for the delivery.
The way he receives payment is that I must transmit all of the info in the
SQL above - along with the 4 fields completed - to corporate and they issue
credit.

And there doesn't seem to be anywhere to store the serial numbers you
want the dealer to send you.

The only place that they will be visible is in my OrderDetails.
I will manually input the serial numbers sent back to me via dealer into the
original order entry form. I am using the Order ID as my invoice number
sent to the customer for payment.

It's always best to get the data structure right before trying to do the
fancy stuff.

I began this project with absolutely no idea of what or how this
was to be accomplished. Thus, my tables leave room for improvement.Again, thank you for your patience and willingness to help me. I
have suddenly run out of time in my quest to complete this in a good,
workable fashion. I am 2 weeks shy of needing to complete.

THANKS

Replies below. As I am answering you, I think that I am indeed raising more
questions than even the ones you asked.

This is raising more questions than answers. What tables do you have,
what are their primary keys, and how are they related?

Tablesealer -( Primary Key = DealerID) DealerID to Orders;
1 to many
Orders - ( Primary Key = OrdersID) CustomerID to
Customers
Customers-( Primary Key = CustomerID)
CustomerID to Order Detail; 1 to many
Order Details - (Primary Key = OrderDetailsID)
Product ID to Products
Products - (Primary Key = Products) Contract ID to
Contracts
Contracts - (Primary Key = ContractID) Contracts to
Products; 1 to many
Sin - (Primary Key = SINID) SinID to Products

The above relationships are the only ones showing up on my tables in the
query. When I look at all table relationships, I get the following:
Orders to Customer - not directly related
Order Details to Products - not directly related
Products to Contracts - not directly related
SIN to Products - not directly related

I have more tables that the ones listed above. The ones above are the ones
pertaining to this query.



Is the control number the unique identifier of a purchase order?
Yes, each purchase order is manually assigned a control number.

At one point it seems to be, when you say "each control number relates to
a
specific purchase order for that customer" - but later you say "Each
product will have all the info I need specific to that particular
control number", which suggests that the control number relates to a
product rather than a purchase order.
No - control number relates to a purchase order wherein the customer
purchases products under that control number. Sorry for my
inability to explain clearly.


About the query "that contains all information pertaining to [a]
specific control number"? Is it a parameter query? if not, where does it
get the control number from? Please paste the SQL statement (from the
SQL view of the query) into your next message.
My present query is a select query. The control number comes from
the customer table; this number is manually assigned in sequencial
order.

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv AS [JD Invoice #],
Dealer.[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));

Within this long SQL, you will see JD Invoice#, JDInv$,SerialNum,
ShipDate. This is the information that the dealer will insert into the
excel worksheet. At the present time, I have attempted to create a
template in excel that would put all of this information in legible format
since my forms and reports will not email and allow protection of all
but these 4 cells and keep the format. I have been trying to find a way to
do what we are talking about now. I must transmit all this information
to the dealer in order that he make delivery. I could omit these 4 fields
and have them in a separate worksheet to link with access.


As for the serial numbers: Does every product you order have a serial
number, or only some of them?
Some of them


Presumably you have (or need) a 1:M
relationship between "order detail" records and "products delivered".
This would allow you to track which individual items were delivered
against which purchase order.
You are correct in your assumption.

Presumably you'd need something like that in any case to cover the
situation where (e.g.) you tell the dealer to deliver 10 x Acme Widgets
Model 99, but he only has two in stock. So he delivers those, and orders
8 from his supplier, who only has 4 in stock, which are delivered a few
days later, while the remaining 4 have to be back ordered.


John - Thank you for your prompt reply. May I give you a bit more
information so that I can be sure that I am doing this correctly?

My database is sales oriented. I have control numbers for each customer
(not the auto id that is assigned from the customer table). Each control
number relates to a specific purchase order for that customer. The order
detail contains the products and their description and the serial numbers
for each product. When I get a purchase order, I assign the control
number,
enter the info into my database, run a query that contains all
information
pertaining to that specific control number, analyze with Microsoft Excel
and
email to my dealer who makes delivery from the info emailed to him. When
the dealer delivers, he emails back to me the items that I need to add to
my
access database; i.e. his vendor invoice number, his cost of the
products
delivered, delivery date, and serial numbers. At the present time, he
inserts the information into the emailed info, and sends back to me.

Since I have never done an update query nor an append query, I felt that
the
above info would be useful to answer the balance of my questions.
The order may have many products on it. Each product will have
all
the info I need specific to that particular control number.
The info sent to the dealer will say, for instance - 2 of a
widget.
The dealer will reply with an invoice number and serial number for EACH
widget. My emailed excel info only has space for 1 set of his info. I
have
not found a way to insert more spaces on an as needed basis other than
manually adding to the excel spreadsheet before I email to him.

Can you, again, guide me through the process of what I need to do? Many
thanks, JR
You can only import into tables, because they are the only places where
data is stored. But you can use queries as you import to control just
what gets imported and where it the data goes.

Often the best way to work is to link to the Excel data rather than
import it (File|Get External Data|Link). This gives you a linked table
in Access that is connected to the Excel worksheet. You then use append
or update queries to move data from the linked table to your actual
tables.

For instance, it sounds as if you need to update existing records that
relate to particular "control numbers" (whatever they are). If so,
you'd
create an update query that joins the linked table to another table on
the control number field, and updates fields in the other table with
values from the corresponding fields in the linked table.

Or if records in the linked table need to be added to the other table,
you'd use an append query.

wrote:


I have never tried this so please bear with me.

I have a database in access.
I will be receiving information via email in excel worksheet format.
I would like to import that information into my access database.

Can I import into a query? or does it HAVE to be into a table?

If only into a table, my information needs to be in more than one
table,
but
they are linked in my database. Can this be done? The information
needs
to be "Control Number" specific. In otherwords, I will have many many
control numbers that contain the same fields that I am importing. The
information needs to go to the correct control number.

My reason for using this method is that I am trying to eliminate the
chance
of errors that occur with key punching and cut and paste procedures,
and
have the computer import the information for me.

I am using Access 2003, Excel 2003, and Windows XP Pro.

I probably did not explain myself very well. Please ask questions.
Your
suggestions and assistance is greatly appreciated.

JR

 

[jwrnana]

I will look at your table versus mine and make changes.

Can you direct me as to how to import from excel to my table in access?

Thank you so much.

From what you've said, it sounds as if you need at least the following
tables. PK means primary key, FK means foreign key.

Customers
CustomerID PK
Other fields for address and contact information

Dealers
DealerID PK
DealerName
DealerType
Other fields for address and contact information

Products
ProductID PK
Manufacturer
ProductCode
ProductName
UnitPrice (i.e. the price current this week or month or year)
Other fields (e.g. indicating the applicable tax or discount
regimes, handling charges, etc.
Your Products table seems to contain a foreign key
called ContractID: I guess that this refers to the contract(s)
under which the dealer has agreed to supply each products
to your order, in which case it would be appropriate here.)

Orders
OrderID PK
CustomerID FK
PONumber (the customer's purchase order number)
DealerID FK
DateReceived
Other fields specific to an individual order.
(For instance, if you receive one and only one
invoice from the dealer in respect of each
order, you would include fields here for the
dealer's invoice number and invoice total.
If on the other hand you may get more than one
invoice from the dealer in respect of one order,
or if one invoice from a dealer may include items
in more than one of your orders, you will need
a separate DealerInvoices table.)

OrderDetails
OrderID PK FK )these two fields together
ProductID PK FK )are the primary key
Quantity
UnitPrice (i.e. the unit price charged on the date
of the order)
Maybe other fields, e.g. relating to tax and discount.
This *may* be the right place to store serial numbers.
If you just want to have a record of the serial numbers
of the products supplied against each order, but don't
expect to have to search it or refer to it routinely,
you could just include a text or memo field in OrderDetails,
and type the serial numbers into it as a comma-delimited list,
e.g.
BA97654-32, BA97654-33, BA97654-34
But if you want to be able to search and report on serial
numbers as conveniently as any other data, they need a table
of their own:

SerialNumbers
OrderID PK FK )
ProductID PK FK ) all three fields form the primary key
SerialNumber PK )


I hope this makes sense.

Like I said -- I am a novice. Please see below.
Brief flow chart:
1. Purchase order received from customer.
2. Purchase order is assigned a control number and dealer.
3. Information is entered into my Orders form, which creates an OrderID
that I use for customer invoicing.
4. I transmit via email all of the information contained on the written
purchase order to the dealer for delivery.
5. When dealer delivers, he emails back to me -
a. Ship Date
b. Serial Numbers for that purchase order
c. His vendor invoice number(s)
d. His vendor cost(s) per invoice number
6. When I receive delivery info, I input, either manually or if
possible have computer link excel to access, the info in 5 above. This
information is forwarded via email (all of my SQL info) to corporate. That
is corporate's signal to pay dealer his share based on purchase order amount
and his costs.
7. I generate an invoice to the customer for payment.

Either I don't understand what you're doing, or there are some serious
problems with your data structure.

The SQL statement from your query shows that ControlNum is a field in
the Customers table, and not - as you stated - a unique identifier of a
purchase order. If PONumber stands for "purchase order", it's very odd
to have this field in the Customers table: normally it would go in the
Orders table to link your Order record to the purchase order issued by
the Customer. But maybe no one ever does business with you a second
time<g>.

I put the ControlNum in the Customers table because I did
not know how to do otherwise. It is possible to have a repeat customer; but
our business is with the government and, as a rule, is not the same
customer. The ControlNum is "my" main point of control. I receive a
purchase order from the government and assign that customer a control
number. Whenever I am searching for any information pertaining to the
purchase order, we use ControlNum rather than the government purchase order
number.

By the way, when you use the term "purchase order" do you mean the
entity modelled by your Orders table, or are "purchase orders" and
"orders" different things?

Purchase orders are received from the customer. Orders are
created when I enter the information from the purchase order.

Also, your JD Invoice Number and JD Invoice Amount fields are in the
Dealer table. This makes no sense, unless you never use a Dealer more
than once.

Again, I was uncertain as to where to put that information.
Yes, the dealer will be used many times. When I receive a "purchase order",
I assign a control number (unique to the purchase order) and dealer, and
email this via my excel template to the dealer. The dealer -- once delivery
is completed -- emails back either the form with my 4 fields completed -- or
can send info back in a separate worksheet. (This is where we began.)
When, and only when, the dealer lets me know that he has delivered AND sent
me the 4 pertinent items needed, can he receive payment for the delivery.
The way he receives payment is that I must transmit all of the info in the
SQL above - along with the 4 fields completed - to corporate and they issue
credit.

And there doesn't seem to be anywhere to store the serial numbers you
want the dealer to send you.

The only place that they will be visible is in my OrderDetails.
I will manually input the serial numbers sent back to me via dealer into the
original order entry form. I am using the Order ID as my invoice number
sent to the customer for payment.

It's always best to get the data structure right before trying to do the
fancy stuff.

I began this project with absolutely no idea of what or how this
was to be accomplished. Thus, my tables leave room for improvement.Again, thank you for your patience and willingness to help me. I
have suddenly run out of time in my quest to complete this in a good,
workable fashion. I am 2 weeks shy of needing to complete.

THANKS

Replies below. As I am answering you, I think that I am indeed raising more
questions than even the ones you asked.

This is raising more questions than answers. What tables do you have,
what are their primary keys, and how are they related?

Tablesealer -( Primary Key = DealerID) DealerID to Orders;
1 to many
Orders - ( Primary Key = OrdersID)

CustomerID
to

Customers
Customers-( Primary Key = CustomerID)
CustomerID to Order Detail; 1 to many
Order Details - (Primary Key = OrderDetailsID)
Product ID to Products
Products - (Primary Key = Products) Contract ID to
Contracts
Contracts - (Primary Key = ContractID)

Contracts
to

Products; 1 to many
Sin - (Primary Key = SINID) SinID to Products

The above relationships are the only ones showing up on my tables in the
query. When I look at all table relationships, I get the following:
Orders to Customer - not directly related
Order Details to Products - not directly related
Products to Contracts - not directly related
SIN to Products - not directly related

I have more tables that the ones listed above. The ones above are the ones
pertaining to this query.



Is the control number the unique identifier of a purchase order?
Yes, each purchase order is manually assigned a control number.

At one point it seems to be, when you say "each control number

relates
to

a
specific purchase order for that customer" - but later you say "Each
product will have all the info I need specific to that particular
control number", which suggests that the control number relates to a
product rather than a purchase order.
No - control number relates to a purchase order wherein the customer
purchases products under that control number. Sorry for my
inability to explain clearly.


About the query "that contains all information pertaining to [a]
specific control number"? Is it a parameter query? if not, where

does
it

get the control number from? Please paste the SQL statement (from the
SQL view of the query) into your next message.
My present query is a select query. The control number

comes
from

the customer table; this number is manually assigned in sequencial
order.

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv AS [JD Invoice #],
Dealer.[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));

Within this long SQL, you will see JD Invoice#, JDInv$,SerialNum,
ShipDate. This is the information that the dealer will insert into the
excel worksheet. At the present time, I have attempted to create a
template in excel that would put all of this information in legible format
since my forms and reports will not email and allow protection of all
but these 4 cells and keep the format. I have been trying to find a

way
to

do what we are talking about now. I must transmit all this information
to the dealer in order that he make delivery. I could omit these 4 fields
and have them in a separate worksheet to link with access.


As for the serial numbers: Does every product you order have a serial
number, or only some of them?
Some of them


Presumably you have (or need) a 1:M
relationship between "order detail" records and "products delivered".
This would allow you to track which individual items were delivered
against which purchase order.
You are correct in your assumption.

Presumably you'd need something like that in any case to cover the
situation where (e.g.) you tell the dealer to deliver 10 x Acme Widgets
Model 99, but he only has two in stock. So he delivers those, and orders
8 from his supplier, who only has 4 in stock, which are delivered a few
days later, while the remaining 4 have to be back ordered.

wrote:


John - Thank you for your prompt reply. May I give you a bit more
information so that I can be sure that I am doing this correctly?

My database is sales oriented. I have control numbers for each customer
(not the auto id that is assigned from the customer table). Each control
number relates to a specific purchase order for that customer. The order
detail contains the products and their description and the serial numbers
for each product. When I get a purchase order, I assign the control
number,
enter the info into my database, run a query that contains all
information
pertaining to that specific control number, analyze with Microsoft Excel
and
email to my dealer who makes delivery from the info emailed to him. When
the dealer delivers, he emails back to me the items that I need to

add
to

my
access database; i.e. his vendor invoice number, his cost of the
products
delivered, delivery date, and serial numbers. At the present time, he
inserts the information into the emailed info, and sends back to me.

Since I have never done an update query nor an append query, I felt that
the
above info would be useful to answer the balance of my questions.
The order may have many products on it. Each product will have
all
the info I need specific to that particular control number.
The info sent to the dealer will say, for instance - 2 of a
widget.
The dealer will reply with an invoice number and serial number for EACH
widget. My emailed excel info only has space for 1 set of his

info.
I

have
not found a way to insert more spaces on an as needed basis other than
manually adding to the excel spreadsheet before I email to him.

Can you, again, guide me through the process of what I need to do? Many
thanks, JR
You can only import into tables, because they are the only places where
data is stored. But you can use queries as you import to control just
what gets imported and where it the data goes.

Often the best way to work is to link to the Excel data rather than
import it (File|Get External Data|Link). This gives you a linked table
in Access that is connected to the Excel worksheet. You then use append
or update queries to move data from the linked table to your actual
tables.

For instance, it sounds as if you need to update existing records that
relate to particular "control numbers" (whatever they are). If so,
you'd
create an update query that joins the linked table to another

table
on

the control number field, and updates fields in the other table with
values from the corresponding fields in the linked table.

Or if records in the linked table need to be added to the other table,
you'd use an append query.

wrote:


I have never tried this so please bear with me.

I have a database in access.
I will be receiving information via email in excel worksheet format.
I would like to import that information into my access database.

Can I import into a query? or does it HAVE to be into a table?

If only into a table, my information needs to be in more than one
table,
but
they are linked in my database. Can this be done? The information
needs
to be "Control Number" specific. In otherwords, I will have

many
many

control numbers that contain the same fields that I am

importing.
The

information needs to go to the correct control number.

My reason for using this method is that I am trying to eliminate the
chance
of errors that occur with key punching and cut and paste procedures,
and
have the computer import the information for me.

I am using Access 2003, Excel 2003, and Windows XP Pro.

I probably did not explain myself very well. Please ask questions.
Your
suggestions and assistance is greatly appreciated.

JR

 

[John Nurick]

Don't make changes unless you know why. You know your business processes
and I don't.

What you need to do is think very hard about the "entities" your
database needs to model, (e.g. Customers, Dealers, Contracts, Products,
Orders, OrderDetails...) and how they relate to one another - and then
set about modifying your data structure to meets your needs.

First get the data structure right - and before you can get to that
stage you need to decide things such as whether and how you want to be
able to run reports on serial numbers. Once that's done, the details of
how to send the order data to the dealers, and how to extract dealer
invoice information and serial numbers from what the dealer sends back,
will fall into place reasonably easily.

For instance, it appears that every order you send to a dealer relates
to one client, one dealer, one purchase order, one delivery address, one
shipping method, one dealer invoice, and one dealer invoice total. In
that case there's no point repeating all these fields for every detail
line in the Excel sheet you send the dealer. Instead, I'd insert this
header information just once (maybe at the top of a sheet, laid out
rather like the Orders form in the Northwind sample database). Then I'd
put the detail lines in a block below, with only the "detail" fields
such as ProductID, ProductCode, ProductName, Quantity, UnitPrice... and
maybe SerialNumber.

The way to do that is to create an Excel template with the layout and
formatting you need, and then write Access VBA code that uses Automation
to place the header fields in the appropriate cells in the worksheet,
and then to write the details lines in the area below. I'd use Excel's
"named range" feature to give names to each of the header cells and the
"detail" area.

Then when the sheet comes back from the dealer, it's just a matter of
(a) For the dealer invoice number and amount, pull these values and the
OrderID from the header area (easy, because each cell is a named range)
and use this information to update the the dealer invoice number and
amount fields in the corresponding Order record.

(b) Get the serial numbers from the detail area. If you're going to
store all the serial numbers relating to an order detail record as a
comma-delimited list in a field in that record, this is easily done with
an update query that links to the "detail" area of the worksheet. If you
need to store serial numbers in a separate table, it's a bit more
complicated.

But there's no point trying to implement the import/export stuff until
you have a workable data structure.

I will look at your table versus mine and make changes.

Can you direct me as to how to import from excel to my table in access?

Thank you so much.

From what you've said, it sounds as if you need at least the following
tables. PK means primary key, FK means foreign key.

Customers
CustomerID PK
Other fields for address and contact information

Dealers
DealerID PK
DealerName
DealerType
Other fields for address and contact information

Products
ProductID PK
Manufacturer
ProductCode
ProductName
UnitPrice (i.e. the price current this week or month or year)
Other fields (e.g. indicating the applicable tax or discount
regimes, handling charges, etc.
Your Products table seems to contain a foreign key
called ContractID: I guess that this refers to the contract(s)
under which the dealer has agreed to supply each products
to your order, in which case it would be appropriate here.)

Orders
OrderID PK
CustomerID FK
PONumber (the customer's purchase order number)
DealerID FK
DateReceived
Other fields specific to an individual order.
(For instance, if you receive one and only one
invoice from the dealer in respect of each
order, you would include fields here for the
dealer's invoice number and invoice total.
If on the other hand you may get more than one
invoice from the dealer in respect of one order,
or if one invoice from a dealer may include items
in more than one of your orders, you will need
a separate DealerInvoices table.)

OrderDetails
OrderID PK FK )these two fields together
ProductID PK FK )are the primary key
Quantity
UnitPrice (i.e. the unit price charged on the date
of the order)
Maybe other fields, e.g. relating to tax and discount.
This *may* be the right place to store serial numbers.
If you just want to have a record of the serial numbers
of the products supplied against each order, but don't
expect to have to search it or refer to it routinely,
you could just include a text or memo field in OrderDetails,
and type the serial numbers into it as a comma-delimited list,
e.g.
BA97654-32, BA97654-33, BA97654-34
But if you want to be able to search and report on serial
numbers as conveniently as any other data, they need a table
of their own:

SerialNumbers
OrderID PK FK )
ProductID PK FK ) all three fields form the primary key
SerialNumber PK )


I hope this makes sense.

Like I said -- I am a novice. Please see below.
Brief flow chart:
1. Purchase order received from customer.
2. Purchase order is assigned a control number and dealer.
3. Information is entered into my Orders form, which creates an OrderID
that I use for customer invoicing.
4. I transmit via email all of the information contained on the written
purchase order to the dealer for delivery.
5. When dealer delivers, he emails back to me -
a. Ship Date
b. Serial Numbers for that purchase order
c. His vendor invoice number(s)
d. His vendor cost(s) per invoice number
6. When I receive delivery info, I input, either manually or if
possible have computer link excel to access, the info in 5 above. This
information is forwarded via email (all of my SQL info) to corporate. That
is corporate's signal to pay dealer his share based on purchase order amount
and his costs.
7. I generate an invoice to the customer for payment.



Either I don't understand what you're doing, or there are some serious
problems with your data structure.

The SQL statement from your query shows that ControlNum is a field in
the Customers table, and not - as you stated - a unique identifier of a
purchase order. If PONumber stands for "purchase order", it's very odd
to have this field in the Customers table: normally it would go in the
Orders table to link your Order record to the purchase order issued by
the Customer. But maybe no one ever does business with you a second
time<g>.

I put the ControlNum in the Customers table because I did
not know how to do otherwise. It is possible to have a repeat customer; but
our business is with the government and, as a rule, is not the same
customer. The ControlNum is "my" main point of control. I receive a
purchase order from the government and assign that customer a control
number. Whenever I am searching for any information pertaining to the
purchase order, we use ControlNum rather than the government purchase order
number.

By the way, when you use the term "purchase order" do you mean the
entity modelled by your Orders table, or are "purchase orders" and
"orders" different things?

Purchase orders are received from the customer. Orders are
created when I enter the information from the purchase order.

Also, your JD Invoice Number and JD Invoice Amount fields are in the
Dealer table. This makes no sense, unless you never use a Dealer more
than once.

Again, I was uncertain as to where to put that information.
Yes, the dealer will be used many times. When I receive a "purchase order",
I assign a control number (unique to the purchase order) and dealer, and
email this via my excel template to the dealer. The dealer -- once delivery
is completed -- emails back either the form with my 4 fields completed -- or
can send info back in a separate worksheet. (This is where we began.)
When, and only when, the dealer lets me know that he has delivered AND sent
me the 4 pertinent items needed, can he receive payment for the delivery.
The way he receives payment is that I must transmit all of the info in the
SQL above - along with the 4 fields completed - to corporate and they issue
credit.

And there doesn't seem to be anywhere to store the serial numbers you
want the dealer to send you.

The only place that they will be visible is in my OrderDetails.
I will manually input the serial numbers sent back to me via dealer into the
original order entry form. I am using the Order ID as my invoice number
sent to the customer for payment.

It's always best to get the data structure right before trying to do the
fancy stuff.

I began this project with absolutely no idea of what or how this
was to be accomplished. Thus, my tables leave room for improvement.

Again, thank you for your patience and willingness to help me. I
have suddenly run out of time in my quest to complete this in a good,
workable fashion. I am 2 weeks shy of needing to complete.

THANKS



Replies below. As I am answering you, I think that I am indeed raising
more
questions than even the ones you asked.

This is raising more questions than answers. What tables do you have,
what are their primary keys, and how are they related?

Tablesealer -( Primary Key = DealerID) DealerID to
Orders;
1 to many
Orders - ( Primary Key = OrdersID) CustomerID
to
Customers
Customers-( Primary Key = CustomerID)
CustomerID to Order Detail; 1 to many
Order Details - (Primary Key = OrderDetailsID)
Product ID to Products
Products - (Primary Key = Products) Contract ID to
Contracts
Contracts - (Primary Key = ContractID) Contracts
to
Products; 1 to many
Sin - (Primary Key = SINID) SinID to Products

The above relationships are the only ones showing up on my tables in the
query. When I look at all table relationships, I get the following:
Orders to Customer - not directly related
Order Details to Products - not directly related
Products to Contracts - not directly related
SIN to Products - not directly related

I have more tables that the ones listed above. The ones above are the
ones
pertaining to this query.



Is the control number the unique identifier of a purchase order?
Yes, each purchase order is manually assigned a control
number.

At one point it seems to be, when you say "each control number relates
to
a
specific purchase order for that customer" - but later you say "Each
product will have all the info I need specific to that particular
control number", which suggests that the control number relates to a
product rather than a purchase order.
No - control number relates to a purchase order wherein the
customer
purchases products under that control number. Sorry for my
inability to explain clearly.


About the query "that contains all information pertaining to [a]
specific control number"? Is it a parameter query? if not, where does
it
get the control number from? Please paste the SQL statement (from the
SQL view of the query) into your next message.
My present query is a select query. The control number comes
from
the customer table; this number is manually assigned in sequencial
order.

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle,
Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order
Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order
Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%],
Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv AS [JD Invoice
#],
Dealer.[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));

Within this long SQL, you will see JD Invoice#, JDInv$,SerialNum,
ShipDate. This is the information that the dealer will insert into the
excel worksheet. At the present time, I have attempted to create a
template in excel that would put all of this information in legible
format
since my forms and reports will not email and allow protection of all
but these 4 cells and keep the format. I have been trying to find a way
to
do what we are talking about now. I must transmit all this
information
to the dealer in order that he make delivery. I could omit these 4
fields
and have them in a separate worksheet to link with access.


As for the serial numbers: Does every product you order have a serial
number, or only some of them?
Some of them


Presumably you have (or need) a 1:M
relationship between "order detail" records and "products delivered".
This would allow you to track which individual items were delivered
against which purchase order.
You are correct in your assumption.

Presumably you'd need something like that in any case to cover the
situation where (e.g.) you tell the dealer to deliver 10 x Acme Widgets
Model 99, but he only has two in stock. So he delivers those, and
orders
8 from his supplier, who only has 4 in stock, which are delivered a few
days later, while the remaining 4 have to be back ordered.

wrote:


John - Thank you for your prompt reply. May I give you a bit more
information so that I can be sure that I am doing this correctly?

My database is sales oriented. I have control numbers for each
customer
(not the auto id that is assigned from the customer table). Each
control
number relates to a specific purchase order for that customer. The
order
detail contains the products and their description and the serial
numbers
for each product. When I get a purchase order, I assign the control
number,
enter the info into my database, run a query that contains all
information
pertaining to that specific control number, analyze with Microsoft
Excel
and
email to my dealer who makes delivery from the info emailed to him.
When
the dealer delivers, he emails back to me the items that I need to add
to
my
access database; i.e. his vendor invoice number, his cost of the
products
delivered, delivery date, and serial numbers. At the present time, he
inserts the information into the emailed info, and sends back to me.

Since I have never done an update query nor an append query, I felt
that
the
above info would be useful to answer the balance of my questions.
The order may have many products on it. Each product will
have
all
the info I need specific to that particular control number.
The info sent to the dealer will say, for instance - 2 of a
widget.
The dealer will reply with an invoice number and serial number for
EACH
widget. My emailed excel info only has space for 1 set of his info.
I
have
not found a way to insert more spaces on an as needed basis other than
manually adding to the excel spreadsheet before I email to him.

Can you, again, guide me through the process of what I need to do?
Many
thanks, JR
You can only import into tables, because they are the only places
where
data is stored. But you can use queries as you import to control
just
what gets imported and where it the data goes.

Often the best way to work is to link to the Excel data rather than
import it (File|Get External Data|Link). This gives you a linked
table
in Access that is connected to the Excel worksheet. You then use
append
or update queries to move data from the linked table to your actual
tables.

For instance, it sounds as if you need to update existing records
that
relate to particular "control numbers" (whatever they are). If so,
you'd
create an update query that joins the linked table to another table
on
the control number field, and updates fields in the other table with
values from the corresponding fields in the linked table.

Or if records in the linked table need to be added to the other
table,
you'd use an append query.

On Tue, 7 Feb 2006 21:16:34 -0600, "jwrnana"
<[email protected]>
wrote:


I have never tried this so please bear with me.

I have a database in access.
I will be receiving information via email in excel worksheet
format.
I would like to import that information into my access database.

Can I import into a query? or does it HAVE to be into a table?

If only into a table, my information needs to be in more than one
table,
but
they are linked in my database. Can this be done? The
information
needs
to be "Control Number" specific. In otherwords, I will have many
many
control numbers that contain the same fields that I am importing.
The
information needs to go to the correct control number.

My reason for using this method is that I am trying to eliminate
the
chance
of errors that occur with key punching and cut and paste
procedures,
and
have the computer import the information for me.

I am using Access 2003, Excel 2003, and Windows XP Pro.

I probably did not explain myself very well. Please ask questions.
Your
suggestions and assistance is greatly appreciated.

JR

 

[jwrnana]

Before I proceed, I would like to discuss a few things with you since you
are being so kind as to help.

I may be closer than it first appears. However, I believe that I am
duplicating information as some of my queries duplicate info; i.e. if I have
2 line items on an invoice (products purchased) and a unit price for each,
when customer makes payment, my report/query indicates that he paid twice as
much as he should have; if 4 items, report says he paid the amount 4 times.

My comments are beneath yours.

Don't make changes unless you know why. You know your business processes
and I don't.

What you need to do is think very hard about the "entities" your
database needs to model, (e.g. Customers, Dealers, Contracts, Products,
Orders, OrderDetails...) and how they relate to one another - and then
set about modifying your data structure to meets your needs.

First get the data structure right - and before you can get to that
stage you need to decide things such as whether and how you want to be
able to run reports on serial numbers. Once that's done, the details of
how to send the order data to the dealers, and how to extract dealer
invoice information and serial numbers from what the dealer sends back,
will fall into place reasonably easily.

For instance, it appears that every order you send to a dealer relates
to one client, one dealer, one purchase order, one delivery address, one
shipping method, one dealer invoice, and one dealer invoice total. In
that case there's no point repeating all these fields for every detail
line in the Excel sheet you send the dealer. Instead, I'd insert this
header information just once (maybe at the top of a sheet, laid out
rather like the Orders form in the Northwind sample database). Then I'd
put the detail lines in a block below, with only the "detail" fields
such as ProductID, ProductCode, ProductName, Quantity, UnitPrice... and
maybe SerialNumber.

This is the norm -- but there are some exceptions.

An order will have a purchase order number, an issuing

office, a delivery address, and a payment
office and the number that we assign in house - Control Number. An order
may have more than one delivery address,
therefore I may have more than one dealer
because dealer selection is based upon delivery info contained on the order.

There will be a dealer invoice for each product
delivered, if that product has a serial number; 4
widgets ordered, usually only one invoice since it has no serial number. 5
units each with a serial number for each will
have 5 invoices.

I did not address your comment regarding products. We
have 3 contracts. Each contract contains
certain products and each product has a SIN number unique to the contract.
Therefore, I have ContractID, and SINID for each product.

The way that I have handled multiple dealers/delivery
addresses is by adding an a), b), c) etc. after my
control number. This does create the need to create that same customer
again for each dealer because info must be broken down
by dealer delivery. I am using the basic order form
provided with the "Order Entry" access database template. I have not found
a way to 'split' the order entry form when there is a
partial delivery OR multiple dealers. I have added a field for the
serial numbers. I know that I can enlarge the field on the order entry
screen to hold all of my serial numbers. If I
enlarge the field on the Invoice report, then, unless I have many serial
numbers, there is a large gap between each line item on the invoice. Is
there a way to have that field enlarged on an "as
needed" basis?

The way to do that is to create an Excel template with the layout and
formatting you need, and then write Access VBA code that uses Automation
to place the header fields in the appropriate cells in the worksheet,
and then to write the details lines in the area below. I'd use Excel's
"named range" feature to give names to each of the header cells and the
"detail" area.

I have already created an Excel template with my layout
and formatting. I do not know how to
write VBA code. What I did was run my query in access,
analyze with Microsoft Excel, and then link that
worksheet to my template. The problem I run into here is that sometimes
when my information is placed in the wrong location
on the template. Then I have to relink that
worksheet cell by cell. Since we are handling 50+ a day, we run out of time
(and patience).

When this is working, it is great. I then email to
dealer (worksheet protected except for the
fields he needs to complete). When it is returned to me, I am at a dead
end. How do I update my access database with this
information without cutting and pasting or manually entering?

I have never used "named range" feature of Excel. Is
there a place to find this info on the web? I have
written to excel group numerous times and rarely get no response. I vaguely
remember reading something about merged cells not
working. I do have merged cells on my template.

Then when the sheet comes back from the dealer, it's just a matter of
(a) For the dealer invoice number and amount, pull these values and the
OrderID from the header area (easy, because each cell is a named range)
and use this information to update the the dealer invoice number and
amount fields in the corresponding Order record.

(b) Get the serial numbers from the detail area. If you're going to
store all the serial numbers relating to an order detail record as a
comma-delimited list in a field in that record, this is easily done with
an update query that links to the "detail" area of the worksheet. If you
need to store serial numbers in a separate table, it's a bit more
complicated.

Serial numbers do not need to be on a separate table.
They pertain to a specific order.

I know that you say there is no point in trying to
implement the import/export stuff until I have a
workable data structure. My database is working, although rather crudely.
I would like to fine tune it with your suggestions;
however, I do not know if I will have the time to complete all of
this before my deadline. However, if you do not instruct me as to how to
accomplish (a) and (b), I fear that I will not be
able to get back in touch with you when I am finished.

I will look at your table versus mine and make changes.

Can you direct me as to how to import from excel to my table in access?

Thank you so much.

From what you've said, it sounds as if you need at least the following
tables. PK means primary key, FK means foreign key.

Customers
CustomerID PK
Other fields for address and contact information

Dealers
DealerID PK
DealerName
DealerType
Other fields for address and contact information

Products
ProductID PK
Manufacturer
ProductCode
ProductName
UnitPrice (i.e. the price current this week or month or year)
Other fields (e.g. indicating the applicable tax or discount
regimes, handling charges, etc.
Your Products table seems to contain a foreign key
called ContractID: I guess that this refers to the contract(s)
under which the dealer has agreed to supply each products
to your order, in which case it would be appropriate here.)

Orders
OrderID PK
CustomerID FK
PONumber (the customer's purchase order number)
DealerID FK
DateReceived
Other fields specific to an individual order.
(For instance, if you receive one and only one
invoice from the dealer in respect of each
order, you would include fields here for the
dealer's invoice number and invoice total.
If on the other hand you may get more than one
invoice from the dealer in respect of one order,
or if one invoice from a dealer may include items
in more than one of your orders, you will need
a separate DealerInvoices table.)

OrderDetails
OrderID PK FK )these two fields together
ProductID PK FK )are the primary key
Quantity
UnitPrice (i.e. the unit price charged on the date
of the order)
Maybe other fields, e.g. relating to tax and discount.
This *may* be the right place to store serial numbers.
If you just want to have a record of the serial numbers
of the products supplied against each order, but don't
expect to have to search it or refer to it routinely,
you could just include a text or memo field in OrderDetails,
and type the serial numbers into it as a comma-delimited list,
e.g.
BA97654-32, BA97654-33, BA97654-34
But if you want to be able to search and report on serial
numbers as conveniently as any other data, they need a table
of their own:

SerialNumbers
OrderID PK FK )
ProductID PK FK ) all three fields form the primary key
SerialNumber PK )


I hope this makes sense.



Like I said -- I am a novice. Please see below.
Brief flow chart:
1. Purchase order received from customer.
2. Purchase order is assigned a control number and dealer.
3. Information is entered into my Orders form, which creates an OrderID
that I use for customer invoicing.
4. I transmit via email all of the information contained on the written
purchase order to the dealer for delivery.
5. When dealer delivers, he emails back to me -
a. Ship Date
b. Serial Numbers for that purchase order
c. His vendor invoice number(s)
d. His vendor cost(s) per invoice number
6. When I receive delivery info, I input, either manually or if
possible have computer link excel to access, the info in 5 above. This
information is forwarded via email (all of my SQL info) to corporate. That
is corporate's signal to pay dealer his share based on purchase order amount
and his costs.
7. I generate an invoice to the customer for payment.



Either I don't understand what you're doing, or there are some serious
problems with your data structure.

The SQL statement from your query shows that ControlNum is a field in
the Customers table, and not - as you stated - a unique identifier of a
purchase order. If PONumber stands for "purchase order", it's very odd
to have this field in the Customers table: normally it would go in the
Orders table to link your Order record to the purchase order issued by
the Customer. But maybe no one ever does business with you a second
time<g>.

I put the ControlNum in the Customers table because I did
not know how to do otherwise. It is possible to have a repeat

customer;
but

our business is with the government and, as a rule, is not the same
customer. The ControlNum is "my" main point of control. I receive a
purchase order from the government and assign that customer a control
number. Whenever I am searching for any information pertaining to the
purchase order, we use ControlNum rather than the government purchase order
number.

By the way, when you use the term "purchase order" do you mean the
entity modelled by your Orders table, or are "purchase orders" and
"orders" different things?

Purchase orders are received from the customer.

Orders
are

created when I enter the information from the purchase order.

Also, your JD Invoice Number and JD Invoice Amount fields are in the
Dealer table. This makes no sense, unless you never use a Dealer more
than once.

Again, I was uncertain as to where to put that information.
Yes, the dealer will be used many times. When I receive a "purchase order",
I assign a control number (unique to the purchase order) and dealer, and
email this via my excel template to the dealer. The dealer -- once delivery
is completed -- emails back either the form with my 4 fields

completed --
or

can send info back in a separate worksheet. (This is where we began.)
When, and only when, the dealer lets me know that he has delivered AND sent
me the 4 pertinent items needed, can he receive payment for the delivery.
The way he receives payment is that I must transmit all of the info in the
SQL above - along with the 4 fields completed - to corporate and they issue
credit.

And there doesn't seem to be anywhere to store the serial numbers you
want the dealer to send you.

The only place that they will be visible is in my OrderDetails.
I will manually input the serial numbers sent back to me via dealer

into
the

original order entry form. I am using the Order ID as my invoice number
sent to the customer for payment.

It's always best to get the data structure right before trying to do the
fancy stuff.

I began this project with absolutely no idea of what or

how
this

was to be accomplished. Thus, my tables leave room for improvement.

Again, thank you for your patience and willingness to help me. I
have suddenly run out of time in my quest to complete this in a good,
workable fashion. I am 2 weeks shy of needing to complete.

THANKS

wrote:


Replies below. As I am answering you, I think that I am indeed raising
more
questions than even the ones you asked.

This is raising more questions than answers. What tables do you have,
what are their primary keys, and how are they related?

Tablesealer -( Primary Key = DealerID) DealerID to
Orders;
1 to many
Orders - ( Primary Key = OrdersID) CustomerID
to
Customers
Customers-( Primary Key = CustomerID)
CustomerID to Order Detail; 1 to many
Order Details - (Primary Key = OrderDetailsID)
Product ID to Products
Products - (Primary Key = Products) Contract

ID
to

Contracts
Contracts - (Primary Key = ContractID) Contracts
to
Products; 1 to many
Sin - (Primary Key = SINID) SinID to Products

The above relationships are the only ones showing up on my tables

in
the

query. When I look at all table relationships, I get the following:
Orders to Customer - not directly related
Order Details to Products - not directly related
Products to Contracts - not directly related
SIN to Products - not directly related

I have more tables that the ones listed above. The ones above are the
ones
pertaining to this query.



Is the control number the unique identifier of a purchase order?
Yes, each purchase order is manually assigned a control
number.

At one point it seems to be, when you say "each control number relates
to
a
specific purchase order for that customer" - but later you say "Each
product will have all the info I need specific to that particular
control number", which suggests that the control number relates to a
product rather than a purchase order.
No - control number relates to a purchase order wherein the
customer
purchases products under that control number. Sorry for my
inability to explain clearly.


About the query "that contains all information pertaining to [a]
specific control number"? Is it a parameter query? if not, where does
it
get the control number from? Please paste the SQL statement (from the
SQL view of the query) into your next message.
My present query is a select query. The control number comes
from
the customer table; this number is manually assigned in sequencial
order.

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle,
Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order
Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order
Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%],
Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv AS [JD Invoice
#],
Dealer.[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));

Within this long SQL, you will see JD Invoice#, JDInv$,SerialNum,
ShipDate. This is the information that the dealer will insert into the
excel worksheet. At the present time, I have attempted to

create
a

template in excel that would put all of this information in legible
format
since my forms and reports will not email and allow protection

of
all

but these 4 cells and keep the format. I have been trying to find

a
way

to
do what we are talking about now. I must transmit all this
information
to the dealer in order that he make delivery. I could omit these 4
fields
and have them in a separate worksheet to link with access.


As for the serial numbers: Does every product you order have a serial
number, or only some of them?
Some of them


Presumably you have (or need) a 1:M
relationship between "order detail" records and "products delivered".
This would allow you to track which individual items were delivered
against which purchase order.
You are correct in your assumption.

Presumably you'd need something like that in any case to cover the
situation where (e.g.) you tell the dealer to deliver 10 x Acme Widgets
Model 99, but he only has two in stock. So he delivers those, and
orders
8 from his supplier, who only has 4 in stock, which are delivered

a
few

days later, while the remaining 4 have to be back ordered.

wrote:


John - Thank you for your prompt reply. May I give you a bit more
information so that I can be sure that I am doing this correctly?

My database is sales oriented. I have control numbers for each
customer
(not the auto id that is assigned from the customer table). Each
control
number relates to a specific purchase order for that customer. The
order
detail contains the products and their description and the serial
numbers
for each product. When I get a purchase order, I assign the control
number,
enter the info into my database, run a query that contains all
information
pertaining to that specific control number, analyze with Microsoft
Excel
and
email to my dealer who makes delivery from the info emailed to him.
When
the dealer delivers, he emails back to me the items that I need

to
add

to
my
access database; i.e. his vendor invoice number, his cost of the
products
delivered, delivery date, and serial numbers. At the present

time,
he

inserts the information into the emailed info, and sends back to me.

Since I have never done an update query nor an append query, I felt
that
the
above info would be useful to answer the balance of my questions.
The order may have many products on it. Each product will
have
all
the info I need specific to that particular control number.
The info sent to the dealer will say, for instance - 2 of a
widget.
The dealer will reply with an invoice number and serial number for
EACH
widget. My emailed excel info only has space for 1 set of his info.
I
have
not found a way to insert more spaces on an as needed basis

other
than

manually adding to the excel spreadsheet before I email to him.

Can you, again, guide me through the process of what I need to do?
Many
thanks, JR
You can only import into tables, because they are the only places
where
data is stored. But you can use queries as you import to control
just
what gets imported and where it the data goes.

Often the best way to work is to link to the Excel data rather than
import it (File|Get External Data|Link). This gives you a linked
table
in Access that is connected to the Excel worksheet. You then use
append
or update queries to move data from the linked table to your actual
tables.

For instance, it sounds as if you need to update existing records
that
relate to particular "control numbers" (whatever they are). If so,
you'd
create an update query that joins the linked table to another table
on
the control number field, and updates fields in the other

table
with

values from the corresponding fields in the linked table.

Or if records in the linked table need to be added to the other
table,
you'd use an append query.

On Tue, 7 Feb 2006 21:16:34 -0600, "jwrnana"
<[email protected]>
wrote:


I have never tried this so please bear with me.

I have a database in access.
I will be receiving information via email in excel worksheet
format.
I would like to import that information into my access database.

Can I import into a query? or does it HAVE to be into a table?

If only into a table, my information needs to be in more than one
table,
but
they are linked in my database. Can this be done? The
information
needs
to be "Control Number" specific. In otherwords, I will have many
many
control numbers that contain the same fields that I am importing.
The
information needs to go to the correct control number.

My reason for using this method is that I am trying to eliminate
the
chance
of errors that occur with key punching and cut and paste
procedures,
and
have the computer import the information for me.

I am using Access 2003, Excel 2003, and Windows XP Pro.

I probably did not explain myself very well. Please ask questions.
Your
suggestions and assistance is greatly appreciated.

JR



--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[John Nurick]

It seems from what you say that one of your orders may be sent to
multiple dealers, each of whom will supply some of the items in the
order (at least that's how I interpret your statement that

An order
may have more than one delivery address,
therefore I may have more than one dealer
because dealer selection is based upon delivery
info contained on the order.

). If that's the case, things are more complicated than I thought and
the table structure you have described is even less adequate to your
situation. I can't prevent you using an unsuitable structure, but I'm
not going to bust a gut to help you work round its deficiencies.

With regard to your final questions, basic use of named ranges in Excel
is very simple. Select the cell or cells you want to name, and use the
Insert|Name|Define menu command. From then on, any time you want to
refer to that cell or those cells, in an Excel formula or elsewhere, you
can use the name instead. When you name the range in your Excel sheet
that contains the order detail items, select not only the actual data
but the row above with the column headings. (But it's important that the
Excel colum headings should be legal as Access field names: avoid
non-alphanumeric characters such as $ # " ' !)

There's more than one way of getting the values out of a named range in
an Excel workbook into Access. This is the one I usually use.

Let's assume the workbook is C:\Temp\XXX.xls.

For a single-cell named range - e.g. a cell named OrderID containing a
number such as 2341, use something like this:

Dim rsR As DAO.Recordset
Dim strWorkbook As String
Dim strRangeName As String
Dim strSQL As String
Dim lngOrderID As Long

strWorkbook = "C:\Temp\XXX.xls"
strRangeName = "OrderID"

'Build SQL statement for a query that gets the data from the
'workbook
strSQL = "SELECT * FROM [Excel 8.0;HDR=No;Database=" _
& strWorkbook & "].[" & strRangeName & "];"

'Open recordset on the query
Set rsR = CurrentDB.OpenRecordset strSQL, dbOpenSnapshot
'Get the value from the recordset
lngOrderID = rsr.Fields(0).Value
rsR.Close

To update some fields in an Access table with new values from the
corresponding records in a table that is a named range in Excel, let's
assume that the range is called Details, you use an update query that
joins the Access table and the Excel range on the appropriate fields.

For example, if:
-the Access table is called OrderDetails
-the Excel range is called Details
-there are fields in Access and columns in Excel called
OrderID and ProductCode which between them identify which
record in Access that corresponds to any given row in Excel
-you want to update the JDInvNum field in Access with the values
from the Excel column headed "JD Invoice Number" column,
and JDInvAmt with the values from "JD Invoice Amount"

then the SQL statement for the query will be like this:

UPDATE OrderDetails AS Target
INNER JOIN [Excel 8.0;HDR=Yes;Database=C:\Temp\XXX.xls;].[Details]
AS Source
ON Source.OrderID=Target.OrderID
AND Source.ProductCode=Target.ProductCode
SET
Target.JDInvNum = Source.[JD Invoice Number],
Target.JDInvoiceAmt = Source.[JD Invoice Amount]
;

Good luck!

 

[jwrnana]

John - I would like to comment on your statement below:

"If that's the case, things are more complicated than I thought
and
the table structure you have described is even less adequate to
your
situation."

What I am attempting to do is to get a suitable structure! Having never
done this, and never had any training, I am learning as I go. I have
purchased books, worked with info from Microsoft Access on the web, etc.
Your interpretation of my statement was correct. There are occasional
instances where there are multiple dealers delivering some of the items on
an order. This does not occur very often, but it does occur. I have
'found' a way to work with the situation, albeit not the most efficient way.
How and where would I have ever found information regarding this particular
situation!?! I am taking the information that you so generously shared
with me and am going "back to basics" in an effort to get my tables correct.

Your statement that "I can't prevent you using an unsuitable structure, but
I'm not going to bust a gut to help you work round its deficiencies" was
uncalled for. I am sure that you are far more knowledgeable than I and it
can be frustrating working with someone with so little knowledge as I. I am
deligently working on these issues. If you have any suggestions regarding
the issue of multiple dealers, etc. and are willing to share with me, I
would appreciate them.

Again, thank you for your assistance.

It seems from what you say that one of your orders may be sent to
multiple dealers, each of whom will supply some of the items in the
order (at least that's how I interpret your statement that

An order
may have more than one delivery address,
therefore I may have more than one dealer
because dealer selection is based upon delivery
info contained on the order.

). If that's the case, things are more complicated than I thought and
the table structure you have described is even less adequate to your
situation. I can't prevent you using an unsuitable structure, but I'm
not going to bust a gut to help you work round its deficiencies.

With regard to your final questions, basic use of named ranges in Excel
is very simple. Select the cell or cells you want to name, and use the
Insert|Name|Define menu command. From then on, any time you want to
refer to that cell or those cells, in an Excel formula or elsewhere, you
can use the name instead. When you name the range in your Excel sheet
that contains the order detail items, select not only the actual data
but the row above with the column headings. (But it's important that the
Excel colum headings should be legal as Access field names: avoid
non-alphanumeric characters such as $ # " ' !)

There's more than one way of getting the values out of a named range in
an Excel workbook into Access. This is the one I usually use.

Let's assume the workbook is C:\Temp\XXX.xls.

For a single-cell named range - e.g. a cell named OrderID containing a
number such as 2341, use something like this:

Dim rsR As DAO.Recordset
Dim strWorkbook As String
Dim strRangeName As String
Dim strSQL As String
Dim lngOrderID As Long

strWorkbook = "C:\Temp\XXX.xls"
strRangeName = "OrderID"

'Build SQL statement for a query that gets the data from the
'workbook
strSQL = "SELECT * FROM [Excel 8.0;HDR=No;Database=" _
& strWorkbook & "].[" & strRangeName & "];"

'Open recordset on the query
Set rsR = CurrentDB.OpenRecordset strSQL, dbOpenSnapshot
'Get the value from the recordset
lngOrderID = rsr.Fields(0).Value
rsR.Close

To update some fields in an Access table with new values from the
corresponding records in a table that is a named range in Excel, let's
assume that the range is called Details, you use an update query that
joins the Access table and the Excel range on the appropriate fields.

For example, if:
-the Access table is called OrderDetails
-the Excel range is called Details
-there are fields in Access and columns in Excel called
OrderID and ProductCode which between them identify which
record in Access that corresponds to any given row in Excel
-you want to update the JDInvNum field in Access with the values
from the Excel column headed "JD Invoice Number" column,
and JDInvAmt with the values from "JD Invoice Amount"

then the SQL statement for the query will be like this:

UPDATE OrderDetails AS Target
INNER JOIN [Excel 8.0;HDR=Yes;Database=C:\Temp\XXX.xls;].[Details]
AS Source
ON Source.OrderID=Target.OrderID
AND Source.ProductCode=Target.ProductCode
SET
Target.JDInvNum = Source.[JD Invoice Number],
Target.JDInvoiceAmt = Source.[JD Invoice Amount]
;

Good luck!

Before I proceed, I would like to discuss a few things with you since you
are being so kind as to help.

I may be closer than it first appears. However, I believe that I am
duplicating information as some of my queries duplicate info; i.e. if I have
2 line items on an invoice (products purchased) and a unit price for each,
when customer makes payment, my report/query indicates that he paid twice as
much as he should have; if 4 items, report says he paid the amount 4 times.

My comments are beneath yours.



This is the norm -- but there are some exceptions.

office, a delivery address, and a payment
office and the number that we assign in house - Control Number. An order
may have more than one delivery address,
therefore I may have more than one dealer
because dealer selection is based upon delivery info contained on the order.

There will be a dealer invoice for each product
delivered, if that product has a serial number; 4
widgets ordered, usually only one invoice since it has no serial number. 5
units each with a serial number for each will
have 5 invoices.

I did not address your comment regarding products. We
have 3 contracts. Each contract contains
certain products and each product has a SIN number unique to the contract.
Therefore, I have ContractID, and SINID for each product.

The way that I have handled multiple dealers/delivery
addresses is by adding an a), b), c) etc. after my
control number. This does create the need to create that same customer
again for each dealer because info must be broken down
by dealer delivery. I am using the basic order form
provided with the "Order Entry" access database template. I have not found
a way to 'split' the order entry form when there is a
partial delivery OR multiple dealers. I have added a field for the
serial numbers. I know that I can enlarge the field on the order entry
screen to hold all of my serial numbers. If I
enlarge the field on the Invoice report, then, unless I have many serial
numbers, there is a large gap between each line item on the invoice. Is
there a way to have that field enlarged on an "as
needed" basis?



I have already created an Excel template with my layout
and formatting. I do not know how to
write VBA code. What I did was run my query in access,
analyze with Microsoft Excel, and then link that
worksheet to my template. The problem I run into here is that sometimes
when my information is placed in the wrong location
on the template. Then I have to relink that
worksheet cell by cell. Since we are handling 50+ a day, we run out of time
(and patience).

When this is working, it is great. I then email to
dealer (worksheet protected except for the
fields he needs to complete). When it is returned to me, I am at a dead
end. How do I update my access database with this
information without cutting and pasting or manually entering?

I have never used "named range" feature of Excel. Is
there a place to find this info on the web? I have
written to excel group numerous times and rarely get no response. I vaguely
remember reading something about merged cells not
working. I do have merged cells on my template.

Serial numbers do not need to be on a separate table.
They pertain to a specific order.

I know that you say there is no point in trying to
implement the import/export stuff until I have a
workable data structure. My database is working, although rather crudely.
I would like to fine tune it with your suggestions;
however, I do not know if I will have the time to complete all of
this before my deadline. However, if you do not instruct me as to how to
accomplish (a) and (b), I fear that I will not be
able to get back in touch with you when I am finished.


field
in identifier
of a very
odd in
the issued
by I
did dealer,
and

 

[John Nurick]

You're the one who understands your business processes. I'd never dreamt
of a business that sends the same order to multiple dealers each of whom
fulfils some of the items.

John - I would like to comment on your statement below:

"If that's the case, things are more complicated than I thought
and
the table structure you have described is even less adequate to
your
situation."

What I am attempting to do is to get a suitable structure! Having never
done this, and never had any training, I am learning as I go. I have
purchased books, worked with info from Microsoft Access on the web, etc.
Your interpretation of my statement was correct. There are occasional
instances where there are multiple dealers delivering some of the items on
an order. This does not occur very often, but it does occur. I have
'found' a way to work with the situation, albeit not the most efficient way.

One of the difficulties of working with relational databases is that you
have to design the data structure to handle not just the normal flow of
things but the unusual cases and the exceptions. Probably the books you
have bought have sections on normalisation and database design, go back
and get familiar with the concepts and apply them to your situation. If
you feel you need a better textbook, I can recommend "Designing
Effective Database Systems" by a fellow MVP, Rebecca M. Riordan.

How and where would I have ever found information regarding this particular
situation!?!

Specifically regarding orders that go to multiple dealers? AFAIK that is
such a rare thing that you might well not have found anything. But there
are analogies in such common things as partial fulfilment of orders, one
order producing multiple shipments, etc. Maybe some of the models at
http://www.databaseanswers.org/data_models/index.htm would be helpful.
But really, as always, it's a matter of systematically analysing your
business processes, working out the real or abstract entities required
to model them, and then designing the tables and relationships that
implement it.

I am taking the information that you so generously shared
with me and am going "back to basics" in an effort to get my tables correct.

Good. It may be worth re-thinking the concept of an Order: for instance,
how about having one entity CustomerOrder (i.e. what comes from the
customer, with the PO number and customer ID), and another entity
DealerOrder (i.e. an order as conventionally understood, from you to one
dealer), with a 1:M or M:M relationship between CustomerOrders and
DealerOrders. I guess that each line item in a customer order would
translate to one line item in a dealer order, so you might have an
OrderDetails table with foreign keys CustomerOrderID and DealerOrderID.

Your statement that "I can't prevent you using an unsuitable structure, but
I'm not going to bust a gut to help you work round its deficiencies" was
uncalled for.

I only said that because it seemed from your previous message that you
felt you were under such time pressure that it was not possible to sort
out the design.

I am sure that you are far more knowledgeable than I and it
can be frustrating working with someone with so little knowledge as I. I am
deligently working on these issues. If you have any suggestions regarding
the issue of multiple dealers, etc. and are willing to share with me, I
would appreciate them.

Again, thank you for your assistance.

It seems from what you say that one of your orders may be sent to
multiple dealers, each of whom will supply some of the items in the
order (at least that's how I interpret your statement that

An order
may have more than one delivery address,
therefore I may have more than one dealer
because dealer selection is based upon delivery
info contained on the order.

). If that's the case, things are more complicated than I thought and
the table structure you have described is even less adequate to your
situation. I can't prevent you using an unsuitable structure, but I'm
not going to bust a gut to help you work round its deficiencies.

With regard to your final questions, basic use of named ranges in Excel
is very simple. Select the cell or cells you want to name, and use the
Insert|Name|Define menu command. From then on, any time you want to
refer to that cell or those cells, in an Excel formula or elsewhere, you
can use the name instead. When you name the range in your Excel sheet
that contains the order detail items, select not only the actual data
but the row above with the column headings. (But it's important that the
Excel colum headings should be legal as Access field names: avoid
non-alphanumeric characters such as $ # " ' !)

There's more than one way of getting the values out of a named range in
an Excel workbook into Access. This is the one I usually use.

Let's assume the workbook is C:\Temp\XXX.xls.

For a single-cell named range - e.g. a cell named OrderID containing a
number such as 2341, use something like this:

Dim rsR As DAO.Recordset
Dim strWorkbook As String
Dim strRangeName As String
Dim strSQL As String
Dim lngOrderID As Long

strWorkbook = "C:\Temp\XXX.xls"
strRangeName = "OrderID"

'Build SQL statement for a query that gets the data from the
'workbook
strSQL = "SELECT * FROM [Excel 8.0;HDR=No;Database=" _
& strWorkbook & "].[" & strRangeName & "];"

'Open recordset on the query
Set rsR = CurrentDB.OpenRecordset strSQL, dbOpenSnapshot
'Get the value from the recordset
lngOrderID = rsr.Fields(0).Value
rsR.Close

To update some fields in an Access table with new values from the
corresponding records in a table that is a named range in Excel, let's
assume that the range is called Details, you use an update query that
joins the Access table and the Excel range on the appropriate fields.

For example, if:
-the Access table is called OrderDetails
-the Excel range is called Details
-there are fields in Access and columns in Excel called
OrderID and ProductCode which between them identify which
record in Access that corresponds to any given row in Excel
-you want to update the JDInvNum field in Access with the values
from the Excel column headed "JD Invoice Number" column,
and JDInvAmt with the values from "JD Invoice Amount"

then the SQL statement for the query will be like this:

UPDATE OrderDetails AS Target
INNER JOIN [Excel 8.0;HDR=Yes;Database=C:\Temp\XXX.xls;].[Details]
AS Source
ON Source.OrderID=Target.OrderID
AND Source.ProductCode=Target.ProductCode
SET
Target.JDInvNum = Source.[JD Invoice Number],
Target.JDInvoiceAmt = Source.[JD Invoice Amount]
;

Good luck!

Before I proceed, I would like to discuss a few things with you since you
are being so kind as to help.

I may be closer than it first appears. However, I believe that I am
duplicating information as some of my queries duplicate info; i.e. if I have
2 line items on an invoice (products purchased) and a unit price for each,
when customer makes payment, my report/query indicates that he paid twice as
much as he should have; if 4 items, report says he paid the amount 4 times.

My comments are beneath yours.

Don't make changes unless you know why. You know your business processes
and I don't.

What you need to do is think very hard about the "entities" your
database needs to model, (e.g. Customers, Dealers, Contracts, Products,
Orders, OrderDetails...) and how they relate to one another - and then
set about modifying your data structure to meets your needs.

First get the data structure right - and before you can get to that
stage you need to decide things such as whether and how you want to be
able to run reports on serial numbers. Once that's done, the details of
how to send the order data to the dealers, and how to extract dealer
invoice information and serial numbers from what the dealer sends back,
will fall into place reasonably easily.

For instance, it appears that every order you send to a dealer relates
to one client, one dealer, one purchase order, one delivery address, one
shipping method, one dealer invoice, and one dealer invoice total. In
that case there's no point repeating all these fields for every detail
line in the Excel sheet you send the dealer. Instead, I'd insert this
header information just once (maybe at the top of a sheet, laid out
rather like the Orders form in the Northwind sample database). Then I'd
put the detail lines in a block below, with only the "detail" fields
such as ProductID, ProductCode, ProductName, Quantity, UnitPrice... and
maybe SerialNumber.

This is the norm -- but there are some exceptions.

An order will have a purchase order number, an issuing
office, a delivery address, and a payment
office and the number that we assign in house - Control Number. An order
may have more than one delivery address,
therefore I may have more than one dealer
because dealer selection is based upon delivery info contained on the order.

There will be a dealer invoice for each product
delivered, if that product has a serial number; 4
widgets ordered, usually only one invoice since it has no serial number. 5
units each with a serial number for each will
have 5 invoices.

I did not address your comment regarding products. We
have 3 contracts. Each contract contains
certain products and each product has a SIN number unique to the contract.
Therefore, I have ContractID, and SINID for each product.

The way that I have handled multiple dealers/delivery
addresses is by adding an a), b), c) etc. after my
control number. This does create the need to create that same customer
again for each dealer because info must be broken down
by dealer delivery. I am using the basic order form
provided with the "Order Entry" access database template. I have not found
a way to 'split' the order entry form when there is a
partial delivery OR multiple dealers. I have added a field for the
serial numbers. I know that I can enlarge the field on the order entry
screen to hold all of my serial numbers. If I
enlarge the field on the Invoice report, then, unless I have many serial
numbers, there is a large gap between each line item on the invoice. Is
there a way to have that field enlarged on an "as
needed" basis?


The way to do that is to create an Excel template with the layout and
formatting you need, and then write Access VBA code that uses Automation
to place the header fields in the appropriate cells in the worksheet,
and then to write the details lines in the area below. I'd use Excel's
"named range" feature to give names to each of the header cells and the
"detail" area.

I have already created an Excel template with my layout
and formatting. I do not know how to
write VBA code. What I did was run my query in access,
analyze with Microsoft Excel, and then link that
worksheet to my template. The problem I run into here is that sometimes
when my information is placed in the wrong location
on the template. Then I have to relink that
worksheet cell by cell. Since we are handling 50+ a day, we run out of time
(and patience).

When this is working, it is great. I then email to
dealer (worksheet protected except for the
fields he needs to complete). When it is returned to me, I am at a dead
end. How do I update my access database with this
information without cutting and pasting or manually entering?

I have never used "named range" feature of Excel. Is
there a place to find this info on the web? I have
written to excel group numerous times and rarely get no response. I vaguely
remember reading something about merged cells not
working. I do have merged cells on my template.

Then when the sheet comes back from the dealer, it's just a matter of
(a) For the dealer invoice number and amount, pull these values and the
OrderID from the header area (easy, because each cell is a named range)
and use this information to update the the dealer invoice number and
amount fields in the corresponding Order record.

(b) Get the serial numbers from the detail area. If you're going to
store all the serial numbers relating to an order detail record as a
comma-delimited list in a field in that record, this is easily done with
an update query that links to the "detail" area of the worksheet. If you
need to store serial numbers in a separate table, it's a bit more
complicated.

Serial numbers do not need to be on a separate table.
They pertain to a specific order.

I know that you say there is no point in trying to
implement the import/export stuff until I have a
workable data structure. My database is working, although rather crudely.
I would like to fine tune it with your suggestions;
however, I do not know if I will have the time to complete all of
this before my deadline. However, if you do not instruct me as to how to
accomplish (a) and (b), I fear that I will not be
able to get back in touch with you when I am finished.





I will look at your table versus mine and make changes.

Can you direct me as to how to import from excel to my table in access?

Thank you so much.

From what you've said, it sounds as if you need at least the following
tables. PK means primary key, FK means foreign key.

Customers
CustomerID PK
Other fields for address and contact information

Dealers
DealerID PK
DealerName
DealerType
Other fields for address and contact information

Products
ProductID PK
Manufacturer
ProductCode
ProductName
UnitPrice (i.e. the price current this week or month or year)
Other fields (e.g. indicating the applicable tax or discount
regimes, handling charges, etc.
Your Products table seems to contain a foreign key
called ContractID: I guess that this refers to the contract(s)
under which the dealer has agreed to supply each products
to your order, in which case it would be appropriate here.)

Orders
OrderID PK
CustomerID FK
PONumber (the customer's purchase order number)
DealerID FK
DateReceived
Other fields specific to an individual order.
(For instance, if you receive one and only one
invoice from the dealer in respect of each
order, you would include fields here for the
dealer's invoice number and invoice total.
If on the other hand you may get more than one
invoice from the dealer in respect of one order,
or if one invoice from a dealer may include items
in more than one of your orders, you will need
a separate DealerInvoices table.)

OrderDetails
OrderID PK FK )these two fields together
ProductID PK FK )are the primary key
Quantity
UnitPrice (i.e. the unit price charged on the date
of the order)
Maybe other fields, e.g. relating to tax and discount.
This *may* be the right place to store serial numbers.
If you just want to have a record of the serial numbers
of the products supplied against each order, but don't
expect to have to search it or refer to it routinely,
you could just include a text or memo field in OrderDetails,
and type the serial numbers into it as a comma-delimited list,
e.g.
BA97654-32, BA97654-33, BA97654-34
But if you want to be able to search and report on serial
numbers as conveniently as any other data, they need a table
of their own:

SerialNumbers
OrderID PK FK )
ProductID PK FK ) all three fields form the primary key
SerialNumber PK )


I hope this makes sense.

wrote:


Like I said -- I am a novice. Please see below.
Brief flow chart:
1. Purchase order received from customer.
2. Purchase order is assigned a control number and dealer.
3. Information is entered into my Orders form, which creates an
OrderID
that I use for customer invoicing.
4. I transmit via email all of the information contained on the
written
purchase order to the dealer for delivery.
5. When dealer delivers, he emails back to me -
a. Ship Date
b. Serial Numbers for that purchase order
c. His vendor invoice number(s)
d. His vendor cost(s) per invoice number
6. When I receive delivery info, I input, either manually or if
possible have computer link excel to access, the info in 5 above.
This
information is forwarded via email (all of my SQL info) to corporate.
That
is corporate's signal to pay dealer his share based on purchase order
amount
and his costs.
7. I generate an invoice to the customer for payment.



Either I don't understand what you're doing, or there are some
serious
problems with your data structure.

The SQL statement from your query shows that ControlNum is a field
in
the Customers table, and not - as you stated - a unique identifier
of a
purchase order. If PONumber stands for "purchase order", it's very
odd
to have this field in the Customers table: normally it would go in
the
Orders table to link your Order record to the purchase order issued
by
the Customer. But maybe no one ever does business with you a second
time<g>.

I put the ControlNum in the Customers table because I
did
not know how to do otherwise. It is possible to have a repeat
customer;
but
our business is with the government and, as a rule, is not the same
customer. The ControlNum is "my" main point of control. I receive a
purchase order from the government and assign that customer a control
number. Whenever I am searching for any information pertaining to the
purchase order, we use ControlNum rather than the government purchase
order
number.

By the way, when you use the term "purchase order" do you mean the
entity modelled by your Orders table, or are "purchase orders" and
"orders" different things?

Purchase orders are received from the customer.
Orders
are
created when I enter the information from the purchase order.

Also, your JD Invoice Number and JD Invoice Amount fields are in the
Dealer table. This makes no sense, unless you never use a Dealer
more
than once.

Again, I was uncertain as to where to put that
information.
Yes, the dealer will be used many times. When I receive a "purchase
order",
I assign a control number (unique to the purchase order) and dealer,
and
email this via my excel template to the dealer. The dealer -- once
delivery
is completed -- emails back either the form with my 4 fields
completed --
or
can send info back in a separate worksheet. (This is where we began.)
When, and only when, the dealer lets me know that he has delivered AND
sent
me the 4 pertinent items needed, can he receive payment for the
delivery.
The way he receives payment is that I must transmit all of the info in
the
SQL above - along with the 4 fields completed - to corporate and they
issue
credit.

And there doesn't seem to be anywhere to store the serial numbers
you
want the dealer to send you.

The only place that they will be visible is in my
OrderDetails.
I will manually input the serial numbers sent back to me via dealer
into
the
original order entry form. I am using the Order ID as my invoice
number
sent to the customer for payment.

It's always best to get the data structure right before trying to do
the
fancy stuff.

I began this project with absolutely no idea of what or
how
this
was to be accomplished. Thus, my tables leave room for improvement.

Again, thank you for your patience and willingness to help
me. I
have suddenly run out of time in my quest to complete this in a good,
workable fashion. I am 2 weeks shy of needing to complete.

THANKS



On Wed, 8 Feb 2006 20:06:21 -0600, "jwrnana"
<[email protected]>
wrote:


Replies below. As I am answering you, I think that I am indeed
raising
more
questions than even the ones you asked.