Simple Marina/Customs Declaration Database

[Lloyd Stevens]

CustomerTable CustomerTarrifTable TarrifTable WarrantTable

*CustomerID CustomerID ProductName *WarrantID
BoatType *CustomerTarrifID TarrifRate WarrantDate
CustomerName TarrifNo *TarrifNo TarrifNo
QuantitySold quantityPurchas
CIF CIF
Relationships

CustomerTable to CustomerTarrifTable = onetomany
TarrifTable to CustomerTarrifTable = onetomany
TarrifTable to WarrantTable = onetomany
I have set up the previously shown four tables and relationships in a
database for a boat marina to declare what they have bought and sold
to customs. Each product has a unique number called a tarrifNo. And
each time a product is bought from a supplier it is called a warrant
which is entered during the year. At the end of the year the purchases
made by a customer is entered into the database. CIF is a number which
varies for product and is a calculation on price quantity and various
other factors set by cusotoms.

I want to be able to produce a report that outputs all the products
ordered by there tarrifno and then underneath have the warrants made
during the year and then the details of the sellings to the customers

I assume this is done by a query that outputs the relevant details,
and the totals required be done in a report. So I need to produce a
query that outputs all the details in the database. I have tried to do
this using the design view in access but am not achieving the desired
results as I am getting the customertable and customertarriftable
details repeated for each warrant which I do not want. i.e I am
getting the boats purchase repeated. I think it requires an SQL query
but I have little knowledge of SQL and am struggling.

Can anyone offer me any help??

Regards

lloyd

[Salad]

Lloyd Stevens wrote:

CustomerTable CustomerTarrifTable TarrifTable WarrantTable

*CustomerID CustomerID ProductName *WarrantID
BoatType *CustomerTarrifID TarrifRate WarrantDate
CustomerName TarrifNo *TarrifNo TarrifNo
QuantitySold quantityPurchas
CIF CIF
Relationships

CustomerTable to CustomerTarrifTable = onetomany
TarrifTable to CustomerTarrifTable = onetomany
TarrifTable to WarrantTable = onetomany
I have set up the previously shown four tables and relationships in a
database for a boat marina to declare what they have bought and sold
to customs. Each product has a unique number called a tarrifNo. And
each time a product is bought from a supplier it is called a warrant
which is entered during the year. At the end of the year the purchases
made by a customer is entered into the database. CIF is a number which
varies for product and is a calculation on price quantity and various
other factors set by cusotoms.

I want to be able to produce a report that outputs all the products
ordered by there tarrifno and then underneath have the warrants made
during the year and then the details of the sellings to the customers

I assume this is done by a query that outputs the relevant details,
and the totals required be done in a report. So I need to produce a
query that outputs all the details in the database. I have tried to do
this using the design view in access but am not achieving the desired
results as I am getting the customertable and customertarriftable
details repeated for each warrant which I do not want. i.e I am
getting the boats purchase repeated. I think it requires an SQL query
but I have little knowledge of SQL and am struggling.

Can anyone offer me any help??

Regards

lloyd

Whatever you are wanting to do seems pretty simple. I don't know what
boat purchase repreaded means. You don't mention what you want to
filter on. Do you want to print for one customer, all customers, for a
specific tarrifno

Open up your recordsource for the report. You should have all 4 tables
listed. THere should be a line from CustomerTable to
CustomerTarrifTable with the link on CustomerID. The
customerTarrifTable has a line linking the tarrifNo to the TarrifTable.
The tarrif table should have a relation line to the Warrant table
linked on WarrantID.

Drag the fields from the table you want to the columns.

From the ReportMenu, click View/Group&Sort. The first groups should be
CustomerID (or CustomerName). The next group is TarrifNo. In the group
for Tarrifno set header to Yes and put your detail from the
customertarrif &customer table there. In the detail row put the data
from tarrif and warrant table. You may want to separate tarriff from
warrant if the tarriff to warrant is a one to many relationship.
It might be easier to first create a query that links like above. Then
create a new report and use the report wizard and make sure you select
the groupings and sortings as you step through it. Once the wizard is
complete you can shift the fields into the order you want and check the
sort/group from th menu as you want.