Parts and prices query

[faca]

Hello!



I'm creating database for car mechanic (actually updating old one)...

I have 3 tables that i need to join together:

ServicePart:
- ServicePartID
- ServiceID
- PartID
- Quantity

Part:
- PartID
- PartNumber
- PartCode
- PartName
- Unit

Price:
- PriceID
- PartID
- PurchasePrice
- PriceValidFrom

In table price i store prices for every part (beacuse prices are changing from time to time i used PriceValidFrom to know which one is last)

In old database i had part info and prices in same table called PartPrice now i separate them in 2 different tables (when price changed i just duplicated previous value and changed price... beacuse of that each part was multiplied many times)
Attachment 34810

In new database i made like this:
Attachment 34811

In ServicePart i store quantity for each part i used

This is how it looks like in old database:
Attachment 34809

the problem is that in new database i don't know how to connect all three tables beacuse everytime i try to make query its grey when i start it.

Any suggestions?

[orange]

You may get some ideas from this data model from Barry Williams' site.

[ArviLaanemets]

It looks like you have a table Service too - from somewhere the FK ServiceID must get values.

Currently you have form for parts (with subform for part prices).
You'll need another pair of forms - form for services with subform for ServiceParts. This will be where most of data entry is done (the parts table is merely a registry used by services form). Look at it this way - the goal is to do services, not to collect parts.

[faca]

It looks like you have a table Service too - from somewhere the FK ServiceID must get values.

Currently you have form for parts (with subform for part prices).
You'll need another pair of forms - form for services with subform for ServiceParts. This will be where most of data entry is done (the parts table is merely a registry used by services form). Look at it this way - the goal is to do services, not to collect parts.

This form for parts is from my old database (i've added screenshot just for example) ..

In my new database i dont know how to connect servicepart, part and price table in query so i can get the same form as in old database


*Service form is already created i just need now to connect this 3 tables and make parts subform in service form

[ArviLaanemets]

You can't edit 2 different tables in same form at same time. With table it is easy - a form can have a single table as source. When you have a query as source, it must be editable. And when it is, you can edit fields of one table in query only. As such query in your case must be aggregate one, there is no chance for it!

Anyway you manage part prices in Parts form, not in Services form. So your choices are:
1. Add a field PurchasePrice into table ServicePart. Base ServiceParts form on table ServicePart.
Create saved queries like (on fly)
qLastPriceDate

SELECT PartID, MAX(PriceBalidFrom) AS LastPriceDate FROM Price GROUP BY PartID

and
qCurrentPrice

SELECT price.PartID, Iif(Nz(lpd.PartID,0) = 0, 0, price.PurchasePrice) AS [PurchasePrice] FROM Price price LEFT JOIN qLastPriceDate lpd ON WHERE lpd.PartID = price.PartID WHERE price.PriceValidFrom = lpd.LastPriceDate

On subform for service parts, write an AfterUpdate event for control linked to combo where you select part. The event checks the value of purchase price of part in control, and when it is empty, sets the value for parts purchase price using DLookup() from qCurrentPrice. It is up to you, are you allowing to edit purchase price afterwards, or you disable editing for the control completly.

2. The another way is to have the price in subform calculated in unbound control, without having the price stored in table ServicePart. For this you need an UDF, which returns the valid price for part at given date. I posted such function in one of forums here lately - check for ValidValue() when you are interested.

[isladogs]

You can't edit 2 different tables in same form at same time. With table it is easy - a form can have a single table as source. When you have a query as source, it must be editable. And when it is, you can edit fields of one table in query only. As such query in your case must be aggregate one, there is no chance for it!

Arvil
Whilst I agree with almost all of your post, your comment that you can only edit fields of one table used in a form query is incorrect.
If the query used as record source is editable then so are all fields used in the form.
That's true whether the join is 1:1 or 1:many;inner or outer join.
Offhand I can't think of an exception to this but perhaps you can.

As you stated aggregate queries are not editable

[faca]

This is what i've done so far.. now i only need parts subform in service form