Choosing a package, return all items

[Guest]

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.

 

[Guest]

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

 

[Guest]

Klatuu,

Thank you for your assumptions! I have none of those things and will remedy
it right away and get moving on this issue. I will be back if I come accross
problems. I will also reply again to say thank you again if I get it right
the first time!

Teri.

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.

 

[Guest]

If you get it right the first time, you are way ahead of me

I don't know how far along you are in your development, but here are some
basics of order entry.
You need two tables
Order Header - Contains information about the order, Sold to, Ship to, Date,
etc.
Order Detail - Related to the Order Header Table by the Order Header's
primary key. Contains information on each item ordered, item number,
quantity, description, price, etc.

You also need access to other tables in your database. Ususally a Customer
table, an inventory table, in your case, you inventory should also have a kit
table that knows all the items in the inventory required to make up a
"package".

You need an Order form based on your Order Table. This form needs a subform
based on the Order Item table.

One basic mistake made in this scenerio is to have fields in your tables for
the extened price for an item (quantitiy * item price), or an order total
(total exteneded prices for all items + tax + shipping, etc). Don't do it!
It is never a good idea nor is there ever any justification for an exception
to the rule "Never carry any Calculated Value in a database". Calculate them
when you need them, like on the form, the packing slip, or any reports.

As to showing the extended pricing calculations on the form and sub form,
look in the Order forms in the Northwind database. There is a great example
there.

Klatuu,

Thank you for your assumptions! I have none of those things and will remedy
it right away and get moving on this issue. I will be back if I come accross
problems. I will also reply again to say thank you again if I get it right
the first time!

Teri.

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.

 

[Guest]

Klatuu,

Here I am again, confused. Taking it one step at a time so I don't get too
confused, I need to create a "Kit" table. The table I have started includes
the following: KitID, OrderDetailID, ProductID, SerielNumbersID and KitName.
I believe these are the items I need, but am unsure how to link these name
with the items in my Product table to pull the correct items. The kit names
are also in my Product table. Do I need to take them out of there and just
have them listed in my Kit table? If this is the case, I am not sure how to
have them appear in the combo box in my subform to be able to choose one of
them. Can you tell I am confused/frustrated?

Thank you,
Teri.

Klatuu,

Thank you for your assumptions! I have none of those things and will remedy
it right away and get moving on this issue. I will be back if I come accross
problems. I will also reply again to say thank you again if I get it right
the first time!

Teri.

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.

 

[Guest]

Most of what you have identified for your Kit table is not relevant.
A Kit is actually an inventory item. It should have most of the attributes
of any other inventory item. Your inventory table should have a field that
identifies an item as being a Kit. That way, whenever you are processing
your orders and you put in an item code, you should check that field to see
if it is a kit. The Kit table is used to identify all the inventory items
that make up the kit. So, all you really need in that table is:
Kit_ID - This would be the the primay key in the inventory table for the
item that is a kit.
Item_ID - The primary key in the inventory table for a component of the kit
Item_Qty - The number of this inventory item required in the kit.

All the other information should already be in the inventory table.
It should not have any reference to the Order Detail. The Order Detail,
however, should reference the Kit table. That is because Order one line item
contains one inventory item and one inventory item may be ordered by multiple
order line items.

Klatuu,

Here I am again, confused. Taking it one step at a time so I don't get too
confused, I need to create a "Kit" table. The table I have started includes
the following: KitID, OrderDetailID, ProductID, SerielNumbersID and KitName.
I believe these are the items I need, but am unsure how to link these name
with the items in my Product table to pull the correct items. The kit names
are also in my Product table. Do I need to take them out of there and just
have them listed in my Kit table? If this is the case, I am not sure how to
have them appear in the combo box in my subform to be able to choose one of
them. Can you tell I am confused/frustrated?

Thank you,
Teri.

Klatuu,

Thank you for your assumptions! I have none of those things and will remedy
it right away and get moving on this issue. I will be back if I come accross
problems. I will also reply again to say thank you again if I get it right
the first time!

Teri.

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

:

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.

 

[Guest]

Okay. My kit table now includes only: KitID, ProductID, and ItemQuantity.
I am not sure I understand how this table is going to pull the items listed
in the Product Table that go with each kit. I'm not sure I understand what
you mean by the following:
I know I sound like a total idiot, but I am just so frustrated with this
database I can't think straight, and I need to get it done by Friday
afternoon.

