Combine Info from Two Tables

[Jeremy]

Hi

I have created a database for my work using Access 2k2. I have around 18 tables feeding three main tables. The 18 tables store data for combo boxes to ensure the users are forced to enter the correct data to the three main tables

Here is my question: I am attempting to calculate Defect PPM by combining my 'concernlog' table and 'vendorshipments' table. The main values needed to complete this calculation are

Concernlog: [date], [defect_qty], [part_number], [supplier_id] (part number and supplier are combo boxes from other tables
Vendortable: [date], [ship_qty], [part_number], [supplier_id] (part number and supplier are combo boxes from other tables

I have used the standard query and report wizards and what I find is Access does combine the data, but it repeats the data from the concernlog until the qty from vendorshipments changes. Here is an example

supplier_id part_number date defect_qty ship_qt
Vendor#1 11111 01/12/04 2 130
Vendor#1 11111 01/13/04 2 100
Vendor#1 11111 01/30/04 2 120
Vendor#1 11111 01/31/04 2 110

With 50 entries for Vendor#1 shipping P/N 1111, it continues to show 2 defects per shipment even though there is only one instance of these 2 defects for a 12 month period. Once the ship_qty data is exhausted, then is starts the process over again but now the defect_qty is the changing value. It took my total of 184 defect reports and 1000 shipments and returned over 5000 total shipments and defect records. I have placed the key in the query for diagnostic purposes and found the key for either the defect or ship qty repeats when the qty repeats

What I need the report to do is show the total defect and shipments per supplier per part number monthly for no more then 12 months. Does anyone have any ideas

Thanks in advanced

Jeremy

 

[Kelvin]

Include [date] in the join. Set all joins to show all data from VendorTable
and only matching from Concernlog.

Kelvin

Hi,

I have created a database for my work using Access 2k2. I have around 18

tables feeding three main tables. The 18 tables store data for combo boxes
to ensure the users are forced to enter the correct data to the three main
tables.

Here is my question: I am attempting to calculate Defect PPM by combining

my 'concernlog' table and 'vendorshipments' table. The main values needed
to complete this calculation are:

Concernlog: [date], [defect_qty], [part_number], [supplier_id] (part

number and supplier are combo boxes from other tables)

Vendortable: [date], [ship_qty], [part_number], [supplier_id] (part

number and supplier are combo boxes from other tables)

I have used the standard query and report wizards and what I find is

Access does combine the data, but it repeats the data from the concernlog
until the qty from vendorshipments changes. Here is an example:

supplier_id part_number date defect_qty ship_qty
Vendor#1 11111 01/12/04 2 1300
Vendor#1 11111 01/13/04 2 1000
Vendor#1 11111 01/30/04 2 1200
Vendor#1 11111 01/31/04 2 1100

With 50 entries for Vendor#1 shipping P/N 1111, it continues to show 2

defects per shipment even though there is only one instance of these 2
defects for a 12 month period. Once the ship_qty data is exhausted, then is
starts the process over again but now the defect_qty is the changing value.
It took my total of 184 defect reports and 1000 shipments and returned over
5000 total shipments and defect records. I have placed the key in the query
for diagnostic purposes and found the key for either the defect or ship qty
repeats when the qty repeats.

What I need the report to do is show the total defect and shipments per

supplier per part number monthly for no more then 12 months. Does anyone
have any ideas?

 

[Jeremy]

I joined the date field but, since the 'day' number in the month/day/year value is random, there is only one record pulled in the query where date from both tables match. Is there a way to force Access to look at only the month and year? I have tried changing the format mask, but Access still looks at the root value, not the mask. The 'day' value is required for other reports I create.

Also, I get an error when changing the joins as stated below. Most of my joins are simply to control the user input. When I create outer join between [date] and [date], Access states it's ambiguous and I need to force a join first. The only other joins I have are indirect between concernlog and vendorshipments (i.e. they both pull data from different tables for [part_number] and [supplier_id]).

Any thoughts would be appreciated.

Jeremy


----- Kelvin wrote: -----

Include [date] in the join. Set all joins to show all data from VendorTable
and only matching from Concernlog.

Kelvin

Hi,

tables feeding three main tables. The 18 tables store data for combo boxes
to ensure the users are forced to enter the correct data to the three main
tables.my 'concernlog' table and 'vendorshipments' table. The main values needed
to complete this calculation are:

Concernlog: [date], [defect_qty], [part_number], [supplier_id] (part

number and supplier are combo boxes from other tables)

Vendortable: [date], [ship_qty], [part_number], [supplier_id] (part

number and supplier are combo boxes from other tables)Access does combine the data, but it repeats the data from the concernlog
until the qty from vendorshipments changes. Here is an example:

Vendor#1 11111 01/12/04 2 1300
Vendor#1 11111 01/13/04 2 1000
Vendor#1 11111 01/30/04 2 1200
Vendor#1 11111 01/31/04 2 1100

defects per shipment even though there is only one instance of these 2
defects for a 12 month period. Once the ship_qty data is exhausted, then is
starts the process over again but now the defect_qty is the changing value.
It took my total of 184 defect reports and 1000 shipments and returned over
5000 total shipments and defect records. I have placed the key in the query
for diagnostic purposes and found the key for either the defect or ship qty
repeats when the qty repeats.supplier per part number monthly for no more then 12 months. Does anyone
have any ideas?

 

[Kelvin]

I thought you were trying to match specific days. If you only need monthly
totals. Try the follow. Watch the line wraps.

SELECT vendortable.supplier_id, vendortable.part_number,
Sum(vendortable.ship_qty) AS SumOfship_qty, Sum(concernlog.defect_qty) AS
SumOfdefect_qty, month(vendortable.date) as dMonth, year(vendortable.date)
as dYear
FROM vendortable LEFT JOIN concernlog ON (month(vendortable.date) =
month(concernlog.date)) AND (year(vendortable.date) = year(concernlog.date))
AND (vendortable.supplier_id = concernlog.supplier_id) AND
(vendortable.part_number = concernlog.part_number)
GROUP BY vendortable.supplier_id, vendortable.part_number,
month(vendortable.date), year(vendortable.date);

Kelvin

I joined the date field but, since the 'day' number in the month/day/year

value is random, there is only one record pulled in the query where date
from both tables match. Is there a way to force Access to look at only the
month and year? I have tried changing the format mask, but Access still
looks at the root value, not the mask. The 'day' value is required for
other reports I create.

Also, I get an error when changing the joins as stated below. Most of my

joins are simply to control the user input. When I create outer join
between [date] and [date], Access states it's ambiguous and I need to force
a join first. The only other joins I have are indirect between concernlog
and vendorshipments (i.e. they both pull data from different tables for
[part_number] and [supplier_id]).

Any thoughts would be appreciated.

Jeremy


----- Kelvin wrote: -----

Include [date] in the join. Set all joins to show all data from VendorTable
and only matching from Concernlog.

Kelvin

Hi,

around 18
tables feeding three main tables. The 18 tables store data for combo boxes
to ensure the users are forced to enter the correct data to the three main

tables.

combining
my 'concernlog' table and 'vendorshipments' table. The main values needed
to complete this calculation are:

Concernlog: [date], [defect_qty], [part_number], [supplier_id]

(part
number and supplier are combo boxes from other tables)

Vendortable: [date], [ship_qty], [part_number], [supplier_id]

(part
number and supplier are combo boxes from other tables) is
Access does combine the data, but it repeats the data from the concernlog
until the qty from vendorshipments changes. Here is an example:

ship_qty
Vendor#1 11111 01/12/04 2 1300
Vendor#1 11111 01/13/04 2 1000
Vendor#1 11111 01/30/04 2 1200
Vendor#1 11111 01/31/04 2 1100

show 2
defects per shipment even though there is only one instance of these 2
defects for a 12 month period. Once the ship_qty data is exhausted, then is
starts the process over again but now the defect_qty is the changing value.
It took my total of 184 defect reports and 1000 shipments and returned over
5000 total shipments and defect records. I have placed the key in the query
for diagnostic purposes and found the key for either the defect or ship qty
repeats when the qty repeats. shipments per
supplier per part number monthly for no more then 12 months. Does anyone
have any ideas?

 

[Jeremy]

Kelvin

Thanks for the reply

I have completed what you said and I filled in some blanks. I made a select query and pasted in the following

SELECT [Vendor Shipments].VendorID, [Vendor Shipments].[Part Number], Sum([Vendor Shipments].Ship_Qty) AS SumOfShip_Qty, Sum([Concern Log].[AI Qty to Supplier]) AS SumofPPM_Qty, month([Vendor Shipments].Date) as dMonth, year([Vendor Shipments].Date) as dYea
FROM [Vendor Shipments] LEFT JOIN concernlog ON (month([Vendor Shipments].Date) =month([Concern Log].Date)) AND (year([Vendor Shipments].Date) = year([Concern Log].Date)) AND ([Vendor Shipments].VendorID = [Concern Log].Supplier) AND ([Vendor Shipments].[Part Number] = [Concern Log].[Part Number]
GROUP BY [Vendor Shipments].VendorID, [Vendor Shipments].[Part Number],month([Vendor Shipments].Date), year([Vendor Shipments].Date)

I recieve a syntax error on join operation at the first instance of [Concern Log]. If I underscore between the concern and log (Concern_Log), then I recieve a syntax error at [AI Qty to Supplier]. If I underscore that name, it continues with the syntax error

Any information would be great!
Thank

----- Kelvin wrote: ----

I thought you were trying to match specific days. If you only need monthl
totals. Try the follow. Watch the line wraps

SELECT vendortable.supplier_id, vendortable.part_number
Sum(vendortable.ship_qty) AS SumOfship_qty, Sum(concernlog.defect_qty) A
SumOfdefect_qty, month(vendortable.date) as dMonth, year(vendortable.date
as dYea
FROM vendortable LEFT JOIN concernlog ON (month(vendortable.date)
month(concernlog.date)) AND (year(vendortable.date) = year(concernlog.date)
AND (vendortable.supplier_id = concernlog.supplier_id) AN
(vendortable.part_number = concernlog.part_number
GROUP BY vendortable.supplier_id, vendortable.part_number
month(vendortable.date), year(vendortable.date)

Kelvi

I joined the date field but, since the 'day' number in the month/day/yea

value is random, there is only one record pulled in the query where dat
from both tables match. Is there a way to force Access to look at only th
month and year? I have tried changing the format mask, but Access stil
looks at the root value, not the mask. The 'day' value is required fo
other reports I createjoins are simply to control the user input. When I create outer joi
between [date] and [date], Access states it's ambiguous and I need to forc
a join first. The only other joins I have are indirect between concernlo
and vendorshipments (i.e. they both pull data from different tables fo
[part_number] and [supplier_id])

Any thoughts would be appreciated
Jerem

----- Kelvin wrote: ----

Include [date] in the join. Set all joins to show all data fro

VendorTabl
and only matching from Concernlog

Kelvi

around 1
tables feeding three main tables. The 18 tables store data for comb boxe
to ensure the users are forced to enter the correct data to the thre mai

tables

combinin
my 'concernlog' table and 'vendorshipments' table. The main value neede
to complete this calculation are

Concernlog: [date], [defect_qty], [part_number], [supplier_id

(par
number and supplier are combo boxes from other tables

Vendortable: [date], [ship_qty], [part_number], [supplier_id

(part
number and supplier are combo boxes from other tables) is
Access does combine the data, but it repeats the data from the concernlog
until the qty from vendorshipments changes. Here is an example:

ship_qty
Vendor#1 11111 01/12/04 2 1300
Vendor#1 11111 01/13/04 2 1000
Vendor#1 11111 01/30/04 2 1200
Vendor#1 11111 01/31/04 2 1100

show 2
defects per shipment even though there is only one instance of these 2
defects for a 12 month period. Once the ship_qty data is exhausted, then is
starts the process over again but now the defect_qty is the changing value.
It took my total of 184 defect reports and 1000 shipments and returned over
5000 total shipments and defect records. I have placed the key in the query
for diagnostic purposes and found the key for either the defect or ship qty
repeats when the qty repeats. shipments per
supplier per part number monthly for no more then 12 months. Does anyone
have any ideas?