I've gone through the various videos a few times, trying to get a handle on the structure of things. And this last time I took notes of what I was doing along the way. So I feel more organized. But when I got to the inventory part of it, after building the template, creating the CRM, setting up invoicing, setting up the ordert, orderdetailT, orderdetailQ, OrderdetailF, using the IIF funct, using conditional formatting, etc.. I realized that the order system i have in place wasn't working. When new orders are created, where are the details being entered? It would seem that the details entered go into the order detailsT via the orderdetailsQ. But it's the ordersT that's opened up to look for orders for a client, not the orderdetailsT. Here's how I have things: OrderF is based on orderT. That's matched up with OrderDetailF, via the customerID field. The orderdetailF is based on the orderdetailQ, which is connected to the OrderDetailT. And when order info is entered into the orderdetailsF (subform of ordersF) the info goes into orderdetailsT, not the ordersT (which is the table queried for orders for a client). What am I getting wrong here?
Joe Holland
@Reply 2 years ago
Order details are related to each order and each order is related to a customer.
Sami Shamma
@Reply 2 years ago
In this video, Richard builds a simple invoicing Database. It should clear things for you.
Thank you both for your help. I ended up getting it working not too long after submitting the question. What I did was two things. First, I made a paralell between what was working and what was not working. So I made a paralell between the contact system and the order system. That gave me a better understanding of the structure. And then I included customerid into the order detailQ and orderdetailT. I then put it in the orderdetailF as hidden, and made it's default value to be: =[Forms]![CustomerF]![CustomerID]
Joe Holland
@Reply 2 years ago
CustomerID is usually just linked in the OrderT. OrderT then uses OrderID to link to all the OrderDetailT records using a foreign OrderID. Then you can easily bring CustomerT, OrderT and OrderDetailT all together in a query. You should watch the video Sami suggested to get a better understanding of how it all relates.
George JakesOP
@Reply
2 years ago
Thank you Joe. I'll use that info to find out what I did wrong. And, Sami, which video is it that you're referring to? When I click in the link within your post I just see a page for templates:
TechHelp Free Templates
By Richard Rost Richard Rost on LinkedIn Email Richard Rost 3 years ago
Current Versions of Templates for TechHelp
*****************************
Thank you
George JakesOP
@Reply
2 years ago
Hi Joe, I get what you're saying about the CustomerID ->OrderT->OrderDetailT. But my question is how does the ContactT fit into this? Would that just be another relationship independent of the customerID-OrderT?
Joe Holland
@Reply 2 years ago
Everything in the OrderDetailT can be related to the CustomerT through the OrderT. It may help you to watch the Relationships Video: https://599cd.com/blog/display-article.asp?ID=1724
Joe Holland
@Reply 2 years ago
Joe Holland
@Reply 2 years ago
I built my database 28 years ago, so the names of my tables do not follow Richard's recommendations. Certainly, name them as Richard says. It will make your life easier as you move on in programming.
Joe Holland
@Reply 2 years ago
Joe Holland
@Reply 2 years ago
Joe Holland
@Reply 2 years ago
I posted a simple query bringing together the customer, order, and order detail info. The image after that is the datasheet showing the query results. I hope this helps.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Visitor Forum.