Thanks again for your help Klatuu!

Teri.

Most of what you have identified for your Kit table is not relevant.
A Kit is actually an inventory item. It should have most of the attributes
of any other inventory item. Your inventory table should have a field that
identifies an item as being a Kit. That way, whenever you are processing
your orders and you put in an item code, you should check that field to see
if it is a kit. The Kit table is used to identify all the inventory items
that make up the kit. So, all you really need in that table is:
Kit_ID - This would be the the primay key in the inventory table for the
item that is a kit.
Item_ID - The primary key in the inventory table for a component of the kit
Item_Qty - The number of this inventory item required in the kit.

All the other information should already be in the inventory table.
It should not have any reference to the Order Detail. The Order Detail,
however, should reference the Kit table. That is because Order one line item
contains one inventory item and one inventory item may be ordered by multiple
order line items.

Klatuu,

Here I am again, confused. Taking it one step at a time so I don't get too
confused, I need to create a "Kit" table. The table I have started includes
the following: KitID, OrderDetailID, ProductID, SerielNumbersID and KitName.
I believe these are the items I need, but am unsure how to link these name
with the items in my Product table to pull the correct items. The kit names
are also in my Product table. Do I need to take them out of there and just
have them listed in my Kit table? If this is the case, I am not sure how to
have them appear in the combo box in my subform to be able to choose one of
them. Can you tell I am confused/frustrated?

Thank you,
Teri.

Klatuu,

Thank you for your assumptions! I have none of those things and will remedy
it right away and get moving on this issue. I will be back if I come accross
problems. I will also reply again to say thank you again if I get it right
the first time!

Teri.

:

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

:

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.

 

[Guest]

Klatuu,

I don't think I mentioned the fact that pretty much any item which belongs
in a certain kit may also belong in at least one other kit. I don't know if
that makes a difference, but I felt the need to mention it just in case.

Teri.

Okay. My kit table now includes only: KitID, ProductID, and ItemQuantity.
I am not sure I understand how this table is going to pull the items listed
in the Product Table that go with each kit. I'm not sure I understand what
you mean by the following:
I know I sound like a total idiot, but I am just so frustrated with this
database I can't think straight, and I need to get it done by Friday
afternoon.

Thanks again for your help Klatuu!

Teri.

Most of what you have identified for your Kit table is not relevant.
A Kit is actually an inventory item. It should have most of the attributes
of any other inventory item. Your inventory table should have a field that
identifies an item as being a Kit. That way, whenever you are processing
your orders and you put in an item code, you should check that field to see
if it is a kit. The Kit table is used to identify all the inventory items
that make up the kit. So, all you really need in that table is:
Kit_ID - This would be the the primay key in the inventory table for the
item that is a kit.
Item_ID - The primary key in the inventory table for a component of the kit
Item_Qty - The number of this inventory item required in the kit.

All the other information should already be in the inventory table.
It should not have any reference to the Order Detail. The Order Detail,
however, should reference the Kit table. That is because Order one line item
contains one inventory item and one inventory item may be ordered by multiple
order line items.

Klatuu,

Here I am again, confused. Taking it one step at a time so I don't get too
confused, I need to create a "Kit" table. The table I have started includes
the following: KitID, OrderDetailID, ProductID, SerielNumbersID and KitName.
I believe these are the items I need, but am unsure how to link these name
with the items in my Product table to pull the correct items. The kit names
are also in my Product table. Do I need to take them out of there and just
have them listed in my Kit table? If this is the case, I am not sure how to
have them appear in the combo box in my subform to be able to choose one of
them. Can you tell I am confused/frustrated?

Thank you,
Teri.

:

Klatuu,

Thank you for your assumptions! I have none of those things and will remedy
it right away and get moving on this issue. I will be back if I come accross
problems. I will also reply again to say thank you again if I get it right
the first time!

Teri.

:

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

:

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.

 

[Guest]

Which Friday in October do you plan to complete this?
If someone has given you a deadline like that, their expectations are
unreasonable. From where you appear to be, even if you had MVP level skills,
a complete package would take a couple of months.

Now to answer your questions.
The kit table is basicaly a "lookup table". It doesn't really have any
information in it. It is just a way to know what makes up a kit and where to
find the information on the items in the kit.
Any time you are working with kits, you can use a query that joins the
inventory table and the kit table using the Item_ID in the kit table and the
inventory table.
It is not easy to give detailed answers without a detailed question. Not a
complaint, just want you to understand that if my answers seem vague, I am
working with only partial information.

