Track Production using linked table from QODBC

[flwrgrl]

I am a complete newbie to Access. I have been trying to create my production database for months, to no avail. Here is my situation:



I work in a job shop foundry where we base all of our production on the customers specific orders. We have ~ 6500 items, ~400 customers, and usually ~ 100-200 items on order at a give time. A very small business.

We use QuickBooks for all order entry and invoicing.

I have a successful QODBC link (read-only), allowing me to view all orders in Access using the SalesOrderLine table. I have created a query to parse out the info I want from that over-blown table. The query currently has ~ 75 line items that are on order. There is a Primary Key in the SalesOrderLine table. I won't be able to type it in as it is too long and clumsy, but it does give each line item its own key.

This is what I want to track:

When we recieve an order for multiple pieces of one item, we have to make it, usually over several days' time. (If I can figure that out, I will dive into tracking the shipping of the castings, which, I theorize will be similar to tracking production.) By knowing how many pieces are left to make and their due dates, we could also ease the process of scheduling the next day's production.

Here are my issues:

1. Since I am using a read-only liked table, is there any need to normalize the orders portion of my project? My SalesOrderLine table includes all the information that I need to know. And really, 90% is based on that order. Ship-to, ship via, all these things depend on the order, perhaps not the order line, but on the order. From what I can tell, normalizing reduces the chance of making a mistake. Well, I can't change the info in the read-only table, so there won't be any mistakes made. I can link to a Customer table and an Item table and proceed with normalization, but my SalesOrderLine will still contain most of the information. Would that actually make any difference?
2. How do I
   1. Connect my SalesOrderLine query to my Production? I tried a maketable query to put the PK in a new table for production entry, but since my SalesOrderLine is linked and constantly changing, my production table would be constantly out of date. And when I run the queryagain, all old info is deleted.
   2. Set up the real meat of my database: Track the Production, enter both date and quantity in the production? (scrap and rework also need to be entered, theroetically similar to production)
   3. Retain the information for future reference?

Clear as mud, right? Am I on the right track...at all? Any advice will be greatly appreciated, but please keep in mind that I am very new to all things database.

Thanks!

[orange]

Have you looked at any of the free data models at http://www.databaseanswers.org ?
eg http://www.databaseanswers.org/data_...ders/index.htm
It may give you something to compare your tables/relationships with.

Normalization will solve many issues where table structures are involved.

Just out of curiosity, how do you schedule your production without knowing what has to be built or what parts are left on that Order??? It seems you must be doing something (relatively right) since you're still in business.

I think you'll get more interest and participation from the group if you show us a picture of your tables and relationships.

Also, you might want to read the first 3 topics here regarding Normalization and Modeling. It's a great refresher in any event.
http://www.rogersaccesslibrary.com/forum/topic238.html

and this one http://www.databaseanswers.org/approach2db_design.htm

[flwrgrl]

Thanks for the feedback orange. You gave me those links in another post I made, and I found them very educational and helpful. So far they have opened more questions but I will look at them some more.


