A
Arkwrite
Hi, I should point out that i am quite new to access and am using the
2000 version.
I have a database setup however i get multiple 'entries' when i run a
report. My database is as follows:
I have a table setup for entering incoming orders - called 'Job Book'.
Each order has a job/order number and is assigned automatically with
autonumber, which is also the primary key. There are also date ordered,
purchase ordernumber, contact name, companyname, cost, description and
date required fields.
I also have a table - 'stock book' which all orders are placed in. Each
job can have multiple components from different suppliers and the order
refernce used is the 'Job number' Dte ordered, supplier, quantity,
product and cost and date received are also other field in the stock
book. I have no primary key for this table as the job number can be
entered more that once due to different components and suppliers.
There is aslo a 'Job Hours' table in which all hours worked by any number
of employees are stored and calculated. Again the 'Job number' can appear
more than once so no promary key is set and any job can have more than
one employee's hours accounted for.
The problem is that is i run a query based on the Job Number and then
generate a report from the query i get a seperate record for each stock
book entry and each employee hours entry, each one with the job book
details - giving at least 4 entries. I have tried the no duplicates on
each field in the report but still the duplicate data is displayed.
I would like just 1 entry form the job book which details the order and
then each entry from the stock book table for each procuct/supplier with
the cost of items bought for a peticular job. Then i would like the
employees hours to be shown and calculated. finnaly i would like a grand
total of all the supplier costs and the billing hours total.
I think maybe the problem is in the relationships between the tables but
i am not sure. Any help would be greatly appreciated.
2000 version.
I have a database setup however i get multiple 'entries' when i run a
report. My database is as follows:
I have a table setup for entering incoming orders - called 'Job Book'.
Each order has a job/order number and is assigned automatically with
autonumber, which is also the primary key. There are also date ordered,
purchase ordernumber, contact name, companyname, cost, description and
date required fields.
I also have a table - 'stock book' which all orders are placed in. Each
job can have multiple components from different suppliers and the order
refernce used is the 'Job number' Dte ordered, supplier, quantity,
product and cost and date received are also other field in the stock
book. I have no primary key for this table as the job number can be
entered more that once due to different components and suppliers.
There is aslo a 'Job Hours' table in which all hours worked by any number
of employees are stored and calculated. Again the 'Job number' can appear
more than once so no promary key is set and any job can have more than
one employee's hours accounted for.
The problem is that is i run a query based on the Job Number and then
generate a report from the query i get a seperate record for each stock
book entry and each employee hours entry, each one with the job book
details - giving at least 4 entries. I have tried the no duplicates on
each field in the report but still the duplicate data is displayed.
I would like just 1 entry form the job book which details the order and
then each entry from the stock book table for each procuct/supplier with
the cost of items bought for a peticular job. Then i would like the
employees hours to be shown and calculated. finnaly i would like a grand
total of all the supplier costs and the billing hours total.
I think maybe the problem is in the relationships between the tables but
i am not sure. Any help would be greatly appreciated.