Consolidating Records

[Gregc.]

G'day

I have two tables with transactional data. Table 1 has a Cost Centre,
Account, Fund Code and a YTD0-6 figure for each Account Number. Table 2
has Cost Centre, Account, Fund Code, Period 7, Period 8, Period 9,
Period 10, Period 11, Period 12 data. Both Tables are linked to 'Data
Tables' , Account Information, Cost Centres Information and Fund Type
Information.

What I want is to consoldiate the transactional data into one table so
that it would look something like this:
YTD0-6 Period7 Period8
etc
Account Cost Centre Fund type

I have designed a query to consolidate the data, but for some reason
the query duplicates the records. Is there some way around this.

Thanks for your assistance.

Greg

 

[[MVP] S.Clark]

Post your SQL. My guess is that you've made a cross join.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Gregc.]

Post your SQL. My guess is that you've made a cross join.

SELECT Customers.CustID, Customers.Title, Customers.FamilyName,
Customers.GivenName, Customers.Address, Customers.City,
Customers.State, Customers.Postcode, Customers.PhoneNumber,
Orders.ItemID, [Updated Items with Price Increase].Type, [Updated Items
with Price Increase].Flavour, [Updated Items with Price
Increase].PackType, Orders.OrderDate, Orders.OrderQty, ([Updated Items
with Price Increase]![CostPrice]*1.35)*[Orders]![OrderQty] AS [Order
Total]
FROM Customers INNER JOIN (Orders INNER JOIN [Updated Items with Price
Increase] ON Orders.ItemID = [Updated Items with Price
Increase].ItemID) ON Customers.CustID = Orders.CustID
WHERE (((Customers.City)<>"Wyong") AND ((Customers.State)="NSW"));


Greg

 

[[MVP] S.Clark]

Ok, still guessing. The order detail line items are included in the query,
thus the order header info is repeated, which includes the customer name,
which appears to you as a duplication.

If not that, then Prof. Plum in the library with the Candlestick.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Post your SQL. My guess is that you've made a cross join.

SELECT Customers.CustID, Customers.Title, Customers.FamilyName,
Customers.GivenName, Customers.Address, Customers.City,
Customers.State, Customers.Postcode, Customers.PhoneNumber,
Orders.ItemID, [Updated Items with Price Increase].Type, [Updated Items
with Price Increase].Flavour, [Updated Items with Price
Increase].PackType, Orders.OrderDate, Orders.OrderQty, ([Updated Items
with Price Increase]![CostPrice]*1.35)*[Orders]![OrderQty] AS [Order
Total]
FROM Customers INNER JOIN (Orders INNER JOIN [Updated Items with Price
Increase] ON Orders.ItemID = [Updated Items with Price
Increase].ItemID) ON Customers.CustID = Orders.CustID
WHERE (((Customers.City)<>"Wyong") AND ((Customers.State)="NSW"));


Greg