Query from two tables

[blogmk]

Hi,

I have 2 tables. In one table(Invoice table) have records of customer , the product they subscribed and the amount they paid and period they subscribed and the number of products they subscribed. The second table is---Each time a existing customer renews the subscription the previous purchace is moved to the second table(Archive table).

Now I need two queries

1. To calculate the number of products they subscribed in 2013

2.The amount they paid for 2013. If the subscription period is between Aug 2012 and Aug 2013. Then I need to claculate the amount only from jan 2013 till Aug 2013.

please help me get the answer.

Thanks

 

[namliam]

Should be relatively easy query to do, what have you got and what isnt working for you?

 

[plog]

-Each time a existing customer renews the subscription the previous purchace is moved to the second table(Archive table)

That's not how databases should work--especially on data you still need to use. Why are you moving data and not just adding a new record to the table for the next subscription?

For your issue, could you post sample data from your table (include field and table names) and then also post the data that should be returned based on that sample data. Use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name
Dave, 15, 7/1/2010
Sally, 66, 4/4/2008
Larry, 109, 3/31/2009

 

[blogmk]

Hi namliam

I used a joined query on the table. And my criteria was Between #01-01-2013# And #31-12-2013# for sub start date and or citeria is <=#01-01-2013# for sub start date and Between #01-01-2013# And #31-12-2013# Or >=#31-12-2013#for sub end date. But there are customers who subscribed for only 3 months say march til may in 2013 and then they renewed the subscription from jan 2014 till march 2014. So the Invoice table will not fetch any records for the above criteria. So the joined query doesnt fetch records from the archive table as there is no matching ID. Can you please help me with the query

 

[namliam]

I used a joined query on the table. And my criteria was Between #01-01-2013# And #31-12-2013# for sub start date and or citeria is <=#01-01-2013# for sub start date and Between #01-01-2013# And #31-12-2013# Or >=#31-12-2013#for sub end date.

If you have the >= for sub end and <= for sub start on the same line that is causing your problem.....
also you are overcomplicating your query.... basicaly you are asking

sub start date <= #31-12-2013# and Sub end date >= #01-01-2013#

Try that and see if it works

 

[namliam]

Question, what needs to happen if people didnt cancel their subs yet?
Assuming you want to see them and assuming the Sub end date is then empty you want to do something like:

sub start date <= #31-12-2013# and nz(Sub end date,date()) >= #01-01-2013#

or alternatively this:
sub start date <= #31-12-2013# and ( Sub end date >= #01-01-2013# or Sub end date is null)

 

[blogmk]

Hi Plog,

I moved the record to a new table as the customer would like to see only the active existing customers and there could be many renewal within a year .

Table Name:Invoice
ID Name Address Add2 Add3 City Country Phone Email Category SA SubType Substartdate Subenddate Copies_perweek Amounttopay Invoicesentdate Invoiceduedate Paid Start Delivery Stop Delivery
1000 Dior Add Add1 605 DK 25254543 email.com Company N/A Pay 04-jun-13 09-jul-14 1 1200,00 02-okt-13 23-okt-13 07-okt-13
1001 Lib Add Add1 607 DK email.com Embassy N/A Pay 01-mar-13 01-mar-14 1 1200,00 28-maj-13 18-jun-13 10-jun-13
1002 Clay Add Add1 608 DK email.com Person N/A Pay 23-apr-13 23-apr-14 1 1200,00 03-jun-13 24-jun-13 31-jul-13
1003 Jakob Add Add1 609 DK 3214132 email.com Person N/A Pay 24-apr-13 24-apr-14 1 1200,00 03-jun-13 24-jun-13 25-jun-13

Table Name: ARCHIVE
Archive ID CusID Name Address Add2 Add3 City Country Phone Email Category SA SubType Substartdate Subenddate Copiesperweek Amounttopay Invoicesentdate Invoiceduedate Paid Start Delivery Stop Delivery
12 1000 Dior Add Add1 605 DK 25254543 email.com Company N/A Pay 04-jun-12 09-jul-13 1 1200,00 02-okt-12 23-okt-12 07-nov-12
13 1001 Lib Add Add1 607 DK email.com Embassy N/A Pay 01-mar-12 01-mar-13 1 1200,00 28-maj-12 18-jun-12 10-aug-12
14 1002 Clay Add Add1 608 DK email.com Person N/A Pay 23-apr-12 23-apr-13 1 1200,00 03-jun-12 24-jun-12 01-sep-12
15 1003 Jakob Add Add1 609 DK 3214132 email.com Person N/A Pay 24-apr-12 24-apr-13 1 1200,00 03-jun-12 24-jun-12

The output for Amount in 2013
ArchiveID CusID Name substartdate enddateAmt Paid Amt in 2013
12 1000 Dior 04-jun-12 09-jul-13 1200,00 07-nov-12 600,00
13 1001 Lib 01-mar-12 01-mar-13 1200,00 10-aug-12 200,00
14 1002 Clay 23-apr-12 23-apr-13 1200,00 01-sep-12 400,00
15 1003 Jakob 24-apr-12 24-apr-13 1200,00 0,00
1000 Dior 04-06-2013 09-07-2014 1200,00 07-okt-13 700,00
1001 Lib 01-03-2013 01-03-2014 1200,00 10-jun-13 1000,00
1002 Clay 23-04-2013 23-04-2014 1200,00 31-jul-13 800,00
1003 Jakob 24-apr-13 24-apr-14 1200,00 25-jun-13 800,00
4500,00

 

[blogmk]

