PDA

View Full Version : Solved: Parent and Child workbook relationships



Aussiebear
12-31-2006, 07:12 AM
I had asked a similar question earlier last month ( yes its 5 mins into 2007 here), relating to security, but now need to ask once again about the setting up of 2 related workbooks.

One being used as a Excel database and the other as an Excel Logic workbook.

Some of you may have been following the Hay deliveries spreadsheet under the Selecting sheets using the value of a combobox thread. So how difficult is it to set up one which records the deliveries and where its stored as a database type workbook and then to use a related logic workbook as a logic to complete all the functions of accepting deliveries, transfers and end of month auditing?

Ted

XLGibbs
12-31-2006, 07:33 AM
Do you mean one to store all of the transfers and such? Basicallly a row by row transaction log of the work?

That is not that too difficult...do you know what you want this extra sheet to look like?

You could hust have that a hidden sheet accept the new data during the file's usage..then copy it to another workbook (all in code) before the file is closed...

Does that sound like I am hearing you right? It is still 2006 here, so forgive me if I am behind the times LOL!

Aussiebear
12-31-2006, 07:59 AM
XLD had suggested the possibility of using 2 workbooks one as a database and the other as a logic book but linked.

The problem as I see it is that I continue to build the current workbook with a number of sheets containing data relating to specific locations or I rebuild a workbook with one sheet containing all the data in a database format and either a seperate workbook or sheet to do all the day by day entries( Deliveries, transfers, end of month audits).

The initial workbook is built upon the premise of using forms to enter data, which I've basicily built and you guys have rebuilt for me. The end of month I've not yet spent much time thinking about but had envisaged a pivot table. Actually hadn't spent much time thinking about it, as I've managed successfully to dodge pivot tables til now.

If I go with a simple one sheet database and pivot table only then there's very little need for vba. Sould I go with a Database and a Logic workbook then there's a great opportunity to put some bells and whitles on the workbooks ( great as in a very good learning opportunity).

Ted

xld
12-31-2006, 08:00 AM
Piece of cake Ted.

The logic workbook would open the data workbook, prfereably using GetOpenFilename to allow browsing and selection of the file, then set a workbook object to point at that newly opened book. Then refer to this workbook object throughout in the code rather than Activeworkbook or Thisworkbook.

xld
12-31-2006, 08:02 AM
Seeing your later post, I wouldn't separate the data book into two, keep all of the related data together, in separate sheets if necessary.

xld
12-31-2006, 08:03 AM
BTW, the forms would be in the logic workbook. Do you have VB6?

Aussiebear
12-31-2006, 08:06 AM
Yes I do, but haven't really got into it as I wanted to use VBA within Excel first

xld
12-31-2006, 08:16 AM
It is just with VB6 you could create an Active X DLL, either for some of the logic, or to use (the more powerful) VB6 forms. That way you can also secure some of your code.

The VB is very similar to VB6, the IDE is similar, it is just much more flexible, although interacting with an application such as Excel adds a level of complexity.

Aussiebear
12-31-2006, 08:27 AM
I'm only just starting to get the hang of VBA....

XLGibbs
12-31-2006, 08:45 AM
I agree, all related data in one file. No need to have a separate "logic" engine and another data storage bin as separate files.

xld
12-31-2006, 09:30 AM
I agree, all related data in one file. No need to have a separate "logic" engine and another data storage bin as separate files.

Then you don't agree, because I said separate the data and the logic.

xld
12-31-2006, 09:34 AM
I'm only just starting to get the hang of VBA....

Moving to VB6 from VBA will be shed-loads easier than learning Access.

XLGibbs
12-31-2006, 09:34 AM
Seeing your later post, I wouldn't separate the data book into two, keep all of the related data together, in separate sheets if necessary.

I was agreeing with this statement you made regarding the related data. I guess I mistyped. Thanks for noting my ignorance to that effect.. :doh:

tstom
12-31-2006, 10:10 AM
Well... Just my opinion. Unless you have very little data, I would separate the data from your users. There are ways to do this and have the data maintained within the same workbook but why bother... The only reason I would consider storing more than a very small amount of data into the front end workbook would be that portability was a limiting factor. If all of your users have access to a "source data location" such as an intra/inter net location, I would store the data in a separate location.

Several things to bear in mind when approaching a project such as this. The most important factor is how you structure your database. There is absolutely no problem using a workbook for a flatfile type database. Though it is possible to create a relational database in Excel, I would not bother. You can also build your house out of lincoln logs.