Try this as an exercise. Create a select query as I described above. From
the kit table, include Kit_ID, Item_ID, and quantity. From the inventory
table, select item description and item price. In the criteria row of the
Kit_ID column in the query builder put in:
[Enter Kit ID]
Now, run the query. When it asks for a Kit ID, give it a number that is a
kit. See what you get back.

Okay. My kit table now includes only: KitID, ProductID, and ItemQuantity.
I am not sure I understand how this table is going to pull the items listed
in the Product Table that go with each kit. I'm not sure I understand what
you mean by the following:
I know I sound like a total idiot, but I am just so frustrated with this
database I can't think straight, and I need to get it done by Friday
afternoon.

Thanks again for your help Klatuu!

Teri.

Most of what you have identified for your Kit table is not relevant.
A Kit is actually an inventory item. It should have most of the attributes
of any other inventory item. Your inventory table should have a field that
identifies an item as being a Kit. That way, whenever you are processing
your orders and you put in an item code, you should check that field to see
if it is a kit. The Kit table is used to identify all the inventory items
that make up the kit. So, all you really need in that table is:
Kit_ID - This would be the the primay key in the inventory table for the
item that is a kit.
Item_ID - The primary key in the inventory table for a component of the kit
Item_Qty - The number of this inventory item required in the kit.

All the other information should already be in the inventory table.
It should not have any reference to the Order Detail. The Order Detail,
however, should reference the Kit table. That is because Order one line item
contains one inventory item and one inventory item may be ordered by multiple
order line items.

Klatuu,

Here I am again, confused. Taking it one step at a time so I don't get too
confused, I need to create a "Kit" table. The table I have started includes
the following: KitID, OrderDetailID, ProductID, SerielNumbersID and KitName.
I believe these are the items I need, but am unsure how to link these name
with the items in my Product table to pull the correct items. The kit names
are also in my Product table. Do I need to take them out of there and just
have them listed in my Kit table? If this is the case, I am not sure how to
have them appear in the combo box in my subform to be able to choose one of
them. Can you tell I am confused/frustrated?

Thank you,
Teri.

:

Klatuu,

Thank you for your assumptions! I have none of those things and will remedy
it right away and get moving on this issue. I will be back if I come accross
problems. I will also reply again to say thank you again if I get it right
the first time!

Teri.

:

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

:

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.

 

[Guest]

I haven't even looked at your entire response yet. I just wanted to let you
know that I was not given the deadline by anyone but myself. This Friday is
my last day at this job and I really wanted to have this one done before I
left. I am now going to read your response and jut set my mind to getting as
much done as I can before I leave and let it go after that.

Teri.

Which Friday in October do you plan to complete this?
If someone has given you a deadline like that, their expectations are
unreasonable. From where you appear to be, even if you had MVP level skills,
a complete package would take a couple of months.

Now to answer your questions.
The kit table is basicaly a "lookup table". It doesn't really have any
information in it. It is just a way to know what makes up a kit and where to
find the information on the items in the kit.
Any time you are working with kits, you can use a query that joins the
inventory table and the kit table using the Item_ID in the kit table and the
inventory table.
It is not easy to give detailed answers without a detailed question. Not a
complaint, just want you to understand that if my answers seem vague, I am
working with only partial information.

Try this as an exercise. Create a select query as I described above. From
the kit table, include Kit_ID, Item_ID, and quantity. From the inventory
table, select item description and item price. In the criteria row of the
Kit_ID column in the query builder put in:
[Enter Kit ID]
Now, run the query. When it asks for a Kit ID, give it a number that is a
kit. See what you get back.

Okay. My kit table now includes only: KitID, ProductID, and ItemQuantity.
I am not sure I understand how this table is going to pull the items listed
in the Product Table that go with each kit. I'm not sure I understand what
you mean by the following:

"So, all you really need in that table is:
Kit_ID - This would be the the primay key in the inventory table for the
item that is a kit.
Item_ID - The primary key in the inventory table for a component of the kit
Item_Qty - The number of this inventory item required in the kit."

I know I sound like a total idiot, but I am just so frustrated with this
database I can't think straight, and I need to get it done by Friday
afternoon.

