| re: Simple Marina/Customs Declaration Database
Lloyd Stevens wrote:
[color=blue]
> 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[/color]
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.
| 
