A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Order Entry type form to add to three tables



 
 
Thread Tools Display Modes
  #1  
Old February 9th, 2010, 12:04 AM posted to microsoft.public.access.tablesdbdesign
CuriousMark
external usenet poster
 
Posts: 61
Default Order Entry type form to add to three tables

I have been working on this database for several years, learning as I go.
However I have been stuck on the problem of the best way to design a form for
data entry into three tables. I now give up and put the whole thing on the
Forum in the hope that someone can help me with the design. For simplicity, I
will illustrate using the "Order Entry" style database, and limit the fields
to only those necessary.

There are four tables with the following fields:

tblCustomers - CustomerID (pk), CustomerName
tblOrders - OrderID (pk), OrderDate, CustomerID (fk)
tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk)
tblProducts - ProductID (pk), ProductName

One customer is associated with one or more orders, and one order can have
one or more products. The user should be able to create a new order by
specifying the date and the customer, and then one or more products.

I have a main form with subform to do this. The main form is bound to a
query that joins the tblOrders and tblCustomers tables, and the subform is
bound to a query that joins the tblOrderProd and tblProducts tables. The
subform is linked to the main form through the OrderID field.

Main form query:
SELECT tblOrders.OrderDate, tblCustomers.CustomerName
FROM tblCustomers INNER JOIN tblOrders
ON tblCustomers.CustomerID = tblOrders.CustomerID

Subform query:
SELECT tblOrderProd.ProductID, tblProducts.ProductName, tblOrderProd.OrderID
FROM tblProducts INNER JOIN tblOrderProd
ON tblProducts.ProductID = tblOrderProd.ProductID

This setup works great for adding records, and when I enter a ProductID in
the subform it will show the ProductName but the problem is that this design
means a new customer is added for each new order: I can't figure out how to
search based on the entry for an existing customer, use it if it exists, or
add it if it is new.

My hunch is that I should unbind both forms, using Append queries to enter
the data in the related three fields. If so, then how do I find out the new
OrderID to use to append to tblOrderProd? And how do I find out the
CustomerID to use to append to the tblOrders if it is a new Customer? Then
how do I progress through each ProductID entered in the subform when
appending to the tblOrderProd table?

Thanks very much.

  #2  
Old February 9th, 2010, 01:07 AM posted to microsoft.public.access.tablesdbdesign
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default Order Entry type form to add to three tables

Are you using the LinkChild/LinkMaster properties in your subform?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201002/1

  #3  
Old February 9th, 2010, 01:20 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Order Entry type form to add to three tables

The subform is linked to the main form through the OrderID field.
How can this be as your query for main form does not have OrderID field.

how to search based on the entry for an existing customer,

Use a combo to select customer.

Main form – tblOrders - tblCustomers
Combo – select customer
Enter OrderDate - automatic generate OrderID
- required delivery - payment - shipping method - etc.

Make sure you set the Master/Child links on form/subform on OrderID.

Subform – tblOrderProd - tblOrders - tblProducts
Combo – select product
QTY – Size – Color -

--
Build a little, test a little.


"CuriousMark" wrote:

I have been working on this database for several years, learning as I go.
However I have been stuck on the problem of the best way to design a form for
data entry into three tables. I now give up and put the whole thing on the
Forum in the hope that someone can help me with the design. For simplicity, I
will illustrate using the "Order Entry" style database, and limit the fields
to only those necessary.

There are four tables with the following fields:

tblCustomers - CustomerID (pk), CustomerName
tblOrders - OrderID (pk), OrderDate, CustomerID (fk)
tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk)
tblProducts - ProductID (pk), ProductName

One customer is associated with one or more orders, and one order can have
one or more products. The user should be able to create a new order by
specifying the date and the customer, and then one or more products.

I have a main form with subform to do this. The main form is bound to a
query that joins the tblOrders and tblCustomers tables, and the subform is
bound to a query that joins the tblOrderProd and tblProducts tables. The
subform is linked to the main form through the OrderID field.