Excel has built in functionality to allow you to perform queries. This includes PivotQueries. If your data is stored correctly, querying and updating will be a breeze. You can even use native functions in your frontend that will draw out your data from another workbook and then utilize these ranges as ControlSources for your various controls within your forms.

You mentioned that you want to learn some new things?

If this is for 50 or fewer users, I would create an MS database. (mdb file) Of course, Access would be the first choice in creating this database but Access is not neccesary (in the case that you do not have Access). Use ADO and/or MS Query to retrieve data from and send data to your database. ADO can readily query from and send updates to workbooks (closed or open) as well. You can learn the basics of ADO in a very short time.

Irregardless of what choice you end up making, you must structure your database in such as way that does not cause you problems down the road. You methioned month ending. What about quarterly or year ending? If your data is set up correctly, you will have not problems getting whatever data you need for whatever purpose. A properly normalized database will support any reasonable query.

Didn't mean to write a chapter, but I enjoy the design phase of projects more than the coding. It's at least as important and it's more fun. :)

XLGibbs
12-31-2006, 10:15 AM
Didn't mean to write a chapter, but I enjoy the design phase of projects more than the coding. It's at least as important and it's more fun. :)

Excellent points Tom, and I couldn't agree more. Many times, creating a normallized database from the outset is more important than the structure of that which reads it.

I inherited a SQL database at work which was poorly designed, and poorly planned, and 18 months later, I am still undoing the damage caused by lack of foresight in the design phase.

Keeping a minimum amount of data in the user's application is a key point as well that you made. Great thoughts.

xld
12-31-2006, 10:30 AM
Ted,

Regarding Tom's DB/ADO comments, you could easily start with an Excel database, and once you have it flying you could migrate the data a 'real' DB. That way you can compartmentalise the development, and learn ADO later. The query could be used to bring the data back to Excel in exactly the same structure that teh Excel database was in (even though your DB structure would probably be different), so the changes could be minimalised.

Told you this subject would make an interesting thread.

Aussiebear
12-31-2006, 04:21 PM
Tom, Thanks for the input. I believe that we'll have something like 240 individual deliveries (Road trains) of hay to achieve the 6000 tons. All of which will need to be recorded as Date, Contract No, Supplier, Commodity Type, Bale Size, Carrier, Carrier Rego's, Location, Block No, Bale Count & Tonnage.

Then there's the internal transferring of hay between blocks over a proposed time frame of potentially 12 months. This may vary due to climatic changes, availability of alternative feed types, nutritional requirements of the feed rations etc.

The auditing requirments are that at any one time I need to know what commodity type, bale count, tonnage, Contract No, Supplier and Bale Size is stored at any one block. We conduct end of month balances and snap audits (external auditors). So what ever I/ We come up with, its needs to be functional, transparent, and carry a few bells and whistles to impress the natives.

One other thing..... I need to understand it as well.(Which is the biggest drawback).

Ted

Aussiebear
12-31-2006, 11:17 PM
Righto.. who wants to step up to the plate and tell me about the logic page?

tstom
01-01-2007, 01:15 AM
You are going to have to provide more details. With the limited explanation you provided, here is a simple second normal form setup within Excel. There are assumptions that are, if incorrect, will lead us to change the design.

BaleSize in the same for all bales in any given shipment.
CommodityType is the same for any given shipment.
CarrierRegos. I do not know what that is.
Location. Refers to the location of what?
Tonnage. If shipments will be dispersed throughout various blocks, we will need the weight of each bale if you desire to know the total tonnage located within any given block.
String * ?. Your fields should be fixed length.
? A question mark alone in any given field means that I am unsure of what datatype is required.
Delivery_Id and Movement_Id are autogenerated and relate to queries only. The numbers in and of themselves have no significance.

This should probably be broken up into several more tables. However, I do not know enough to do so.

Aussiebear
01-01-2007, 01:28 AM
Okay I'll post a partial excel database. Have a crack at this one then.

xld
01-01-2007, 03:38 AM
Ted,

IMO, the first thing to do is to forget Excel.

Make a list of all of the business areas that you want to cover in this application, the processes that would be involved in, the data items required, and their relationships to each other.

From this you can do two things, define the database, and define the logic processes.

Don't start from where you are, that will inevitably lead to compromising, start from where you want to be.

XLGibbs
01-01-2007, 08:24 AM
It would seem the Userform sequences, as well as data storage, reporting and other benefits could be gained from using Access.