We currently use a hand-written system. I make identical two cards for every part ordered on which we write in the daily production. (A picture of the card is attached. I have it in my head when planning the database. I need to come up with a new look, I think to make it all work, but I haven't gotten that far yet.) One card is used to plan production and one is used to track shipping. We use two cards because they are sorted in a different order for each task. The person scheduling production for the next day first writes in the day's production and scrap on his card, goes through the cards, compares the due dates, and writes down how many we still need to make for each part for those due soonest. I then write the daily production in on my card so that when it is time to ship, we have a record of how many are made. I also track how many are swhipped on my card. It is a time consuming and mistakes are easily made. However, it is the system we have used for a very long time, and yes, we have been successful. A lot of the success is due to the man in charge, who has an excellent memory when it comes to knowing his business and what his customers need. In attempting to create this database, I am messing with "what works" and I don't want to be the one to take us down! But I also know that one piece of information (daily production) is the only one not in the computer where it could be used to make all the other decisions and planning. It seems so easy in my head, but there are a lot of parts.


As I mentioned, the table that comes in from QB is huge with a lot of blank columns and information I don't need. I created a query to include just the information I need.
My SalesOrderLine Query:

SELECT
SalesOrderLine.FQPrimaryKey,
SalesOrderLine.CustomerRefFullName,
SalesOrderLine.TxnDate,
SalesOrderLine.RefNumber,
SalesOrderLine.PONumber,
SalesOrderLine.SalesOrderLineDesc,
SalesOrderLine.SalesOrderLineQuantity,
SalesOrderLine.SalesOrderLineRate,
SalesOrderLine.SalesOrderLineInvoiced,
SalesOrderLine.SalesOrderLineIsManuallyClosed,
SalesOrderLine.CustomFieldSalesOrderLineOn,
SalesOrderLine.CustomFieldSalesOrderLineAlloy,
CDate(SalesOrderLine!CustomFieldSalesOrderLineDueD atebyItem) AS DueDate,
SalesOrderLine.CustomFieldSalesOrderLineWeight,
SalesOrderLine.CustomFieldSalesOrderLineSpecialIns tructions,
SalesOrderLine.CustomFieldCompanyNotes,
SalesOrderLine.ShipAddressAddr1,
SalesOrderLine.ShipAddressAddr2,
SalesOrderLine.ShipAddressAddr3,
SalesOrderLine.ShipAddressAddr4,
SalesOrderLine.ShipAddressAddr5,
SalesOrderLine.ShipAddressCity,
SalesOrderLine.ShipAddressState,
SalesOrderLine.ShipAddressPostalCode,
SalesOrderLine.ShipAddressCountry,
SalesOrderLine.ShipMethodRefFullName,
SalesOrderLine.CustomFieldFreightCharges,
Right(SalesOrderLine!SalesOrderLineItemRefFullName ,Len(SalesOrderLine!SalesOrderLineItemRefFullName)-InStrRev(SalesOrderLine!SalesOrderLineItemRefFullN ame,":")) AS Item


FROM SalesOrderLine


WHERE
(((SalesOrderLine.SalesOrderLineDesc) Not Like "*METAL SURCHARGE*"
And (SalesOrderLine.SalesOrderLineDesc) Not Like "*PATTERN*"
And (SalesOrderLine.SalesOrderLineDesc) Not Like "*FEE*"
And (SalesOrderLine.SalesOrderLineDesc) Not Like "*SUBCONTRACT*"
And (SalesOrderLine.SalesOrderLineDesc) Not Like "*SHIPPING CONTAINER*")
AND ((SalesOrderLine.SalesOrderLineInvoiced)<[SalesOrderLineQuantity])
AND ((SalesOrderLine.SalesOrderLineIsManuallyClosed)<= 0));

Originally I exported my open orders report from QuickBooks to Excel, used a macro to make it look right, and added columns for all the little boxes on the card. They were named productiondate1, productionsdate2, etc etc. No, it wasn't normalized (I hadn't read that yet), but it worked until I dicovered that I couldn't use a wild card to search all the boxes (columns) for the date I wanted to create the prodsheet that is attached. The left portion of the prodsheet is what is filled out in the shop by the guys doing the work. I fill in the right portion and do the calculations.

To be perfectly honest, nothing I have attempted has worked so I really don't have any relationships or tables to show.

Sorry if this is more information than you wanted. I am trying to explain where I am coming from and where I want to go. There are several more papers that we fill out by hand that I would like to generate through the database.

If this is more than can be completed through a forum, please let me know. Maybe I am shooting too high!

Thanks again.

[orange]

I certainly don't want to interfere or jeopardize your production -- and I guess that's your concern as well.
Anyway, the part of your post that got me thinking was the

They were named productiondate1, productionsdate2, etc etc. No, it wasn't normalized (I hadn't read that yet), but it worked until I dicovered that I couldn't use a wild card to search all the boxes

I set up a test to search controls with similar names (product1, product2...) and look for data. It's only a test and I've attached it in Acc2000 format (from 2003).

There is one table and 1 form, with code behind the on current event. If you open the form, and just progress thru the existing 7 records, you'll see info on the immediate window.

Perhaps you could adapt the concept which would allow you to process your "little production1,2,3 boxes" and take whatever action is required. I don't mean to get you off topic, but this might be worth a try.

[flwrgrl]

Since I have fixed the QODBC link, that orginial method isn't in play. Although, I could make new tables containing the columns.

But I may have figured out a different way. Please tell me what you think:

• I used a maketable query to put my PK in a new table - tbl_DailyProduction.
• Then (assuming more orders are entered) I made an unmatched query to pull up the new orders found in the SalesLineOrder Query that are not in tbl_DailyProduction.
• THEN I run an append query to put those new orders in tbl_DailyProduction.
• In order to track the production date AND production qty I created columns for each day of the year.
• Now I can enter the qty in each day's column. Sort by column for each day, or criteria "not is null" to search for a single line item's production dates.

Does this method work as far as you can see?

I still don't know how I will create the form, but if I am this far, I am excited!

[flwrgrl]

The method won't allow me to create a form using the SalesOrderLine Query and the tbl_DailyProduction. If I make tbl_DailyProduction contain ALL my information it might work. I was trying to avoid putting any more info in one table, but Access won't let me combine anything that has to do with the same origin. I will keep working on it...