Main form query:
SELECT tblOrders.OrderDate, tblCustomers.CustomerName
FROM tblCustomers INNER JOIN tblOrders
ON tblCustomers.CustomerID = tblOrders.CustomerID

Subform query:
SELECT tblOrderProd.ProductID, tblProducts.ProductName, tblOrderProd.OrderID
FROM tblProducts INNER JOIN tblOrderProd
ON tblProducts.ProductID = tblOrderProd.ProductID

This setup works great for adding records, and when I enter a ProductID in
the subform it will show the ProductName but the problem is that this design
means a new customer is added for each new order: I can't figure out how to
search based on the entry for an existing customer, use it if it exists, or
add it if it is new.

My hunch is that I should unbind both forms, using Append queries to enter
the data in the related three fields. If so, then how do I find out the new
OrderID to use to append to tblOrderProd? And how do I find out the
CustomerID to use to append to the tblOrders if it is a new Customer? Then
how do I progress through each ProductID entered in the subform when
appending to the tblOrderProd table?

Thanks very much.

  #4  
Old February 9th, 2010, 01:37 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Order Entry type form to add to three tables

Hello Mark!

You deserve an A+ for Stick-To-It ness for working on the database for
several years. However, wouldn't it be a relief to get it done and move on?
I would like to offer to work with you and finish your database. I provide
help with Access, Excel and Word applications. My fee to finish the database
with you would be very reasonable. Contact me and let's get it done!!!

Steve



"CuriousMark" wrote in message
...
I have been working on this database for several years, learning as I go.
However I have been stuck on the problem of the best way to design a form
for
data entry into three tables. I now give up and put the whole thing on the
Forum in the hope that someone can help me with the design. For
simplicity, I
will illustrate using the "Order Entry" style database, and limit the
fields
to only those necessary.

There are four tables with the following fields:

tblCustomers - CustomerID (pk), CustomerName
tblOrders - OrderID (pk), OrderDate, CustomerID (fk)
tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk)
tblProducts - ProductID (pk), ProductName

One customer is associated with one or more orders, and one order can have
one or more products. The user should be able to create a new order by
specifying the date and the customer, and then one or more products.

I have a main form with subform to do this. The main form is bound to a
query that joins the tblOrders and tblCustomers tables, and the subform is
bound to a query that joins the tblOrderProd and tblProducts tables. The
subform is linked to the main form through the OrderID field.

Main form query:
SELECT tblOrders.OrderDate, tblCustomers.CustomerName
FROM tblCustomers INNER JOIN tblOrders
ON tblCustomers.CustomerID = tblOrders.CustomerID

Subform query:
SELECT tblOrderProd.ProductID, tblProducts.ProductName,
tblOrderProd.OrderID
FROM tblProducts INNER JOIN tblOrderProd
ON tblProducts.ProductID = tblOrderProd.ProductID

This setup works great for adding records, and when I enter a ProductID in
the subform it will show the ProductName but the problem is that this
design
means a new customer is added for each new order: I can't figure out how
to
search based on the entry for an existing customer, use it if it exists,
or
add it if it is new.

My hunch is that I should unbind both forms, using Append queries to enter
the data in the related three fields. If so, then how do I find out the
new
OrderID to use to append to tblOrderProd? And how do I find out the
CustomerID to use to append to the tblOrders if it is a new Customer? Then
how do I progress through each ProductID entered in the subform when
appending to the tblOrderProd table?

Thanks very much.



  #5  
Old February 9th, 2010, 01:52 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Order Entry type form to add to three tables

These newsgroups are "staffed" by (mostly) volunteers. The Code of Conduct
precludes soliciting paid work.

Before you sign any contracts, you might want to consider whether you want
to hire someone who doesn't feel it necessary to follow the rules.

