Some access assistance needed

Printable View

November 9th, 2005, 06:07 PM
mohaughn

Some access assistance needed

I'm working on an access database for a software engineering course in my masters. Basically what I'm doing is taking the Microsoft template database called "Orders management database" and extending it to meet my needs and to fix some of the table design. The system will be an order tracking database with some extra fields added to create good business reports in terms of profitability per product, things like that.

One of the issues with that database as it is is that it stores price information in the orderdetails table. I want to get rid of any instances of storing data when that data can be calculated.

So what I've done is create a table called "Pricedetail" it has these fields-

PricedetailID : the key
ProductID : has a many to 1 relationship with the Products table
UnitPrice
PriceStartDate
PriceEndDate

So a single ProductID can have multiple entries in the PriceDetail table. This will allow for product prices to be changed, and historic data is maintained about product pricing. It will also allow for me to calculate old invoice totals instead of storing them. When an invoice is calculated I'll do a between on the invoice date and PriceStartDate and PriceEndDate..

So here is the problem. I'm familiar with using access to process data that already exists in tables. I'm not so familiar with using Forms to enter that data.

I have a Add Order main form which contains a Order detail subforms. The OrderID in the main form is linked to the orderID in the child form. So when you are looking at order #14 it only shows the products that exist in the order details table for order #14. What I'm having problems with is looking up the price from the pricedetail table.

I've used dlookup like this- DLookUp("UnitPrice","PriceDetail","ProductID = " & [ProductID])

Which works, but it doesn't take my date calculations like explain above into consideration. Also it returns an #error? on the last record that is null. I know I can use IFF or NV to overcome this, but not sure of the exact syntax.

I've also read some help manuals that saying using dlookup is not the best way to do this, and it should be done with a query.

So in my query I put the Orders detail table and Price detail table with the productID joined between the two, and I put my between in the criteria for the field "order date". And the query works if I click it and enter the date manually. However, when I set the query to be the Unit Price control source in my form I get a #name? error. If I can get the dlookup syntax nailed down properly I don't even have to mess with a query as I think the performance problems cause by dlookup will be minimal as there are only going to be 10 or 15 product fields for each order at most.

Any help would be appreciated. I'm willing to send the database over to somebody that has a good solution and not just wanting to look at it.
November 9th, 2005, 08:46 PM
mohaughn

I figured it out. I did a combination of both solutions. I used dlookup, and a query.

So my control source for the form is

=IIf(IsNull([ProductID]),[ProductID],DLookUp("UnitPrice","GetProductPrice","ProductID = " & [ProductID]))

And then I have the query GetProductPrice which is what dlookup is calling - the SQl for that is-

SELECT PriceDetail.*, Orders.OrderDate
FROM Orders INNER JOIN ([Order Details] LEFT JOIN PriceDetail ON [Order Details].ProductID = PriceDetail.ProductID) ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate) Between [PriceDetail]![PriceStartDate] And [PriceDetail]![PriceEndDate]));

And it works. Been beating my head against the wall for a day on that one.
November 9th, 2005, 08:51 PM
dinowuff

Sweet disregard the last PM
November 9th, 2005, 09:18 PM
mohaughn

I appreciate the quick response. I'd been working on that for awhile, so I figured I would get it sooner or later.. Here is my next issue.

I now have all of the price information being built dynamically based on the above. So on my subform you see-

ProductName, Quantity, UnitPrice, and a calculated field that is Unit total.

I now need to do a subtotal of "unit total" to be displayed on the main form.

The samples and helps say that you can create a new field in the footer of the subform and use the SUM function. However, as my UnitPrice is a calculated field, SUM doesn't work on it. Any idea of how to get around this?

This one form is taking way to much time!!!
November 10th, 2005, 01:54 AM
nihil

Hi mohaughn,

Quote:

I want to get rid of any instances of storing data when that data can be calculated.

In other words, a classic "third normal form database"? In my experience the last stage in DB design is generally "de-normalisation" Please do not forget to consider the efficiency of holding summary and metadata.

Some questions:

1. Is this a multicurrency system?
2. Will it supply different customers at different prices?
3. Are their variable sales tax rates?

