query snag - help needed

[craig76]

I have genuinely tried but I am stuck, any guidance is greatly appreciated.

I am currently trying to create a database for a fee for storage company that specialises in caravans, campers trailers, boats, cars and shipping containers.
Locked undercover storage for an increased fee is also available.
The business for the most part runs on prompt pre-paid/in advance storage. some laggards exist.

I have set up three tables so far:

1 - asset details of what is stored - make model color length undercover etc
2 - customer details - phone, address, name, email etc
3 - payment/receipts - name, amount,date paid,date paid from, date paid to and receipt number

Details
Currently 80 clients that all have unique identfier(s) either cusromer_name or ag_number (agreement of terms etc)

Receipts table is the concern - 145 payment entries with : customer_name, ag_number, date_paid, payment_from, payment_to and receipt number. this table holds all transactions since earliest date on table.
Some customers pay monthly, some pay qtry, some bi-monthly or some other variation.
some customers have not paid at all since i took over.

my efforts so far querying the paid-to column bring back multiple entries of same customer.

first sql 2/9/18 - hunt
SELECT receipts.customer_name, receipts.amount, receipts.payment_from, receipts.payment_to, receipts.receipt_num
FROM receipts
WHERE (((receipts.customer_name)="hunt"));

not a date <>#28/04/2018#
SELECT receipts.customer_name, receipts.amount, receipts.payment_from, receipts.payment_to, receipts.receipt_num
FROM receipts
WHERE ((Not (receipts.payment_to)=#4/28/2018#));

Basically i am trying to create a report where i can show who has not paid.


regards Craig

 

[MarkK]

It doesn't look to me like your tables are sufficient to determine who has not paid, for instance, what table determines when payments should occur and how much they should be? I would expect to see a table for invoices, AND a table for payments. Then you can sum the amount owing on the invoices, and sum the amounts paid on the payments, and for any customer where the payments are less than the invoices, you have a someone who hasn't payed. But if you only have table of payments, I don't see how you determine what is MISSING from that table.
hth
Mark

 

[pbaldy]

FYI, I moved your thread out of the introductions forum.

 

[jdraw]

As has been suggested, your tables may not be cover your business requirements.
Here is a link to a data model (Customers/Invoices and Products), but replace the Product concept with Service and see if it covers the breadth of your business. The models at the site (Barry Williams) are meant as a starting point in that they represent the most typical entities and relationships. They may lack detail for your needs, or may be too detailed. They are meant to assist you in organizing the data found in many business structures. Add, drop or edit as suited to your set up.
Good luck with your project.

 

[craig76]

Thank you, jdraw, pbaldy and MarkK. Your feedback has provided fresh input.
Thanks again.