Why do you have a main form with Customer and Order joined? If the main
form is your Order form, you can use a combobox to list possible customers
and select which one it is, to add to your Order record (customerID).

If you'd rather have Customer as your main form, your Order is your subform
and the OrderDetail is the subsubform.

Using subforms and the Parent/Child joins between main and sub (and between
sub and subsub) will allow Access to properly record the "parent" to which a
particular Order Detail or Order belongs

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"CuriousMark" wrote in message
...
I have been working on this database for several years, learning as I go.
However I have been stuck on the problem of the best way to design a form
for
data entry into three tables. I now give up and put the whole thing on the
Forum in the hope that someone can help me with the design. For
simplicity, I
will illustrate using the "Order Entry" style database, and limit the
fields
to only those necessary.

There are four tables with the following fields:

tblCustomers - CustomerID (pk), CustomerName
tblOrders - OrderID (pk), OrderDate, CustomerID (fk)
tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk)
tblProducts - ProductID (pk), ProductName

One customer is associated with one or more orders, and one order can have
one or more products. The user should be able to create a new order by
specifying the date and the customer, and then one or more products.

I have a main form with subform to do this. The main form is bound to a
query that joins the tblOrders and tblCustomers tables, and the subform is
bound to a query that joins the tblOrderProd and tblProducts tables. The
subform is linked to the main form through the OrderID field.

Main form query:
SELECT tblOrders.OrderDate, tblCustomers.CustomerName
FROM tblCustomers INNER JOIN tblOrders
ON tblCustomers.CustomerID = tblOrders.CustomerID

Subform query:
SELECT tblOrderProd.ProductID, tblProducts.ProductName,
tblOrderProd.OrderID
FROM tblProducts INNER JOIN tblOrderProd
ON tblProducts.ProductID = tblOrderProd.ProductID

This setup works great for adding records, and when I enter a ProductID in
the subform it will show the ProductName but the problem is that this
design
means a new customer is added for each new order: I can't figure out how
to
search based on the entry for an existing customer, use it if it exists,
or
add it if it is new.

My hunch is that I should unbind both forms, using Append queries to enter
the data in the related three fields. If so, then how do I find out the
new
OrderID to use to append to tblOrderProd? And how do I find out the
CustomerID to use to append to the tblOrders if it is a new Customer? Then
how do I progress through each ProductID entered in the subform when
appending to the tblOrderProd table?

Thanks very much.



  #6  
Old February 9th, 2010, 03:03 AM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Order Entry type form to add to three tables

"Steve" wrote in message
...
Hello Mark!

You deserve an A+ for Stick-To-It ness for working on the database for

and an F if he accepts your offer.
several years. However, wouldn't it be a relief to get it done and move
on?

how about it if you moved on?
I would like to offer to work with you and finish your database. I provide
help with Access, Excel and Word applications. My fee to finish the
database with you would be very reasonable. Contact me and let's get it
done!!!

Steve


Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP


  #7  
Old February 9th, 2010, 09:13 AM posted to microsoft.public.access.tablesdbdesign
StopThisAdvertising
external usenet poster
 
Posts: 334
Default Order Entry type form to add to three tables


"Steve" schreef in bericht ...
Hello Mark!

You deserve an A+ for Stick-To-It ness for working on the database for several years. However, wouldn't it be a relief to get it
done and move on? I would like to offer to work with you and finish your database. I provide help with Access, Excel and Word
applications. My fee to finish the database with you would be very reasonable. Contact me and let's get it done!!!


--
Get lost $teve. Go away... far away....

Again... Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

This newsgroup is meant for FREE help..
No-one wants you here... no-one needs you here...
OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)

Arno R


  #8  
Old February 10th, 2010, 05:21 PM posted to microsoft.public.access.tablesdbdesign
CuriousMark
external usenet poster
 
Posts: 61
Default Order Entry type form to add to three tables

yes, but that's not the problem. The problem is to select or add a new
customer.

"PieterLinden via AccessMonster.com" wrote:

