Database Help Much Needed!

[taicho]

Hi, All
I am designing a database to keep track of part prices and their trends, the part numbers and all their related information are stored in a "Unified Parts Database" containing all pertinent information about the parts, their vendor, current price, material, sub-material etc...Now in the new database I am making I wanted to have one table that has these field.

The tables name currently is "Main", fields are as follows:

MainID (Primary Key)
PartNumber
PartDescription
VendorID
UnitAmount
CurrentPrice
Notes
ApprovedBy
ApprovalNumber
ReasonForChange

and then I have a "Prices" table which stores the different price changes for each part number:

PriceID (PKey)
PartNumber
Price
PriceChanged

now I what I am trying to do is have the "Main" table pull all the part numbers and their descriptions from the Unified Parts Table which is a linked table and I am not sure how to get it to fill all this stuff automatically inside a table not a form, would a "Main" Query be the correct way to go and completely skip the "Main" table??

Brain hurt......

 

[neileg]

There's no point in storing data in Main if it's available in a linked table. As you suggest, a query would bring together the data you need. If there is additional data you need to store, you may need Main and it should hold the PK of the linked table.

If you are holding price information as you suggest, you should not hold the part number but the PK from Main. How can you guarantee the part number is unique?

 

[taicho]

There's no point in storing data in Main if it's available in a linked table. As you suggest, a query would bring together the data you need. If there is additional data you need to store, you may need Main and it should hold the PK of the linked table.

If you are holding price information as you suggest, you should not hold the part number but the PK from Main. How can you guarantee the part number is unique?

Thanks for the reply :-D
The reason I thought about creating the "Main" table was because it has some new fields which are not in my "Unified Parts Table/DB" but I was curious - when you say that if I need to use Main table it should have the PK of the linked table do you mean actually use the PK from the UnifiedPTable in one of the fields in "Main"?

 

[taicho]

Another idea...

I was also thinking of making the Main Query from the "Unified Parts Table" and the "Main Table" to merge the data/fields from "Unified Parts Table" and get the fields from "Main Table" then turning the query into a make-table query one time and then changing that same query into an Update Query but it just seems like it would be alot of maintanence because if someone were to add a part to the Unified Parts Table in the Unified Parts DB it would have no way of signaling to this seperate DB to run its Update-query unless there is a way this can be done that I am not seeing...?

P.S. Is it possible to make the default value for my Prices field in the "Unified Parts Table" be the most recent price of that part from the "Prices table" in the other DB?

 

[neileg]

I wouldn't use a make table query. So long as the Unified Parts Table/DB is available for linking, a simple selct query will gather all the data you need in one place. Since there will be a one to one relationship between the records in the Unified Parts Table and Main, you need to link the two together. This will involve holding the PK of Unified Parts Table as a foreign key in Main.

However, this prompts another question. If the Unified Parts Table/DB holds a lot of data that you need, why don't you just stick to this db and add in the new data? Is there a special reason why you need separate databases? If you have different users with different needs, you can create different front end applications and hold all the data in one back end db and link the front ends to this.

Finding the latest price form your price table can be done either with an aggregate query (sometimes called a totals query) and using the Max option on the date field. Alternatively, you could use DMax() to return the same data.

 

[taicho]

Sounds good...

I was also starting to think that the most logical thing would be to do this project in the Unified Parts DB but something still puzzles me, you see the reason I want/need the most current price from the "Prices table" copied into the "Price" field in the "Unified Parts Table" is because I have yet another sales database which is functional and that takes whatever price is in the Price field in the Unified Parts Table for the corresponding Part # when making a Purchase Order, i.e. when you select Part # 0000 it pulls the price so it can use it as the value for multiplying based on quantity of the order of that part...So let's say I were to move the "Prices" project into the Unified P DB, I'm still not sure how I would route or copy the most recent price from the "Prices" table into the "Price" field of the "Unified Parts Table" for the corresponding part #. Excuse the bad writing...

Brain hurt more...

 

[taicho]

Just trying to keep thread up high...

 

[neileg]

How many databses do you need?

Your sales databse can extract the price data via a query. You still don't need to store this.

 

[taicho]

Well since my last post I've gotten more of a grip on everything but there is something I still can't figure out - My Query looks like this:

PriceID;Part Number;Price;Price Added

Now in the query setup I've set it up so that totals are on and "Price Added" is set to Last and from everything I've read it should only show me the last date for each Part Number instead of showing me all the Prices for each part sorted by last date...What am I missing here so I can only filter in the most recent price for each part number?

Thanks for the help :-D

 

[neileg]

Don't use Last. First and Last are unreliable. Use Max on the date. Any aggregate function will operate only on identical records. Since [Price] will be different in each record, you will get the latest date for each price which will probably mean returning every record.

You have two choices. One is to run a Max query that returns only the fields you need to identify the record. (You seem to be persisting in using the part number when this should be the PK from main). Then join this query back to the Prices table to retrieve the Price. Or, you can use DMax() as a criterion in your query on the Dateadded field. DMax() is less efficient than a query, but it will be easier for you to implement. Look at DMax in Access Help.

 

[taicho]

Problems have been solved! Just wanted to make the known so this thread can die peacefully, thank you very much for advice Neil - much of it was used in the solution(s) to my problem(s)... :-D