Movement between locations and blocks could be handled with a properly structured table and using update statements to modify the data, reports could query the table(s) grouping and sorting as needed. In access it may be less cumbersome than excel. (I am not a big fan of Access per se, but it is a reasonable UI for someone familiar with VBA..no?)

But if Aussie needs in excel for now, it can still be done.

Aussiebear
01-01-2007, 11:47 AM
I'll have to post over in the Access section then? I'm off to work now but will work on something tonight when I get back.

xld
01-01-2007, 11:50 AM
Why, Access is not needed, it was just a suggestion. Excel will do what you want and you have some experience of that. The product is more important than the tool.

XLGibbs
01-01-2007, 12:15 PM
Why, Access is not needed, it was just a suggestion. Excel will do what you want and you have some experience of that. The product is more important than the tool.

True 'dat Bob.

Aussiebear
01-02-2007, 12:57 AM
Okay so end product needs:

1. Ability to account for all bales in a by block, by location, byContract, by Supplier, by Carrier.

2. End of month auditing. To show bale count and tonnage in any or all of the designated blocks.

3. Ability to transfer bales, between blocks at the same location, between locations. Need to be able to show date, number of bales and an estimated tonnage in any transfer.

4. Ability to account for new deliveries, by recording date, contract No., Supplier, Commodity type, Bale Size, Carrier, Rego of individual loads, Location, block number, bales count and tonnage of every load.

Relationships:
1.Contract Number, Commodity Type.

2. Contract Number, Supplier, Bale Size

3. Carrier Name, Rego ( could be a problem here as some Carriers had more than one truck operating)

4. Deliveries. Date, Contract No., Carrier, Bale Count, Tonnage, Location, Block No,.

Unless someone has a better idea. What else do you need to know

Ted

Dave
01-02-2007, 06:45 AM
Ted it seems like your main concern is how many bales/tonnage are currently at each block and location? Beyond this you need transaction records of bale movements. Perhaps consider keeping only current bale totals info in XL and then "dumping" the rest to Word in the form of a transaction receipt generated by XL. You could have a doc file for each block and/or location/contract/carrier/supplier... however, this isn't necessary and it will slow down record retrieval (ie. opening,searching and closing multi docs vs opening and searching 1 doc)... so it would depend upon your needs. Your receipt records can be gathered together and extracted quite easily for presentation by using a keyword search of the doc(s). If you need to further manipulate this data from Word, it is quite difficult to do and probably should have been stored in XL anyways. I know "Word is not a data base" and there hasn't been any previous suggested use of it, but I thought I'd throw it out there as an option. HTH. Dave

tstom
01-03-2007, 11:46 PM
Okay so end product needs:

1. Ability to account for all bales in a by block, by location, byContract, by Supplier, by Carrier.

2. End of month auditing. To show bale count and tonnage in any or all of the designated blocks.

3. Ability to transfer bales, between blocks at the same location, between locations. Need to be able to show date, number of bales and an estimated tonnage in any transfer.

4. Ability to account for new deliveries, by recording date, contract No., Supplier, Commodity type, Bale Size, Carrier, Rego of individual loads, Location, block number, bales count and tonnage of every load.

I renamed the two tables to bring it in line with your descriptions.
I removed the "Location" field from the Deliveries table and moved it to the transfers table. Added three fields to the transfers table. TransferDate, OriginationId, and BaleCount. OriginationId=DeliveryId. The transfer originated directly from a shipment. OriginationId<>DeliveryId. This was an intratransfer from another Location/Block. The OriginationId will equal the TransferId of its previous location in this instance. These two tables in the attached file will account for all of your query requirements and much more.



4. Ability to account for new deliveries, by recording date, contract No., Supplier, Commodity type, Bale Size, Carrier, Rego of individual loads, Location, block number, bales count and tonnage of every load.


Keep in mind that the location and block number have nothing to do with a delivery in and of itself. In essence, you will be performing an initial transfer from the truck(s) to one or more valid transfer locations whenever you receive a delivery. The logic is "deliveries do not go to locations/blocks, bales do." This explains the absence of location and block number fields in the Deliveries table.

Where are you accounting for the removal of stock?

Bear with me. This may seem overcomplicated but there is an end that will make sense to you.

tstom
01-03-2007, 11:48 PM
See attached

tstom
01-03-2007, 11:52 PM
Unable to get the attachment???

Use this link.

http://home.fuse.net/tstom/FlatFileDB2.xls