Query help please.

[dmunday]

I have two different tables, one with turnover values by customer by month,
and one with some cost values by customer by month. I currenlty have two
queireis that takes these values into separate reports, but I would like to
know how to combine these values into one report, as I cannot seem to get a
query to combine the two tables.

Table 1.
Contains Customer Id, Product Id, Sales Amount, Rebate Value, Profit,
Analysis Period.

Table 2.
Contains Customer Id, Shipment, Demvalue, Analysis Period.

I would like to have a query that will let me have Customer Id and Totals of
Sales Amount, Rebate Value, Profit and Demvalue, by an input Analysis Period.

Thanks in advance.

 

[Allen Browne]

So you want to match the 2 tables on the [Customer Id] and the [Analysis
Period]?

1. Create a query using the 2 tables.

2. If you don't already see the lines from one table to the other in the
upper pane of query design, drag:
- Table1.[Customer Id] onto Table2.[Customer Id]
- Table1.[Analysis Period] onto Table2.[Analysis Period]

You can now output the fields you want.

 

[dmunday]

Allen,

Thanks for your response. I have already tried this which allows me to get
all of the results, but I cannot get each tables records queried as subtotals
by analysis period. The other confusing issue is that there will not
necessarily always be a record in table 2 (demvalue) for every analysis
period.

Ideally the query will give me totals by customer by period for table 1
(sales amount, rebate value, profit) and also total by customer by period for
table 2 (demvalue). These totals will then be hopefully used to generate a
profit refort for any given period.

Thanks

Derek

So you want to match the 2 tables on the [Customer Id] and the [Analysis
Period]?

1. Create a query using the 2 tables.

2. If you don't already see the lines from one table to the other in the
upper pane of query design, drag:
- Table1.[Customer Id] onto Table2.[Customer Id]
- Table1.[Analysis Period] onto Table2.[Analysis Period]

You can now output the fields you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have two different tables, one with turnover values by customer by month,
and one with some cost values by customer by month. I currenlty have two
queireis that takes these values into separate reports, but I would like
to
know how to combine these values into one report, as I cannot seem to get
a
query to combine the two tables.

Table 1.
Contains Customer Id, Product Id, Sales Amount, Rebate Value, Profit,
Analysis Period.

Table 2.
Contains Customer Id, Shipment, Demvalue, Analysis Period.

I would like to have a query that will let me have Customer Id and Totals
of
Sales Amount, Rebate Value, Profit and Demvalue, by an input Analysis
Period.

Thanks in advance.

 

[Allen Browne]

As we can't see what's going on with your data, I don't know why you are not
getting the desired totals.

You can get all records from Table1 (even if there is no match in Table2) if
you use an outer join. Details in:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

You may need to depress the Total button on the toolbar to get totals per
period.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response. I have already tried this which allows me to get
all of the results, but I cannot get each tables records queried as
subtotals
by analysis period. The other confusing issue is that there will not
necessarily always be a record in table 2 (demvalue) for every analysis
period.

Ideally the query will give me totals by customer by period for table 1
(sales amount, rebate value, profit) and also total by customer by period
for
table 2 (demvalue). These totals will then be hopefully used to generate a
profit refort for any given period.

Thanks

Derek

So you want to match the 2 tables on the [Customer Id] and the [Analysis
Period]?

1. Create a query using the 2 tables.

2. If you don't already see the lines from one table to the other in the
upper pane of query design, drag:
- Table1.[Customer Id] onto Table2.[Customer Id]
- Table1.[Analysis Period] onto Table2.[Analysis Period]

You can now output the fields you want.

I have two different tables, one with turnover values by customer by
month,
and one with some cost values by customer by month. I currenlty have
two
queireis that takes these values into separate reports, but I would
like
to
know how to combine these values into one report, as I cannot seem to
get
a
query to combine the two tables.

Table 1.
Contains Customer Id, Product Id, Sales Amount, Rebate Value, Profit,
Analysis Period.

Table 2.
Contains Customer Id, Shipment, Demvalue, Analysis Period.

I would like to have a query that will let me have Customer Id and
Totals
of
Sales Amount, Rebate Value, Profit and Demvalue, by an input Analysis
Period.