Any chance of seeing your Entity Relationship Diagrams, Data Flow Diagrams and the Data Dictionary?

:)
November 10th, 2005, 04:22 AM
mohaughn

You got it. For this size of a database I really don't see the problem with storing unit price data with each order detail. This way you don't have to maintain price data seperately. But I have to be concerned with how the form of everything is going to be analyzed. I have all of my relationships laid out. I've not done my data dictionary yet. I was wanting to make sure I have enough of the app working before I lock myself into a design.

To answer your questions-

Quote:

1. Is this a multicurrency system?

No. American dollars only. The end application will be used by a local pool construction subcontractor.

Quote:

2. Will it supply different customers at different prices?

No. Right now on the invoice form there is a field to input a discount for that particular item. I currently do not believe that my customer needs this functionality. So I'll probably remove it from the form.

Quote:

3. Are their variable sales tax rates?

No. Everything that is billed is for labor. His customers by the parts and supplies. He picks them up using a PO number, and then just bills back for labor.
November 10th, 2005, 11:21 AM
nihil

Hi mohaughn

Might I suggest you take a step back?

Quote:

I've not done my data dictionary yet. I was wanting to make sure I have enough of the app working before I lock myself into a design.

You don't lock yourself into a design until you start the development. ;)

Just look at any structured design and development methodology? The all have "phases" along the lines of:

1. User requirements specification.
2. Design specification
3. Development
4. Testing
5. Implementataion
6. Post implementation review

You MUST complete phases #1 and #2 before you move to #3 and #4

Two things that struck me about your replies:

1. This is a "subcontractor"................what does that mean? do they work for a main contractor, or directly for the customer? (sorry I don't know US constructiopn terminology)

2. You bill for labour only? materials are collected on behalf of the customer who pays for them directly?

At this stage it seems to me that you don't have any "products" you actually have "contracts" which consist of "jobs" which consist of "work packages" or "tasks" .................you can break it down further if required.

So, another question: Do you bill only on completion, or are there progress payments?

I would love to see the instructions you got for this exercise if that is at all possible.

:)
November 11th, 2005, 08:12 PM
mohaughn

Yes I understand how software development works. However, when you are usually working with a budget you can afford to hire developers that know how to code stuff. I'm not a developer. So I have to make sure that the way I design it, I can actually code. I know how to design it properly so that it can be built, but if I CAN develop it that way it a totally different story. So I did my design, and I'm making sure I can do it, then I will do my formal documentation. Instead of doing all of the formal documentation before I start development. Just leaving myself some flexibility that you otherwise wouldn't have is this were a real development project.

Quote:

At this stage it seems to me that you don't have any "products" you actually have "contracts" which consist of "jobs" which consist of "work packages" or "tasks" .................you can break it down further if required.

My table stucture is already built. These are invoices, and for this project, my product is services. So you can bill it out as a part. For instance, for a specific job you may need 10 linear feet of concrete. On the invoice that will be 10 units of 1 linear sq.ft. So yeah, I'm using "parts" interchangably with "tasks" or "services." Although he could also really sell a part in the future and it would be tracked the same. As it is now he doesn't buy anything, he just shows up and does labor. He goes to his supplier, orders the stuff he needs to do his work, and all of the gets billed directly to the pool company. He doesn't have to manage any of that.

Quote:

So, another question: Do you bill only on completion, or are there progress payments?

This app will only manage invoices. No payment information will be handled. He has his payment stuff all worked out with his accountant and I don't want to get involved with what they are already doing. Maybe in the future, but not for what I'm doing with this class. I have a time constraint that I'm working under, so making it a complete business management system isn't really an option.

Quote:

1. This is a "subcontractor"................what does that mean? do they work for a main contractor, or directly for the customer? (sorry I don't know US constructiopn terminology)

OK, so a home owner wants a pool. They go to the pool company and order what they want. That pool company then pays subscontractors to go and actually do the work. One contractor may dig the hole, another one puts the steal rebar for the shell, another one does the concrete, etc.. This particular company I'm working with does concrete, plumbing, tile, and stone work. Although none of it really matters to the application as it is only concerned with units or parts.