Sorry, the table is quite big and hope it makes sense.

Thanks for the help

 

[blogmk]

Hi namliam,

SELECT ArchiveCustomerDetails.Archive_ID, Invoice.Customer_ID, Invoice.Customer_Name, Invoice.Subscription_Period, Invoice.Subscription_startdate, Invoice.Subscription_enddate, Invoice.Copies_perweek, ArchiveCustomerDetails.Subscription_startdate, ArchiveCustomerDetails.Subscription_enddate, ArchiveCustomerDetails.Subscription_Period, ArchiveCustomerDetails.Copies_perweek, ArchiveCustomerDetails.Customer_Name
FROM ArchiveCustomerDetails RIGHT JOIN Invoice ON ArchiveCustomerDetails.Customer_ID = Invoice.Customer_ID
WHERE (((Invoice.Subscription_startdate) Between #1/1/2013# And #12/31/2013#)) OR (((Invoice.Subscription_startdate)<=#1/1/2013#) AND ((Invoice.Subscription_enddate) Between #1/1/2013# And #12/31/2013# Or (Invoice.Subscription_enddate)>=#12/31/2013#));

The criteria works fine for me. But the problem is, for eg: Invoice table has subscription from 9-Jan -2014 till 9-March-2014. The Archive table has subscription from 3-March-2013 till 3-Aug -2013. I use join on CUS Id. In the above case there is record in the invoice table for 2013.But Archive table has. It does retrieve the Archive table record as there is no matching CUSID in Invoice table. How to solve this. And also even if there are matching CUSID, it retrieves record from Invoice table and just appends Archive ID to the record instead of showing 2 records. Is it possible to show record from Archive table and Invoice table even if they have matching CUSID

 

[blogmk]

Hi namliam,

The current record is like this

Archive_ID ArchiveCustomerDetails.Customer_ID Invoice.Customer_ID Invoice.CustomerName Invoice.SubPeriod Invoice.Substartdate Invoice.Subenddate Invoice.Copiesperweek ArchiveCustomerDetails.Substartdate ArchiveCustomerDetails.Subenddate ArchiveCustomerDetails.SubPeriod ArchiveCustomerDetails.Copiesperweek ArchiveCustomerDetails.CusName
182 2943 2943 Ida 6 12-nov-13 12-maj-14 1 12-maj-13 12-nov-13 6 1 Ida
181 2944 2944 Kevin 6 12-nov-13 12-maj-14 1 12-maj-13 12-nov-13 6 1 Kevin

But I would like to get the following
2943 Ida 6 12-11-2013 12-05-2014 1 750 05-11-2013 26-11-2013
2944 Kevin 6 12-11-2013 12-05-2014 1 750 05-11-2013 26-11-2013 12-11-2013
182 2943 Ida 6 12-05-2013 12-11-2013 1 750 10-06-2013 01-07-2013 25-07-2013
181 2944 Kevin 612-05-2013 12-11-2013 1 750 10-06-2013 01-07-2013 18-06-2013

 

[namliam]

Please post tables in code tags or stick them in a text or excel sheet and attach that, I get headaches trying to read this crappy tables and please format your SQL, More headaches

Your READABLE where clause

WHERE 
(
 (
  (Invoice.Subscription_startdate) Between #1/1/2013# And #12/31/2013#
 )
) 
OR 
(
    (
     (Invoice.Subscription_startdate)<=#1/1/2013#
    ) 
 AND 
    (
        (Invoice.Subscription_enddate) Between #1/1/2013# And #12/31/2013# 
     Or (Invoice.Subscription_enddate)>=#12/31/2013#
    )
);

This where clause will select anyone that:
1) Started their subscription in 2013, either still going on cancelled at any point
2) Subscription started in 2012 or earlier and ended in 2013 or later
It will not select anyone that started in 2012 or earlier and has not cancelled their subs, but reading your requirement now, that doesnt seem to matter.

However most your select columns and ALL of your Where clause columns are from your invoice table, where you are talking about the requirements being on the Archive table.. I believe... is that your problem?

 

[plog]

I moved the record to a new table as the customer would like to see only the active existing customers and there could be many renewal within a year .

By that logic, you should have yet another table structure for this issue: People want to see how much they paid in 2013, so your table structure should accomodate that. And another table structure for how many products each person had. And then so and so forth for every output they want.

Output has little bearing on proper table structure (it let's you know what fields to use, but not what structure they should be in). The fields and relationships among data dictate structure . Databases are built from the ground up.

All your data should be in the same table, then like this issue, you run a query to see active/existing customers--you don't build a table to tell you that.

 

[blogmk]

Hi Plog,

If I have all the records in one table.

Each time I renew the subscriptions, then the primary key will be duplicated. How do I handle this?

How do I show only the current records in the form?

Please help

 

[plog]

First, when I said 'All your data should be in the same table' I meant you shouldn't have 2 tables with the same structure but different data (regular and archive). After seeing what data you posted, you definitely need more than one table, but not like you have it set up.

You need to read up on normalization (google that term). From the field names you have posted and your description of your data, I believe you need at least 3 tables: one for customers, one for invoices and one for subscriptions. None of those tables will have the same structure.

Customers would have everything about the customer--ID, name, address, etc. The way you have it now, its duplicated in your Invoice table. That's not correct. All the invoice table should have in it for customer data is the Customer ID so you can link to your customers table.

Subscriptions would have all the data about all of their subscriptions: start date, end date, copies per week, etc. Then, since you have a start data in that table you can use queries to get the most recent subscription for each customer.