Are you using the LinkChild/LinkMaster properties in your subform?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201002/1

.

  #9  
Old February 10th, 2010, 05:21 PM posted to microsoft.public.access.tablesdbdesign
CuriousMark
external usenet poster
 
Posts: 61
Default Order Entry type form to add to three tables



"KARL DEWEY" wrote:

The subform is linked to the main form through the OrderID field.

How can this be as your query for main form does not have OrderID field.


The OrderID field is autonumber generated as soon as data is entered in the
form.

how to search based on the entry for an existing customer,

Use a combo to select customer.

Main form – tblOrders - tblCustomers
Combo – select customer
Enter OrderDate - automatic generate OrderID
- required delivery - payment - shipping method - etc.


how do I have it add a new customer if the customer doesn't exist?

Make sure you set the Master/Child links on form/subform on OrderID.

Subform – tblOrderProd - tblOrders - tblProducts
Combo – select product
QTY – Size – Color -

--
Build a little, test a little.


"CuriousMark" wrote:

I have been working on this database for several years, learning as I go.
However I have been stuck on the problem of the best way to design a form for
data entry into three tables. I now give up and put the whole thing on the
Forum in the hope that someone can help me with the design. For simplicity, I
will illustrate using the "Order Entry" style database, and limit the fields
to only those necessary.

There are four tables with the following fields:

tblCustomers - CustomerID (pk), CustomerName
tblOrders - OrderID (pk), OrderDate, CustomerID (fk)
tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk)
tblProducts - ProductID (pk), ProductName

One customer is associated with one or more orders, and one order can have
one or more products. The user should be able to create a new order by
specifying the date and the customer, and then one or more products.

I have a main form with subform to do this. The main form is bound to a
query that joins the tblOrders and tblCustomers tables, and the subform is
bound to a query that joins the tblOrderProd and tblProducts tables. The
subform is linked to the main form through the OrderID field.

Main form query:
SELECT tblOrders.OrderDate, tblCustomers.CustomerName
FROM tblCustomers INNER JOIN tblOrders
ON tblCustomers.CustomerID = tblOrders.CustomerID

Subform query:
SELECT tblOrderProd.ProductID, tblProducts.ProductName, tblOrderProd.OrderID
FROM tblProducts INNER JOIN tblOrderProd
ON tblProducts.ProductID = tblOrderProd.ProductID

This setup works great for adding records, and when I enter a ProductID in
the subform it will show the ProductName but the problem is that this design
means a new customer is added for each new order: I can't figure out how to
search based on the entry for an existing customer, use it if it exists, or
add it if it is new.

My hunch is that I should unbind both forms, using Append queries to enter
the data in the related three fields. If so, then how do I find out the new
OrderID to use to append to tblOrderProd? And how do I find out the
CustomerID to use to append to the tblOrders if it is a new Customer? Then
how do I progress through each ProductID entered in the subform when
appending to the tblOrderProd table?

Thanks very much.

  #10  
Old February 10th, 2010, 05:23 PM posted to microsoft.public.access.tablesdbdesign
CuriousMark
external usenet poster
 
Posts: 61
Default Order Entry type form to add to three tables

Steve,

Thanks but not thanks. I guess you don't understand.....I wouldn't have
learned as much as I have if I just hired someone else to do it for me. That
takes all the fun and learning out of it.

"StopThisAdvertising" wrote:


"Steve" schreef in bericht ...
Hello Mark!

You deserve an A+ for Stick-To-It ness for working on the database for several years. However, wouldn't it be a relief to get it
done and move on? I would like to offer to work with you and finish your database. I provide help with Access, Excel and Word
applications. My fee to finish the database with you would be very reasonable. Contact me and let's get it done!!!


--
Get lost $teve. Go away... far away....

Again... Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

This newsgroup is meant for FREE help..
No-one wants you here... no-one needs you here...
OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)

Arno R


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.