If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Why is access filling in fields in query results
I'm querying a table that has payments recorded in [payments] and the next
payment due date recorded in [duedate]. Only the most recent payment row has a due date recorded, [duedate] is empty in the other payment rows. (there's probably a better way to do this but i'm new). Any query I run on these tables that includes the [duedate] field automatically fills all the result [duedate] fields with the most recent date entered for each customer. I don't want this and don't know why it happens. Suggestions? |
#2
|
|||
|
|||
On Tue, 13 Sep 2005 03:14:35 GMT, "Robbie via AccessMonster.com"
wrote: Any query I run on these tables that includes the [duedate] field automatically fills all the result [duedate] fields with the most recent date entered for each customer. I don't want this and don't know why it happens. That sounds very odd. Please open this query in SQL view and post the SQL text here. When you enter a new payment, do you update previous payments to "null out" the Duedate? Note that a due date should probably NOT EXIST in a payments table; if it can be calculated, do so. How do you determine the due date? John W. Vinson[MVP] |
#3
|
|||
|
|||
Thank you, John,
After posting I got frustrated and deleted all my queries to start over from scratch. I realized I don't understand joins and probably set up some weird conflict with that. I'm working on it and if I end up in the same place again I'll post the SQL here. But I was curious about your comment about not including the due date in the payments table and maybe that's why I can't get to where I want to be. I'll describe what I'm trying to do here and if you see this and have a suggestion that would be great. It's a musical instrument rental database - tables for instruments, customers, and transactions. The transactions (payments) table records each rental payment (linked to customer ID and instrument ID) and the due date for the next payment (not fixed so manually entered each time). When I enter a new payment and due date I manually clear the due date field from the previous transaction on that instrument. Maybe this is where I'm doing it wrong. Anyway - what I NEED to do is at the due date send out a reminder letter which not only reminds the customer to pay but ALSO calculates their total payments on that instrument so far minus a percentage plus sales tax in case they would like to purchase that instrument (rent w/ option to buy). I can calculate all the correct amounts, etc. but I can't get it to seperate out payments they may have made on a DIFFERENT instrument (like, if they used to rent one but now another, or if they have two). I realize it may be hard to answer this without knowing exactly what I've set up so far or without basically starting me from scratch but I was curious if my system for dealing with the due date was at the root of the problem. Thanks to all - great site - I just found it last night while deep in despair! Robbie John Vinson wrote: Any query I run on these tables that includes the [duedate] field automatically fills all the result [duedate] fields with the most recent date entered for each customer. I don't want this and don't know why it happens. That sounds very odd. Please open this query in SQL view and post the SQL text here. When you enter a new payment, do you update previous payments to "null out" the Duedate? Note that a due date should probably NOT EXIST in a payments table; if it can be calculated, do so. How do you determine the due date? John W. Vinson[MVP] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200509/1 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
WORD XP mail-merge FAILS using ACCESS query | SueMackay | Running & Setting Up Queries | 4 | November 29th, 2004 04:11 PM |
is Access 2003 any better than XP? | Gorb | General Discussion | 4 | November 11th, 2004 09:44 PM |
is Access 2003 any better than XP? | Gorb | Using Forms | 2 | November 11th, 2004 09:20 AM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |