Basic Questions - Beginner to MS access

[speedyrabbit]

Hello I am trying to create a basic application + reporting solution using ms access.



I have a table1 - to store invoice details ( coming from an input form)

Example

Invoice Table
Invoice ID - 00001 , Qty - 2, ShipFrom - Germany, ShipTo - France, etc

"ShipFrom" & "ShipTo" are look up from two other table tbl_Shipto & tbl_ShipFrom

I have another table which stores VAT info by country

VAT Information
Germany 20 % ,France 19 %

My Question: How do I get VAT for both Germany & France from the same table on my report?

Report needs to look like below :

Invoice ID Qty
00001 2

Ship From VAT
Germany 20 %

Ship To VAT
France 19%



Thanks!

[CJ_London]

from your description, it appears your table design is wrong.

I would expect something like

tblCountries
CountryPK autonumber
Country text

tblInvoices
InvoicePK autonumber
InvoiceNum long/text
...
...
ShipFromFK long - link to tblCountries
ShipToFK long- link to tblCountries


tblInvoiceLines
LinePK autonumber
InvoiceFK long - link to tblInvoices
Qty - long
VATCode integer - may not be required but different products can have different VAT rates, HMRC can provide the codes/rates. Either way, VAT is calculated at the product level, not the invoice total level
...
...


tblVAT
VATPK autonumber
CountryFK long - link to tblCountries
VATCode integer - may not be required but different products can have different VAT rates, HMRC can provide the codes/rates
VATRate double


In a query you can have tables in more than once (subsequent tables will be suffixed with _1, _2 etc )

so to get the VAT for each country, in the query grid, drag one copy of tblInvoices and two each of tblCountries and tblVAT

link ShipFromFK to tblCountries.CountryPK, then link tblCountries.CountryPK to tblVAT.CountryFK
and link ShipToFK to tblCountries_1.CountryPK, then link tblCountries_1.CountryPK to tblVAT_1.CountryFK

[ssanfu]

"ShipFrom" & "ShipTo" are look up from two other table tbl_Shipto & tbl_ShipFrom

This sounds a lot like you have look up FIELDs in a table.

You might want to see these sites:
The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm

The Ten Commandments of Access
http://access.mvps.org/access/tencommandments.htm