Full Version: Subform question
UtterAccess Forums > Microsoft® Access > Access Forms
I have tblOrders and tblOrderDetail. Certain orders have multiple payments making up the total amount. I'd like to keep only final billed (complete) orders in tblOrders and segregrate all details to the other table. I want a form that has OrderID, Customer, etc. based on tbl Orders. I then want a subform with the breakdown of payments' info. My issue (probably a simple one to solve) is that currently I can't type in an OrderID as its tblOrder's PK and is bound. I'd like the user to be able to open the form whenever a mult. payment order is entered, type in the appropriate OrderID (pulling up the general info specified above) and then enter the details. How do I fix the OrderID field?
You need a table for payments, separate from the orders and the order details.
It willl contain a foreign key relating payments to Orders so that each payment can be attributed to the correct Order. It will also contain attribute fields for the payment date and payment amount
All orders are first recorded into the order table and any related details then are recorded into the order detail table.
My tblOrderDetail was going to fill that roll. For instance, if there is an order# 12345 for 30,000 resale, and 24k cost that will include 3 1/3 installment payments...tblOrders would contain the total resale/cost and other info universal for the order. tblOrderDetail would have FK OrderID and would then have something like this:
nstallment Dist. Type Payment Date Invoice Date Resale Cost Profit
12345-11 DP1 8/30/07 (Blank until paid) $10,000 $8,000 2,000
12345-12 DP2
12345-13 Final
Are you saying I should break this down further into another payment table? I think what you're suggesting is what I have done, but I didn't describe it very well in the original post.
I see. Typically, an order detail table has details about an order, such as the items that were included in it (3 books, 2 tables, 1 car, etc., price per unit, etc. etc.) That's why I was confused.
On your case, you are using the order detail table to simply record payments (and I assume nothing else?) for each order.
So, what I see in your example is that you need to include the Primary Key from the order table as a Foreign Key in the order detail table.
You CAN use a self-generated number for your Order Number (as in the 12345 shown). That same value MUST be included in the order detail table as a Foreign Key and it MUST match the primary key exactly.
Instead of what appears in your sample data to be a concatenated value, 12345-11, you need a single field with the value 12345. That allows you to relate the two tables.
The Payment ID "11" is a separate field in the order detail table and it identifies each payment uniquely.
If you need to show payments on a form or report with the concatenation between order id and payment id, that is a separate technique and easily implemented.
That allows you to create your subform so that all and only related order detail records are shown whenever you select an order id in the main form.
On a separate note: You do not need to store the value of resale and profit in this detail table. I'm assuming of course, that you are storing the cost in the order table.
Any time you need to display the break-outs for a payment, you can do the calcuations as needed from the values stored in the order table.
I'm kind of dealing with a non-standard set of products that doesn't allow a straightforward fill in the item, price per unit, etc. I should have been clearer, but the 12345-11 would be the sales order number...the company uses the 11, 12, 13 as distributions that represent different installments for the same order (again, sorry for the lack of clarification on my part). The OrderDetailID itself is a simple autonumber.
think I just set up my form incorrectly. I just want the user to be able to type in the appropriate OrderID located in the main form and then fill in the appropriate details in the subform. Currently, the main form is based off of tblOrders whose PK is "OrderID". Therefore, I can't type in the specific OrderID I'd like to edit. I'm sure it's terribly simple, but I'm new and am not sure if the control source is the problem, or what I'm supposed to do here.
In the order detail table, you need to have a Foreign Key field which contains the same values as the Primary Key field in the order table.
On your order table, that field is called "OrderID", but I'm not clear yet what value(s) it contains. I assume it is the "12345" that you indicate is the order number " if there is an order# 12345 "
That means you must have a field in the order detail table which has the same value "12345" in order to be able to create the relationship between the tables.
If, as it appears, you have values like "12345-11" and "12345-12", but not like "12345" in the order detail table, you can't create the relationship.
Are we tracking together?
Somewhat. Both tblOrders and tblOrderDetail have a generic autonumber for their respective PKs. Most orders require one payment and are complete. Thus, I don't want them in tblOrderDetail. Some orders, however, have the multiple payments. Ultimately, if an order requires more than one payment, I'd like to create a macro where I click a button (i.e. "Multiple Payments?") on frmOrders which will open up frmOrderDetail. I'd like the main frm to automatically contain the OrderID that I was entering on frmOrders before clicking the button, and then fill in the subform with the payment schedule. I'd also like to be able to open up frmOrderDetail and enter an OrderID into the main form just in case I need to edit a particular order.
apologize again, I'm not being very clear and I'm probably confusing the [censored] out of you. The 12345 in the example represented an order's given PurchaseOrder#, not the PK.
If you don't record ALL payments in the detail table, how do you know you've been paid on all orders? Is there some other table where payments are recorded? It certainly seems to me that the idea of recording payments should apply to all payments, not just those where they are broken out into installments.

