Table, query, ref and calc problem. Desperate.

[caman74]

Hello.
I need your help to make this work.
Any ideas that may help me, would be great.

At this time I do have a form "TrspOppdrag".

Shown below...

On this form there are some text boxes:
[Fra postnr] and [Til sted] which are the "zip" and "town" of the sender
[Til postnr] and [Til sted] which are the "zip" and "town" of the receiver.
[Antall kll],[Antall pll],[BruttoVekt] and [VolumVekt] are "pieces", "pallets", "gross weight" and "volum weight".
[KostTrsp] is the same as "Transportation costs"
I also got a combo [Transportør] = "carrier".

The form has its unique number in [Transport nummer](txtbox)

The [Transportør] combo gets its values from:

SELECT [Transportør Spørring].Kode, [Transportør Spørring].KundeNr, [Transportør Spørring].Dek, [Transportør Spørring].VårtKundeNr, [Transportør Spørring].Telefonnummer, [Transportør Spørring].Faksnummer, * FROM [Transportør Spørring] ORDER BY [Transportør Spørring].

I have a Table calles "Post" which contains teo rows:
"Postnr" and "Poststed" = "zip" and "town".
--------------------------------

What I need:

-To make either a Table or a Excel doc, containing every carrier (Transportør), with their "sones" and prices.

E.g. Carrier= GPX, Zip= 2020, Town=Skedsmokorset, Weight 50-100kg, Price=150 and so on...

When I enter the Zip "2020" into [Til Postnr], selects either [Antall kll],[Antall pll] or both, e.g. [Antall kll]= 3, [BruttoVekt] And/Or [VolumVekt] e.g. [BruttoVekt]= 75 and [Transportør]=GPX the the query would find the value (price) 150, and put it into [TrspKost]

Simplyfying: [Til postnr]=2020, [Antall kll]=5, [BruttoVekt]=75, [Transportør]=GPX = [TrspKost]: 150

------------------

Any ideas on how to make this work.
Shall I make several Tables for each carrier, or one for all of them, and so on.


Thank You in advance.

 

[GingerR]

This is a little confusing because of the language, but basic Relational Database design/Normalization rules would dictate that you have a table with a unique list of Transporters:

TransporterID
TransporterName
TransporterAddress

Then if a Transporter can cover many zones, you'd have another table:

TransporterID
Zone
Cost

...something like that. Then use joins in queries to output every transporter and their zones/costs, and use these tables as well to fill combo boxes after one is chosen. NOt sure if this is answering your question totally, but again it's hard to tell what you're up to. Maybe if you spell out the basics of your table structure we can help more.

Thanks--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

caman74

Your system is apparently very well thought out with a lot of attention spent on details. However, SELECT statement only depicts one table, [Transportør Spørring]...

SELECT [Transportør Spørring].Kode, [Transportør Spørring].KundeNr, [Transportør Spørring].Dek, [Transportør Spørring].VårtKundeNr, [Transportør Spørring].Telefonnummer, [Transportør Spørring].Faksnummer, * FROM [Transportør Spørring] ORDER BY [Transportør Spørring].

Your system is either an Order Entry or a Shipping system. You apparently want your system to be "smart".

Now I am hoping that your ystem includes more than one table, and your database has been "normalized", and you have defined your relationships....
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

...Moving on

You can perform calculations on the form through a few technicques...
- Read the required values directly from the table(s)
- Read the values on the form

My prefrence is to read from the table. You can use "domain aggregates", DLookup, DSum. But I find a more flexible approach is to create "function" to perform the calculation. The function approach is more flexible in that you can call the function from various places -- on a form, in a report and from a query.

The function is best created in a module ("View" -> "Database Objects" -> "Module")

And example...

Function CalcTrspKost(lngPrimaryKey as Long) as Currenc

'You pass a key field or several key fields
'The function returns a value, in this case a currency value

'You can use a recordset approach
Dim rst as DAO.Recrdset, strSQL as String

strSQL = "SELECT Sum(YourField) As TotlTrspKost FROM..."
Set rst = CurrentDB.Openrecordset(strSQL)

With rst
   .MoveLast
   CalcTrspKost = !TotlTrspKost
   .Close
End With

Set rst = Nothing

'OR you can use domain aggregates
CalcTrspKost = DSum("YourField", "YourTable", "YourPrimaryKey = " & lngPrimaryKey)

'These are just simple examples.  The function can be
'much more complex.

'Domain aggregates may be easier to code
'Domain aggregates tend to be slower, and only reference
'one table, and one column (but the WHERE clause can 
'be complicated)

'The record set can use any valid SQL statement which
'means it can involve more than one column and more 
'than one table

End Function

Hope this points you in the correct direction
Richard

 

[willir]

Caman74

...I forgot.

I alluded to but did not provide details on the alternate approach for grabbing totals. You can reference the control object "fields" on the form by their names.

Here is a post on the subject...
Add data to a table from an equation, how?

The only thing to add to it is that when adding values, you have to accommodate null values. Use the Nz function for this...

=Nz([YourField1], 0) + Nz([YourField2], 0)...

If the value is null, Nz will substitute a 0 so you do not see errors on the form,

Richard

 

[caman74]

Thank you for now, GingerR and Richard.
I'll look into you sugestions tonight after work, but by glansing at your suggestions, I belive I'm going to create what I want.

Richard:
I'll take your comment; "Your system is apparently very well thought out with a lot of attention spent on details.", as a compliment.
Thank you.
My database includes several tables, forms and so on. All relationships are defined. I belive I've done what is needed to make this database run smoothly.
But, in early december, I din now nothing about access or databasees in general, so I am a real novice.
All ideas and comments, are taken to serious consideration.

For now

All the best

Chris

 

[willir]

Chris
Good job! As stated, your form and application appears to be well thought out.

Good luck in your project, and let us know if we can help you further.

Richard