Thanks again for your help Klatuu!

Teri.

Most of what you have identified for your Kit table is not relevant.
A Kit is actually an inventory item. It should have most of the attributes
of any other inventory item. Your inventory table should have a field that
identifies an item as being a Kit. That way, whenever you are processing
your orders and you put in an item code, you should check that field to see
if it is a kit. The Kit table is used to identify all the inventory items
that make up the kit. So, all you really need in that table is:
Kit_ID - This would be the the primay key in the inventory table for the
item that is a kit.
Item_ID - The primary key in the inventory table for a component of the kit
Item_Qty - The number of this inventory item required in the kit.

All the other information should already be in the inventory table.
It should not have any reference to the Order Detail. The Order Detail,
however, should reference the Kit table. That is because Order one line item
contains one inventory item and one inventory item may be ordered by multiple
order line items.


:

Klatuu,

Here I am again, confused. Taking it one step at a time so I don't get too
confused, I need to create a "Kit" table. The table I have started includes
the following: KitID, OrderDetailID, ProductID, SerielNumbersID and KitName.
I believe these are the items I need, but am unsure how to link these name
with the items in my Product table to pull the correct items. The kit names
are also in my Product table. Do I need to take them out of there and just
have them listed in my Kit table? If this is the case, I am not sure how to
have them appear in the combo box in my subform to be able to choose one of
them. Can you tell I am confused/frustrated?

Thank you,
Teri.

:

Klatuu,

Thank you for your assumptions! I have none of those things and will remedy
it right away and get moving on this issue. I will be back if I come accross
problems. I will also reply again to say thank you again if I get it right
the first time!

Teri.

:

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

:

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.

 

[Guest]

Klatuu:

After reading your response, I thought it might be a good idea to take a
stop back before trying to go any further forward. I have downloaded
template 01018547 (For customer orders) from Microsoft.com. For the most
part it is what I need. The database contains the following tables (fields
listed too):

Customers Table: Customer ID; Company Name; First Name; Last Name; Billing
Address; City; State/Province; Zip Code; Website; Phone Number; Fax Number;
Ship Address; Ship City; Ship State/Province; Ship Zip Code

Employees Table: EmployeeID; First Name; Last Name; Title; Work Phone

Order Details Table: OrderDetailID; OrderID; ProductID; Quantity; Unit
Price; Price (this is a calculated field, multiplies quantity and unit
price); Discount

Orders Table: OrderID; CustomerID; EmployeeID; Order Date; PO Number; Ship
Date; Shipping MethodID; Sales Tax Rate

Our Company Info table: SetupID; Company Name; Address; City; State; Postal
Code; Phone Number; Fax Number (I don't have any issues with this one at this
point)

Payment Methods Table: Payment MethodID; Payment Method

Payments Table: PaymentID; OrderID; Payment Amount; Payment Date; Status
Credit Card #; Cardholder Name; Card Exp Date; Payment Method ID; Credit Card?

Products Table: ProductID; Product Name; Unit Price

Shipping Methods Table: Shipping MethodID; Shipping Method

Additional tables that I have created are:

Seriel Number Table: Seriel Number ID; Order Detail ID; and Seriel Number

Software Version Table: SWVersion ID; Order Detail ID; and Software Version

Valid Seriel Number Table: Valid Seriel ID; Order Detail ID; Seriel Numer ID

The last post was created as a result of another post that no one ever
finished helping me with and I have reposted.

None of the payment/price tables or fields are currently being used, though
I do plan to leave them where they are at. This database is to be used for
nothing but generating packing slips and tracking where each item with a
seriel number has been for the moment.

My biggest issue at the moment is that I need to let Access know that when I
select the CCUSB package I need not only a CCUSB (with its seriel number)
listed on my packing slip, but also a certain lead set, a certain set of
electrodes, a manual, a pen, and software (with the version number). I also
need to keep in mind a few other things. First, I have 8 different kits that
will need to be pulled together. Second, most of these kits have some of the
same stuff in them (ie - every kits gets a manual, a pen, and the software).
Third, I need to be able to pull these individual items by themselves too (a
customer may only want to order 2 copies of the software, no kits).

Does this help at all? My brain is fried! I am beginning to believe that I
will never come even close to understanding this whole Access thing.

Thank you again for any help you can give me, I can't begin to express how
much I appreciate it!