Edited by: GroverParkGeorge on Mon Aug 13 16:59:46 EDT 2007.
Yes. tblOrders contains all final orders. Most orders have one payment made, so they are billed final when the equipment ships and that's that. For the ones that do not have one single payment I have to handle them differently. They will still have an entry in the main tblOrders. This record will contain the total resale/cost of the entire order. Once all payments are made, the invoice date is entered which signifies the order is billed final. As the installment payments are made, each line of tblOrderDetail will be completed. Once the final payment of the order's schedule is paid (in tblOrderDetail) that will indicate that the order as a whole should be billed final. I have to do something like this for the sake of revenue recognition. A/P department wants a monthly report of which partial orders to back out of revenue, and which complete orders to be added back in (revenue not recognized until order is completely billed through).
"Yes. tblOrders contains all final orders. '
oes that mean you record PAYMENT information for some orders in this table? By this I mean do you have a field in tblOrders for "resale" and a field for "cost" in this orders table? Or do you only enter those amounts in the detail table?
et me just type away here and hope you can derive something from this blurb…
Best as I understand, for this issue you can ignore the tblorderdetail PK (payment line item id) – should be named like ‘OrderDetailID’? perhaps? - not to say delete it, as it will be handy somewhere, but you will need in your ‘tblorderdetail’, a field named 'OrderID'. This should be indexed, allow duplicates yes. That's you're one to many relationship between the Orders and the Payment tables, and the link you could use from the main form (showing the simple order info), and a subform (showing any number of payments for that one item order).
Outright, if you haven't gone too far, I would have to recommend changing tblorderdetail's name to tblorderpayment (or similar). As George stated, most db structures have a 'header' table (customer name, order date, their purchase order number etc...), and a detail table with the list of stuff which would fill the order... and I'd suspect, sometime this may end up being your situation, or even if it is only one 'detail' item, it's a more 'robust' and versatile layout that can cover any possibility…anyway, no one knows what you do better than yourself so, so be it.
Another point with any accounting system is to clarify 'Sales Order' i.e. tblSalesOrder, and 'Purchase Order', i.e. tblPurchaseOrder. These last tid-bits are just from experience, as you may find your 'system' may grow, and if it does, it can become quite confusing with table names that aren't 100% clear. BTW, I'm a lazy typist, and opt for table names like 'tSO', 'tSOb', 'tPO, 'tPOb', tARPay, tARPayB, tAPPay and so on.
With the relationship tblOrders.OrderID 1 - many to tblOrderDetail.OrderID established, if you drop a prebuilt subform (based on the ‘tblOrderDetail’) on to the main form with the wizard button on, you'll be asked to confirm the link, and if all's good, the OrderID will be automagically entered by default (you don't even need to see it on the subform). The 'Order #' (also better named OrderNo), need only be created and recorded on the main form (header) record.
This field ‘OrderNo’ as you desire to be such as 12345-11, would have to be a text field (not the best plan, but it would work), and should be indexed, allow duplicates = no. All payments, be they one or one hundred, should be recorded in to this payments table (through the subform on the main form). We won’t get in to finance charges and such as that’s a whole other can a’ worms.
Another suggestion for the ‘OrderNo’ portion would be two numeric fields, i.e. 12345 as a long integer, and perhaps for ‘11’, a byte, and these two fields could be indexed together, allow duplicates = no. The bonus to this is the 11,12,13 and so on could then be used for casual lookup info on your distributors. On the form, it would just be two text fields with a ‘ – ‘ label in between, so no one is any wiser, not to mention these values can always be concatenated in a query for reports or other processes. The two indexed numeric fields will sort and perform much better than one text, assuming you’re dealing with a few thousands of records.
Gord o!
Thanks for your response. Let's see...I have already set up the one-many relationship using OrderID as you discussed in your 1st paragraph. I'm dealing with a non-standard type of system here. It's not as straightforward as, every order has a PO, and if there's multiple parts to the order the PO stays the same but the SO changes (11, 12, 13). Sometimes with multivendor orders, the PO changes but the SO stays the same (I don't get it why it's set up this way, but it's what I have to work with). Anyway, in my head it's most beneficial for me to have the total resale/cost/profit in tblOrders and to only include in tblOrderDetail the breakdown of any orders that have multiple payments. I feel it would be easier when running reports, as the monthly totals from tblOrders will reflect only orders billed final, and therefore only the revenue that should be recognized. The A/P dept. requires us to send over a report (currently a spreadsheet) that displays the month's orders that must be backed out or put back in to revenue. I figure I'd be able to construct a report using tblOrderDetail this way that would satisfy A/P.
Ounderstand this isn't the most efficient way of doing things and I definitely need to start considering what you described. Is the way I initially planned on doing things going to work (albeit maybe not as smoothly) or is this method just asking for problems and not going to operate?
For my form, I constructed it similarly to your description. I have a header with the OrderID, Customer, and also the TOTAL (more accurately the expected total as numbers can change slightly) for resale/cost/profit. The subform based on tblOrderDetail has OrderDetailID, Payment Date, Invoice Date (to indicate payment being made), and then the resale/cost/profit for each segment of the order.
I hope I've done a decent job explaining things. I guess basically, I want to know if this will work. Also, I still would like to know (regardless of how I end up constructing things) how to be able to type in an OrderID in the header of my form so that it pulls up a specific orders info for me. Thanks again for your feedback.
Another major issue here are all the prior orders. I'm transitioning from an Excel based system so there are a ton of old orders that will be in the db that need to be structured in a way that will work in a database.
Good afternoon/evening - sorry for the slow reply... one of those days it seems.
Well, I can say that if you're going to pull total payments from Orders *and* OrderDetails, it's not going to be pretty. Not to say it can't or hasn't been done, but for sure, I'd strive to keep all the payment info in one place - as to get an accurate summation from a payment in Orders, plus the multiple payments in OrderDetails, would probably require some rather unattractive union queries, which can be a lot more difficult to design and maintain.
Nothing terribly wrong with your basic 'Orders' setup as I understand - especially if the values are only being used as approximations, however do keep in mind, it's quite frowned upon to store values which can be calculated, so if 'the order' is a single 'line item' for 2 widgets selling at $100., say $80. cost, better to only store the 2, the 100 and the 80, then let some form fields (or an underlying query) do the math on the fly and display the 200, 160 and 40.
Pulling data from Excel almost always seems to bring up some 'singular' situations - but best advice I can give is try to think as far down the road... lots of 'what-ifs' and such. You may need to bring in the data and then disect it a bit more first, but for sure, I'd keep every payment record in the OrderDetails.
Interesting challenge - your explanations are fine and the situation indeed 'special' ! wink.gif
Gord o!
Thanks for the additional feedback.
completely understand what you're saying. I'm just wondering if separating all payments into the details table will work given that there are 5000-6000 previous records being imported into the table back to the beginning of '06. Would you suggest I leave any prior orders (that are billed complete) be, and not group their individual payments together? I just can't see how I can do that other than manually. Obviously, I would need to manually do this to the more recent orders that still are waiting on installment payments, which will be a task in itself.
HAs for the calculated fields you mentioned, I'm not sure if that applies here. The orders are based on contracts set up between vendors and customers (my group and the network of specialists/salesmen are basically the agent) where costs and resales are often times different for the same item.
You mentioned that including payments on the two tables will not be pretty. My initial plan was for the main table to (tblOrders) include records that represent a total order. Therefore, it doesn't invoice complete until ALL the details (tblOrderDetail) have. All my month end reports deal with only the complete orders (rev. recognition purposes), so couldn't I create reports just based on tblOrders to avoid the confusion and problems of incorporating tblOrderDetail with it? Now, the actual details of the order are needed for more of a secondary set of reports that must be sent to A/P. Basically, if a downpayment occurs in the month (that does not complete it's parent order), it needs to be included in a report indicating it be backed out of revenue. If an order bills final, the final billing and all of its related downpayments must be on the report indicating they be put back IN to revenue (there are better ways of doing this I realize, but the people in A/P are the kind that don't want any sort of change from what they're used to no matter how silly their procedures are). Anyway, I was thinking I could use tblOrderDetail to construct this particular type of report. Therefore, for reporting purposes the two tables stay separate. Does this make things any less prone to problems down the road?
As we've discussed, the system here is unusual pretty much because Excel has allowed the prior group members to make up their own ways of doing things even though they were nowhere near as efficient or logical as they could be. Leaving me with this project (I've been here 2 months) to try to create a system that works...
Good afternoon / evening yet again.
'd have to say, I'd probably import everything and from within Access, begin to develop some update and append queries that would move everything in to place, even if the '06 orders are done and gone. Without a doubt, someone, someday will ask for everything since the beginning of time - so best to be prepared, and 5 ~ 6 K records is quite small and could really be handled very easily in Access.
If you're flagging the order (tblOrders) as complete by some means, well, nothing wrong with that, and if you're comfortable with the stored values remaining accurate, then all that should be tolerable albeit not the 'ultimate'. The downpayments (from tblOrderDetails) could indeed be run in a seperate query or report and results handled according to AR's needs.
Bottom line to me is to say, you can do whatever works for your situation, but when that 5 - 6K records becomes 50 - 60, and someone asks for a change, it is a lot better to have a system foundation that is sturdy and can be more easily 'adapted'. I'd still aim to have all payments in the OrderDetails... even if it takes a handful of queries or a few hours or even days of work... it may save you weeks later on. wink.gif
Gord o!
Do you have any suggestions as to what kind of update/append queries to develop in order to break down this data? Let's say taht currently here are my fields displaying order information:
rderID (auto - PK)
Type (i.e. "DP1" shows 1st down payment)
SalesBranchID (FK)
Ship To
VendorID (FK)
Serial Number
Date Entered
Est Ship Date
Invoice Date
How would you suggest I break this down? And more importantly, what are some methods that may help me do this accurately? I'm thinking to separate everything from Ship Date on. I would also add "Type" to the Order Details section. Another issue is that there are multivendor orders, so recording more than one vendors is required at times.
Good afternoon,
hat's a tough call for myself without seeing some sample Excel data (which is possible here, but must be non-confidential) - I am a bit concerned with the comment that there's a potential for multiple vendors, and if it is the case, the VendorID (fk) should really be 'sub-tabled' to the Order table as well. I find this is starting to get a little confusing, so if I understand, you're buying say, 10 widgets, possibly 3 from Vendor X and 7 from Vendor Y, then selling them to Sales branch Z? Sales Branch Z is (possibly) making multiple payments on these 10 widgets?
If I have it sort of close, and you do want accurate cost and profit results, there's potentially a whole lot more info needed, i.e. did the vendors sell the widgets to you at the same price, if the widgets were actually in your 'inventory', if you are just reselling without actual inventory, when and how were you paying for the widgets ... you know, things like that. If I'm not following correctly or have it completely inverse, do let me know.
In general (how I've worked on occasion), I start with an import from Excel, and pull the data in to one table in Access. Keep a copy of that table (as a back-up). Run a make table query to build your orders table - for what it's worth this could remain a copy of the original import with a bunch of extra fields, at least for some time, or grab just the fields you really desire. If there's already an orders table, change that to an append query - just watch you're getting all the required data, and also without duplication. Don't forget in append and update queries, you can 'make' values to fill fields as need be, so if you have a field with a required = true, and you're data can't fill it, expressions like:
Expr1: IIF(ISNULL([SomeField]),0,[SomeField]) will work in a query. I have also on occasion, turned off all the required = true properties, then cleaned up the table after the fact, and switched required = true back on again.
Handy also are the InStr, Left, Mid and Right, Access built-in functions in the event you have to deal with something like comma delimited data.
Run another make table query (or again, append), to get the majority of the payment records in to their place in 'orderdetails'- pulling the orderid, paydate, amount. For the cases where there were multiple payments, if these were recorded in separate columns in Excel, they're now in seperate columns in Access, so an append query with the orderid, paydate, amount could now add these rows to the payments table by selecting the appropriate column (field) - (query for an amount value that's not null). Again and again until all payment columns (fields) have been covered.
By the way, watch using the word 'type' in Access as a field name. 'fldType' by example, is a good alternative and will help eliminate errors in VB code some day. wink.gif
Good evening,
You are on the right track with how the current system is set up. The orders deal with equipment. Multivendor orders (or systems orders) are when a customer needs several pieces of equipment in order to set up a larger system (i.e. a conveyor system that packages a product). Since each vendor specializes in certain equipment, the customer needs to receive different pieces of equipment from different vendors. Sometimes it's simple and consists of 2 vendors, a piece of machinery each. Other times an order may involve 10 vendors. One of the tricky things is that these orders are the only instances that for whatever the PO#s do NOT match for each piece of the order. For example, the first order may be "u12345" (standard PO appearance) and the other vendors' POs would be 1U2345, 12u345, etc etc. This causes a huge issue for me with how I'd like to set up the form/subform we've discussed. For every other order (where every piece of one order shares the same PO) I can tie the order and paymentdetails together with a PO. For these systems orders, I have no idea how to tie them together so that all of their pieces can be arranged in the subform while still being tied together.
To respond to another part of your post, the company has no inventory. It goes from Vendor to Customer, without ever passing through a warehouse of ours (Stock orders are an exception, but they are not involved in the db). Also, sales branch refers to the specific location of our company that the order is taking place in, and not anything to do with customer....Anyway, I get a sales order which has the TOTAL resale and cost on it. Currently, I take these numbers and enter in Excel. Resale being what we bill the customer, cost being what the vendor bills us. Let's say a 50% down payment is made. I add a line underneath the original entry on Excel. I use an order history printout to record the actual resale and cost for the downpayment. On the second line the difference remains (the last 50% of the order) in resale and cost and waits until another installment is paid. The majority of the time, vendor AND customer payment terms match. So if we bill the customer 50%, the vendor is billing us 50% at the same time. The multivendors have unmatching terms sometimes. If a vendor bills us at a point in time when we don't bill the customer, then a "vendor payment" line is added and resale/cost/profit is entered at $0. The resale and costs for these orders are recorded when the customer is billed. The system is entirely too awkward and complicated. It's one of those situations where people years ago started making up there way of doing things, and it worked for them because they invente the process, but the process turns out to be very inefficient for anyone new to maintain it.
Oapologize for the length of that description, but I wanted to make sure you somewhat understand the way things are done here. You can probably see why I'm having all of these issues. So let me go back over your plan:
-I should import the data from Excel as is (into a temporary table or one that I'll be using later?)
-Perform make table query...I'm assuming using just the fields I'd like in my tblEquipmentOrders? I don't quite follow the idea of leaving it as the original import and adding "a bunch of extra fields"
-Make table (or append) selecting the fields that I want in tblPaymentDetail
Two add'l things (sorry again for the length of this)..I do have most of the data in a table now, but I'm having trouble appending. Some orders that are already in the db have since had things updated (if they were invoiced since I imported them for instance). Will the append query update these records or do I have to delete the duplicates? Second, for orders with multi payments...in Excel each payment is its own row (not column). Any suggestions as to breaking those down in my tblPaymentDetail?
I appreciate all your help a great deal. Thanks again.
Good morning - thanks for the detailed info as it is helpful to understand everything possible for the situation.

Having seen similar 'issues' before (meaning company developed convoluted processes), I would at this point tend to say it might be very advantageous to stop, take a moment, and look more at all those 'exceptions' which cause the huge issues for you. Depending on how much of a system the company is willing to let you build for them i.e. is this database going to really only do some of the reporting tasks, or (I would think someday be better), take over all the facets of the day to day operations. If for the latter, it sounds to me that the only way to 'do it right' is going to involve setting up a stricter structure, not to mention a handful of processes (how the company will work from now on), in order to assure everything runs well and can provide accurate reporting.

Obviously you understand how this company operations work, so now to concentrate on improving it, and although it may sound like going about things the long way, I'd say you will need to set up more distinct tables.

Take a simple example, with tables for customers, vendors, sales orders, purchase orders, perhaps quotations, and all with 'sub' tables for contacts and details. Inventory should probably be set up as well - although it's an exception, it can be a useful hub with which to control the movement of goods even if there's little or no actual inventory, or 'inventory worth'. Then the dollars and cents side with the AR and AP payments, and so on...

I do not like Excel, having carved my way through these situations before, and have always found it easier to just get everything in to Access first (yes, it's really just a temp table), then eventually disect a copy of it - breaking its contents in to what should make a relational database work. My point with leaving some extra data or adding fields if even only for a moment, is it is sometimes what it takes to get through the initial conversion. Simple example is adding a yes/no field defaulted to yes, to tables you're about to import in to. This can then be used as indicator to quickly verify the results of the import, and also similar can be done to the export table - to then know which rows a query or queries have touched. When you're content you've had complete success, all these additional fields can be tossed.

I can't stress more that regardless of how you approach this, backup your backups. If you run a query and the results are junk, find the problem, apply the fix to another copy, and go again.

Keeping a backup, if referential integrity is setup properly, you should be able to run the append query, it will then say like x number of records can't be appended because of a key issue, and that should be fine. (Keep a back up!) wink.gif Access will just ignore the duplicates and add only the new rows. A quick check and you should see if the results are what you are expecting. Actually, say you're appending from tblNewData to tblOldData, you can make a join between the two tables in the append query, and just set a field with criteria to say where tblOldData.OrderID (whatever key you're working with), Is Null. This should then show only records that don't already exist in the tblOldData.

Payments on rows (hopefully in their own column) may require some VBA code if the i.e. OrderID is not beside each payment. That or criteria that would elliminate null rows or even setting the field you're importing to, to accept everything in the column i.e. a text field, then import everything in the column, sort it, delete the junk, and set the field data type appropriately.

Fun and curious challenge you have here - just take your time, lots of backups, (and notes), and it will come together in time. I am curious to see your Excel format, so if you do feel comfortable, you're welcome to zip and email a snippet.

Best wishes,

Gord o!
The database will basically be taking over the reporting process and maybe filling some other roles along the way. There will still always be the company's "legacy" systems in place which is where the actual equipment orders are entered and edited, which produces the purchase orders, etc etc. As orders are entered I put the relevant info in the spreadsheet. As orders are invoiced (the invoices are reviewed here, vouched by A/P, and sent back having been processed) I update the spreadsheet. At month end, I construct the reports from the spreadsheet (a very tedious process involving a ton of copying and pasting and waht not which was one of the main reasons for this project).
My department does not deal with A/R or A/P aspects of the orders. We don't receive or cut checks or work directly with the general ledger (however we do work with A/P...for instance, every month a revenue recognition report is sent to A/P indicating what orders should have been added or backed out). Our month end reports are largely meant to distribute to the different groups or branches around the country. They use some the reports to determine commission for sales reps and also obviously to use the numbers to guage how well they are doing and to act accordingly. So while accurate numbers are essential, I'm not sure if things like A/R and A/P payments fields are necessary.
One question I have is how to structure tables based on PO# or SO# that you mentioned. Also, another odd thing about the process here is that no reports are really based on customers. Do I still set up a table for customer even though there are a vast amount of customers, and a large amount are one time contracts? You see, the different branches concentrate more on the individual customer level. How would you suggest I handle this? I'm not familiar with 'sub' tables either..
I have the majority of data in access currently (a month's worth needs to be imported). I'd love to get all of the info in correctly, but was thinking I should do that right before I'm ready to start entering data in the db and using it instead of access (i.e. the beginning of a month). Or maybe getting all data in access and simultaneously updating excel and access until I'm ready to launch the db???
HAs for installment payments...there's a few examples on the sample I've attached. Most info is blank, but hopefully this will help.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.