[orange]

You know your data and your operations much better than me. So, you're going to have to spell it out in small pieces.

The method won't allow me to create a form using the SalesOrderLine Query and the tbl_DailyProduction.

I'm not following, maybe you have to adjust/modify your query or other. What exactly do you need on the Form? Can you describe how the form works with your operations?
Have you decided on some tables and structure?
Are we talking just a piece of your operations and using QB to be the main driver? If QB can handle OrderEntry and Invoicing ( Business and Money), that seems to be a good start. No sense trying to re-invent QB.

Can you post a non-sensitive copy of your db? Some records but nothing confidential etc.

Has anyone else who uses Access along with QB commented? Have you asked specifically for help with Access and some readOnly QB procedures? May be a way to get some insight into options (if there are any).

EDIT: I guess you're familiar with this. I take it is the QODBC you mentioned.
http://www.qodbc.com/qodbcaccess.htm

and https://ipp.developer.intuit.com/008...ows_SDK/010_qb

https://member.developer.intuit.com/...eo.aspx?part=1

[flwrgrl]

I'm glad I'm not the only one who seems befuddled!

All ordering, invoicing, and accounting is completed in QuickBooks. We don't use QuickBooks to track inventory because QB requires the raw materials to be formed into the final product. We make so many different things, in such small quantities, we can't track the raw materials that closely.

In Access, all I want to track is my daily production and scrap. I then want to use that information, paired with the orders table linked from QB, to create several reports:

1. Daily and Monthly Production
2. Daily and Monthly Scrap
3. Schedule Production, i.e. Molder's List
4. Products waiting to ship
5. Perhaps more?

The form that I am trying to make is where I would enter my production each day. My original idea was to base it on the cards that we currently use (attached earlier). I believe I will have to rework that form to properly use the database. But the idea is to enter the daily production easily on a form where it will be compared to the total production for that item and the quantity ordered.

When I tried to make that form based on SalesOrderLine Query and tbl_DailyProduction (the result of the maketable query) (both sources are based on the SalesOrderLine table that is linked from QB), I got an error message:

"You have chosen fields from record sources which the wizard can't connect. You many have chosen fields from a table and from a query based on that table. If so try choosing fields from only the table or only the query."

I tried to upload a mockup of my db, but it is too big (with only 3 small files in it). How can I get around this?

I have seen 2 of the links. I am no where near a developer. And nothing else seems to give me a very deep understanding of how to use the two together. I will check out the video.

[orange]

I don't have access to Quickbooks, but you may be able to take a subset of your records --to reduce file size.
I'm not sure what I can offer, but your approach seems feasible and reasonable.
As long as you can separate which software is controlling what, I think you should be able to create the reports that you need.

As a first step, for your improved understanding, and for reference in future, and for communications with others, I would recommend you document what you system is, what it should be with the improved reports. You might want to have some flow chart type info showing the inputs and the outputs of the various processes. You may want to link/associate each report with a Business process or operation, and show how the report would/could/should be used.

Many times people get turned off with the documentation aspects, but I can say from experience that most people do NOT understand the systems - especially the data. You go to 3 or 4 different people and you get 4 or 5 definitions or descriptions of what a particular piece of data means. And quite often, terms are used for different things at a different stages of operation. You will understand more about your business and the data it needs (and what it currently is using) by going through the documentation process. When discrepancies arise (and they will) question the people involved to get "to the bottom of the issue".

You may want to do some report mock ups to get some buy-in. Keep the users involved. Let them advise you on what is needed. Once the user becomes part of the process, a lot of learning can be done.

Good luck.

[flwrgrl]

orange, I took your advice and created a flow chart. It didn't clear up much, but was still something I should have done a long time ago! As you can see, it isn't terribly involved, so this should be easy, right?

I am reverting to a previous method of organizing my info, with some slight alterations, but I still need some help:


It isn't Normalized. I have tried and tried but the only way I can figure out how to make my database leaves me with 23 columns for 23 different production dates for each product. However, I not only need to know the dates on which the product was made, but also how many of that product were made on that day. So I also have 23 qty coulmns. Currently all these coulmns are in the same table, but I am open to suggestions on that.

For Example:

We will make various quantities of 20 different products in a given day, but for any given product it could be the first time we've made it, or the 10th, or the 23rd.

To make my form, I am using the DATE01, DATE02, DATE03... next to QTY01, QTY02, QTY03..., from the table screenshot attached as seen in the prodcardnotnormalized.jpg attached.

My next hurdle will be to create the Report that sorts for the date, but only gives the QTY for THAT date.

Any suggestions on any of this? Thanks!