Teri.

Which Friday in October do you plan to complete this?
If someone has given you a deadline like that, their expectations are
unreasonable. From where you appear to be, even if you had MVP level skills,
a complete package would take a couple of months.

Now to answer your questions.
The kit table is basicaly a "lookup table". It doesn't really have any
information in it. It is just a way to know what makes up a kit and where to
find the information on the items in the kit.
Any time you are working with kits, you can use a query that joins the
inventory table and the kit table using the Item_ID in the kit table and the
inventory table.
It is not easy to give detailed answers without a detailed question. Not a
complaint, just want you to understand that if my answers seem vague, I am
working with only partial information.

Try this as an exercise. Create a select query as I described above. From
the kit table, include Kit_ID, Item_ID, and quantity. From the inventory
table, select item description and item price. In the criteria row of the
Kit_ID column in the query builder put in:
[Enter Kit ID]
Now, run the query. When it asks for a Kit ID, give it a number that is a
kit. See what you get back.

Okay. My kit table now includes only: KitID, ProductID, and ItemQuantity.
I am not sure I understand how this table is going to pull the items listed
in the Product Table that go with each kit. I'm not sure I understand what
you mean by the following:

"So, all you really need in that table is:
Kit_ID - This would be the the primay key in the inventory table for the
item that is a kit.
Item_ID - The primary key in the inventory table for a component of the kit
Item_Qty - The number of this inventory item required in the kit."

I know I sound like a total idiot, but I am just so frustrated with this
database I can't think straight, and I need to get it done by Friday
afternoon.

Thanks again for your help Klatuu!

Teri.

Most of what you have identified for your Kit table is not relevant.
A Kit is actually an inventory item. It should have most of the attributes
of any other inventory item. Your inventory table should have a field that
identifies an item as being a Kit. That way, whenever you are processing
your orders and you put in an item code, you should check that field to see
if it is a kit. The Kit table is used to identify all the inventory items
that make up the kit. So, all you really need in that table is:
Kit_ID - This would be the the primay key in the inventory table for the
item that is a kit.
Item_ID - The primary key in the inventory table for a component of the kit
Item_Qty - The number of this inventory item required in the kit.

All the other information should already be in the inventory table.
It should not have any reference to the Order Detail. The Order Detail,
however, should reference the Kit table. That is because Order one line item
contains one inventory item and one inventory item may be ordered by multiple
order line items.


:

Klatuu,

Here I am again, confused. Taking it one step at a time so I don't get too
confused, I need to create a "Kit" table. The table I have started includes
the following: KitID, OrderDetailID, ProductID, SerielNumbersID and KitName.
I believe these are the items I need, but am unsure how to link these name
with the items in my Product table to pull the correct items. The kit names
are also in my Product table. Do I need to take them out of there and just
have them listed in my Kit table? If this is the case, I am not sure how to
have them appear in the combo box in my subform to be able to choose one of
them. Can you tell I am confused/frustrated?

Thank you,
Teri.

:

Klatuu,

Thank you for your assumptions! I have none of those things and will remedy
it right away and get moving on this issue. I will be back if I come accross
problems. I will also reply again to say thank you again if I get it right
the first time!

Teri.

:

I assume you have a kit table that defines all the items to be included for a
package. I assume your Order Entry from is a form/subform where the order
header info is in the form and the line items are in the sub form. If any of
the above is incorrect, you need to change your design.

There will be some details to work out, but the basic approach would be to
create an parameterized append query that would gather information from your
Order Header Form, your Order Detail sub form, your kit table, your inventory
table, and where ever else it may need to go to get the information and
append it to your order detail table.

So, in the After Update event of the control where you enter the package
identifier, the code would need to identify that a package code was entered,
run the append query that would create the new records in the table, and
requery the subform to show the newly created records.

:

Here I am again, still working on my shipping database. I have a need to be
able to select a package in my Order form (for example CCUSB) and have
Access pull together all items that should be in that package (for example
the CCUSB itself [along with it's seriel number], lead set, stress kit,
cable, belt, manual, pen, and software [along with version number]) into the
order and be able to list them on my packing slip too.

Is this possible? Silly question. Virtually all things are possible in
Access, it is just a matter of knowing how to accomplish these things. I do
not possess this knowledge, so any help I could get on this issue would be
greatly appreciated!

Thank you very much in advance for any help.

Teri.