have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
[fee] they sometimes do not have same dates. I can get info from one or the 
other tried to join date fields and do outer join but cannot get info if 
there is not data for both only shows for either invoice or credit card

Thanks for your help

At what point in time are the two records in the two different tables created?
I can see where the dates may be different because often credit cards or 
debit cards clearing date may be some time after the transaction date.
Is it possible you could add the invoice number to the credit card table or 
the credit card number to both tables?

To be able to join the two, you need some common field or fields.  The only 
thing that would appear to be in common would be customer and amount, but I 
don't know if those will always be a match.  If they are, you could join on a 
combination of those two fields.
-- 
Dave Hargis, Microsoft Access MVP


"the bp Guy" wrote:

> have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> [fee] they sometimes do not have same dates. I can get info from one or the 
> other tried to join date fields and do outer join but cannot get info if 
> there is not data for both only shows for either invoice or credit card
> 
> Thanks for your help

we are a wholesale company selling fuel and on a daily basis we track the 
credit cards that customer uses [tblcreditcard] when customer buys fuel 
[tblinvoice] they want to deduct creditcards collected from invoice. want to 
make report to show invoice for fuel purchased minus credit cards in the time 
frame 

thanks again

"Klatuu" wrote:

> At what point in time are the two records in the two different tables created?
> I can see where the dates may be different because often credit cards or 
> debit cards clearing date may be some time after the transaction date.
> Is it possible you could add the invoice number to the credit card table or 
> the credit card number to both tables?
> 
> To be able to join the two, you need some common field or fields.  The only 
> thing that would appear to be in common would be customer and amount, but I 
> don't know if those will always be a match.  If they are, you could join on a 
> combination of those two fields.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "the bp Guy" wrote:
> 
> > have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> > tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> > setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> > [fee] they sometimes do not have same dates. I can get info from one or the 
> > other tried to join date fields and do outer join but cannot get info if 
> > there is not data for both only shows for either invoice or credit card
> > 
> > Thanks for your help

I am surprised your tblinvoice does not have a payment method field or child 
table that allows you to track how the invoice was paid and for checks a 
check number and R&T for the bank and a card number and type for card 
purchases.

If what you have posted it all the fields in both tables, I see no reliable 
way to join the two so that all data are captured correctly.
-- 
Dave Hargis, Microsoft Access MVP


"the bp Guy" wrote:

> we are a wholesale company selling fuel and on a daily basis we track the 
> credit cards that customer uses [tblcreditcard] when customer buys fuel 
> [tblinvoice] they want to deduct creditcards collected from invoice. want to 
> make report to show invoice for fuel purchased minus credit cards in the time 
> frame 
> 
> thanks again
> 
> "Klatuu" wrote:
> 
> > At what point in time are the two records in the two different tables created?
> > I can see where the dates may be different because often credit cards or 
> > debit cards clearing date may be some time after the transaction date.
> > Is it possible you could add the invoice number to the credit card table or 
> > the credit card number to both tables?
> > 
> > To be able to join the two, you need some common field or fields.  The only 
> > thing that would appear to be in common would be customer and amount, but I 
> > don't know if those will always be a match.  If they are, you could join on a 
> > combination of those two fields.
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "the bp Guy" wrote:
> > 
> > > have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> > > tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> > > setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> > > [fee] they sometimes do not have same dates. I can get info from one or the 
> > > other tried to join date fields and do outer join but cannot get info if 
> > > there is not data for both only shows for either invoice or credit card
> > > 
> > > Thanks for your help

this is a check and balance to show the credit cards that we have been paid 
for gets reimbursed to customer. what i want to see on the report is total 
amt of invoice for fuel purchased ($25,000 on 9/1/08) and list credit cards 
to be credited ( 08/23/08 to 08/28/08) which might be ($19,000) and that 
difference we would eft from customer

Thanks for your help

"Klatuu" wrote:

> I am surprised your tblinvoice does not have a payment method field or child 
> table that allows you to track how the invoice was paid and for checks a 
> check number and R&T for the bank and a card number and type for card 
> purchases.
> 
> If what you have posted it all the fields in both tables, I see no reliable 
> way to join the two so that all data are captured correctly.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "the bp Guy" wrote:
> 
> > we are a wholesale company selling fuel and on a daily basis we track the 
> > credit cards that customer uses [tblcreditcard] when customer buys fuel 
> > [tblinvoice] they want to deduct creditcards collected from invoice. want to 
> > make report to show invoice for fuel purchased minus credit cards in the time 
> > frame 
> > 
> > thanks again
> > 
> > "Klatuu" wrote:
> > 
> > > At what point in time are the two records in the two different tables created?
> > > I can see where the dates may be different because often credit cards or 
> > > debit cards clearing date may be some time after the transaction date.
> > > Is it possible you could add the invoice number to the credit card table or 
> > > the credit card number to both tables?
> > > 
> > > To be able to join the two, you need some common field or fields.  The only 
> > > thing that would appear to be in common would be customer and amount, but I 
> > > don't know if those will always be a match.  If they are, you could join on a 
> > > combination of those two fields.
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "the bp Guy" wrote:
> > > 
> > > > have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> > > > tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> > > > setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> > > > [fee] they sometimes do not have same dates. I can get info from one or the 
> > > > other tried to join date fields and do outer join but cannot get info if 
> > > > there is not data for both only shows for either invoice or credit card
> > > > 
> > > > Thanks for your help

It seems strange to me that the credit card dates would be earlier than the 
purchase dates, but let's see how this works.  It is totally untested and may 
take some fixing, but I think it is headed in the right direction:

SELECT tblinvoice.[date], tblinvoice.[Customer], tblinvoice.[Invoice#], 
tblinvoice.[amount], tblcreditcard.[gross], tblcreditcard.[fee]
FROM tblInvoice
LEFT JOIN tblcreditcard ON tblinvoice.[Customer] = tblcreditcard.[customer]
WHERE tblinvoice.[date] = #9/1/2008# AND tblcreditcard.[date] BETWEEN 
#8/23/2008# AND #8/28/2008#;

-- 
Dave Hargis, Microsoft Access MVP


"the bp Guy" wrote:

> this is a check and balance to show the credit cards that we have been paid 
> for gets reimbursed to customer. what i want to see on the report is total 
> amt of invoice for fuel purchased ($25,000 on 9/1/08) and list credit cards 
> to be credited ( 08/23/08 to 08/28/08) which might be ($19,000) and that 
> difference we would eft from customer
> 
> Thanks for your help
> 
> "Klatuu" wrote:
> 
> > I am surprised your tblinvoice does not have a payment method field or child 
> > table that allows you to track how the invoice was paid and for checks a 
> > check number and R&T for the bank and a card number and type for card 
> > purchases.
> > 
> > If what you have posted it all the fields in both tables, I see no reliable 
> > way to join the two so that all data are captured correctly.
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "the bp Guy" wrote:
> > 
> > > we are a wholesale company selling fuel and on a daily basis we track the 
> > > credit cards that customer uses [tblcreditcard] when customer buys fuel 
> > > [tblinvoice] they want to deduct creditcards collected from invoice. want to 
> > > make report to show invoice for fuel purchased minus credit cards in the time 
> > > frame 
> > > 
> > > thanks again
> > > 
> > > "Klatuu" wrote:
> > > 
> > > > At what point in time are the two records in the two different tables created?
> > > > I can see where the dates may be different because often credit cards or 
> > > > debit cards clearing date may be some time after the transaction date.
> > > > Is it possible you could add the invoice number to the credit card table or 
> > > > the credit card number to both tables?
> > > > 
> > > > To be able to join the two, you need some common field or fields.  The only 
> > > > thing that would appear to be in common would be customer and amount, but I 
> > > > don't know if those will always be a match.  If they are, you could join on a 
> > > > combination of those two fields.
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "the bp Guy" wrote:
> > > > 
> > > > > have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> > > > > tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> > > > > setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> > > > > [fee] they sometimes do not have same dates. I can get info from one or the 
> > > > > other tried to join date fields and do outer join but cannot get info if 
> > > > > there is not data for both only shows for either invoice or credit card
> > > > > 
> > > > > Thanks for your help

tblInvoice.DateCustomer Invoice#Amount	Gross     Fee        tblcreditcard.Date	
9/1/2008	Stuart	 55550	 50000	$1,500.36 $78.25   8/23/2008
9/1/2008	Stuart	 55550	 50000	$1.00	  $1.00   8/24/2008
9/1/2008	Stuart	 55550	 50000	$1,800.36 $128.36  8/25/2008
9/1/2008	Stuart	 55550	 50000	$1,000.00 $95.00    8/26/2008
9/1/2008	Stuart	 55550	 50000	$1,500.00 $100.00  8/27/2008

We are on the right track but as you can see the invoice shows up for every 
line that there is credit card data. the credit cards accumulate until the 
fuel is invoiced and subtracted of of the invoice

Thanks
"Klatuu" wrote:

> It seems strange to me that the credit card dates would be earlier than the 
> purchase dates, but let's see how this works.  It is totally untested and may 
> take some fixing, but I think it is headed in the right direction:
> 
> SELECT tblinvoice.[date], tblinvoice.[Customer], tblinvoice.[Invoice#], 
> tblinvoice.[amount], tblcreditcard.[gross], tblcreditcard.[fee]
> FROM tblInvoice
> LEFT JOIN tblcreditcard ON tblinvoice.[Customer] = tblcreditcard.[customer]
> WHERE tblinvoice.[date] = #9/1/2008# AND tblcreditcard.[date] BETWEEN 
> #8/23/2008# AND #8/28/2008#;
> 
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "the bp Guy" wrote:
> 
> > this is a check and balance to show the credit cards that we have been paid 
> > for gets reimbursed to customer. what i want to see on the report is total 
> > amt of invoice for fuel purchased ($25,000 on 9/1/08) and list credit cards 
> > to be credited ( 08/23/08 to 08/28/08) which might be ($19,000) and that 
> > difference we would eft from customer
> > 
> > Thanks for your help
> > 
> > "Klatuu" wrote:
> > 
> > > I am surprised your tblinvoice does not have a payment method field or child 
> > > table that allows you to track how the invoice was paid and for checks a 
> > > check number and R&T for the bank and a card number and type for card 
> > > purchases.
> > > 
> > > If what you have posted it all the fields in both tables, I see no reliable 
> > > way to join the two so that all data are captured correctly.
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "the bp Guy" wrote:
> > > 
> > > > we are a wholesale company selling fuel and on a daily basis we track the 
> > > > credit cards that customer uses [tblcreditcard] when customer buys fuel 
> > > > [tblinvoice] they want to deduct creditcards collected from invoice. want to 
> > > > make report to show invoice for fuel purchased minus credit cards in the time 
> > > > frame 
> > > > 
> > > > thanks again
> > > > 
> > > > "Klatuu" wrote:
> > > > 
> > > > > At what point in time are the two records in the two different tables created?
> > > > > I can see where the dates may be different because often credit cards or 
> > > > > debit cards clearing date may be some time after the transaction date.
> > > > > Is it possible you could add the invoice number to the credit card table or 
> > > > > the credit card number to both tables?
> > > > > 
> > > > > To be able to join the two, you need some common field or fields.  The only 
> > > > > thing that would appear to be in common would be customer and amount, but I 
> > > > > don't know if those will always be a match.  If they are, you could join on a 
> > > > > combination of those two fields.
> > > > > -- 
> > > > > Dave Hargis, Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "the bp Guy" wrote:
> > > > > 
> > > > > > have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> > > > > > tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> > > > > > setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> > > > > > [fee] they sometimes do not have same dates. I can get info from one or the 
> > > > > > other tried to join date fields and do outer join but cannot get info if 
> > > > > > there is not data for both only shows for either invoice or credit card
> > > > > > 
> > > > > > Thanks for your help

Right track, wrong train.  Not suprised, I kind of expected that.  How we 
approach it depends on a couple of things.
1. Do you need to see line item data by date for the credit card data or can 
in be accumulated into one line?
2. Is this going to be on a report, or how will you output the data?

-- 
Dave Hargis, Microsoft Access MVP


"the bp Guy" wrote:

> tblInvoice.DateCustomer Invoice#Amount	Gross     Fee        tblcreditcard.Date	
> 9/1/2008	Stuart	 55550	 50000	$1,500.36 $78.25   8/23/2008
> 9/1/2008	Stuart	 55550	 50000	$1.00	  $1.00   8/24/2008
> 9/1/2008	Stuart	 55550	 50000	$1,800.36 $128.36  8/25/2008
> 9/1/2008	Stuart	 55550	 50000	$1,000.00 $95.00    8/26/2008
> 9/1/2008	Stuart	 55550	 50000	$1,500.00 $100.00  8/27/2008
> 
> We are on the right track but as you can see the invoice shows up for every 
> line that there is credit card data. the credit cards accumulate until the 
> fuel is invoiced and subtracted of of the invoice
> 
> Thanks
> "Klatuu" wrote:
> 
> > It seems strange to me that the credit card dates would be earlier than the 
> > purchase dates, but let's see how this works.  It is totally untested and may 
> > take some fixing, but I think it is headed in the right direction:
> > 
> > SELECT tblinvoice.[date], tblinvoice.[Customer], tblinvoice.[Invoice#], 
> > tblinvoice.[amount], tblcreditcard.[gross], tblcreditcard.[fee]
> > FROM tblInvoice
> > LEFT JOIN tblcreditcard ON tblinvoice.[Customer] = tblcreditcard.[customer]
> > WHERE tblinvoice.[date] = #9/1/2008# AND tblcreditcard.[date] BETWEEN 
> > #8/23/2008# AND #8/28/2008#;
> > 
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "the bp Guy" wrote:
> > 
> > > this is a check and balance to show the credit cards that we have been paid 
> > > for gets reimbursed to customer. what i want to see on the report is total 
> > > amt of invoice for fuel purchased ($25,000 on 9/1/08) and list credit cards 
> > > to be credited ( 08/23/08 to 08/28/08) which might be ($19,000) and that 
> > > difference we would eft from customer
> > > 
> > > Thanks for your help
> > > 
> > > "Klatuu" wrote:
> > > 
> > > > I am surprised your tblinvoice does not have a payment method field or child 
> > > > table that allows you to track how the invoice was paid and for checks a 
> > > > check number and R&T for the bank and a card number and type for card 
> > > > purchases.
> > > > 
> > > > If what you have posted it all the fields in both tables, I see no reliable 
> > > > way to join the two so that all data are captured correctly.
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "the bp Guy" wrote:
> > > > 
> > > > > we are a wholesale company selling fuel and on a daily basis we track the 
> > > > > credit cards that customer uses [tblcreditcard] when customer buys fuel 
> > > > > [tblinvoice] they want to deduct creditcards collected from invoice. want to 
> > > > > make report to show invoice for fuel purchased minus credit cards in the time 
> > > > > frame 
> > > > > 
> > > > > thanks again
> > > > > 
> > > > > "Klatuu" wrote:
> > > > > 
> > > > > > At what point in time are the two records in the two different tables created?
> > > > > > I can see where the dates may be different because often credit cards or 
> > > > > > debit cards clearing date may be some time after the transaction date.
> > > > > > Is it possible you could add the invoice number to the credit card table or 
> > > > > > the credit card number to both tables?
> > > > > > 
> > > > > > To be able to join the two, you need some common field or fields.  The only 
> > > > > > thing that would appear to be in common would be customer and amount, but I 
> > > > > > don't know if those will always be a match.  If they are, you could join on a 
> > > > > > combination of those two fields.
> > > > > > -- 
> > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > 
> > > > > > 
> > > > > > "the bp Guy" wrote:
> > > > > > 
> > > > > > > have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> > > > > > > tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> > > > > > > setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> > > > > > > [fee] they sometimes do not have same dates. I can get info from one or the 
> > > > > > > other tried to join date fields and do outer join but cannot get info if 
> > > > > > > there is not data for both only shows for either invoice or credit card
> > > > > > > 
> > > > > > > Thanks for your help

it will be a report and would like to see line item data.

thanks

"Klatuu" wrote:

> Right track, wrong train.  Not suprised, I kind of expected that.  How we 
> approach it depends on a couple of things.
> 1. Do you need to see line item data by date for the credit card data or can 
> in be accumulated into one line?
> 2. Is this going to be on a report, or how will you output the data?
> 
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "the bp Guy" wrote:
> 
> > tblInvoice.DateCustomer Invoice#Amount	Gross     Fee        tblcreditcard.Date	
> > 9/1/2008	Stuart	 55550	 50000	$1,500.36 $78.25   8/23/2008
> > 9/1/2008	Stuart	 55550	 50000	$1.00	  $1.00   8/24/2008
> > 9/1/2008	Stuart	 55550	 50000	$1,800.36 $128.36  8/25/2008
> > 9/1/2008	Stuart	 55550	 50000	$1,000.00 $95.00    8/26/2008
> > 9/1/2008	Stuart	 55550	 50000	$1,500.00 $100.00  8/27/2008
> > 
> > We are on the right track but as you can see the invoice shows up for every 
> > line that there is credit card data. the credit cards accumulate until the 
> > fuel is invoiced and subtracted of of the invoice
> > 
> > Thanks
> > "Klatuu" wrote:
> > 
> > > It seems strange to me that the credit card dates would be earlier than the 
> > > purchase dates, but let's see how this works.  It is totally untested and may 
> > > take some fixing, but I think it is headed in the right direction:
> > > 
> > > SELECT tblinvoice.[date], tblinvoice.[Customer], tblinvoice.[Invoice#], 
> > > tblinvoice.[amount], tblcreditcard.[gross], tblcreditcard.[fee]
> > > FROM tblInvoice
> > > LEFT JOIN tblcreditcard ON tblinvoice.[Customer] = tblcreditcard.[customer]
> > > WHERE tblinvoice.[date] = #9/1/2008# AND tblcreditcard.[date] BETWEEN 
> > > #8/23/2008# AND #8/28/2008#;
> > > 
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "the bp Guy" wrote:
> > > 
> > > > this is a check and balance to show the credit cards that we have been paid 
> > > > for gets reimbursed to customer. what i want to see on the report is total 
> > > > amt of invoice for fuel purchased ($25,000 on 9/1/08) and list credit cards 
> > > > to be credited ( 08/23/08 to 08/28/08) which might be ($19,000) and that 
> > > > difference we would eft from customer
> > > > 
> > > > Thanks for your help
> > > > 
> > > > "Klatuu" wrote:
> > > > 
> > > > > I am surprised your tblinvoice does not have a payment method field or child 
> > > > > table that allows you to track how the invoice was paid and for checks a 
> > > > > check number and R&T for the bank and a card number and type for card 
> > > > > purchases.
> > > > > 
> > > > > If what you have posted it all the fields in both tables, I see no reliable 
> > > > > way to join the two so that all data are captured correctly.
> > > > > -- 
> > > > > Dave Hargis, Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "the bp Guy" wrote:
> > > > > 
> > > > > > we are a wholesale company selling fuel and on a daily basis we track the 
> > > > > > credit cards that customer uses [tblcreditcard] when customer buys fuel 
> > > > > > [tblinvoice] they want to deduct creditcards collected from invoice. want to 
> > > > > > make report to show invoice for fuel purchased minus credit cards in the time 
> > > > > > frame 
> > > > > > 
> > > > > > thanks again
> > > > > > 
> > > > > > "Klatuu" wrote:
> > > > > > 
> > > > > > > At what point in time are the two records in the two different tables created?
> > > > > > > I can see where the dates may be different because often credit cards or 
> > > > > > > debit cards clearing date may be some time after the transaction date.
> > > > > > > Is it possible you could add the invoice number to the credit card table or 
> > > > > > > the credit card number to both tables?
> > > > > > > 
> > > > > > > To be able to join the two, you need some common field or fields.  The only 
> > > > > > > thing that would appear to be in common would be customer and amount, but I 
> > > > > > > don't know if those will always be a match.  If they are, you could join on a 
> > > > > > > combination of those two fields.
> > > > > > > -- 
> > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > 
> > > > > > > 
> > > > > > > "the bp Guy" wrote:
> > > > > > > 
> > > > > > > > have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> > > > > > > > tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> > > > > > > > setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> > > > > > > > [fee] they sometimes do not have same dates. I can get info from one or the 
> > > > > > > > other tried to join date fields and do outer join but cannot get info if 
> > > > > > > > there is not data for both only shows for either invoice or credit card
> > > > > > > > 
> > > > > > > > Thanks for your help

Okay, then the query will work for you.
In your report use the sorting and grouping to create a group on the 
customer and or Invoice number (depending on how you want it to sort and 
whether the same customer might have multiple invoices).  Create a header and 
footer section for this group.

In the group header, put the customer/invoice information
In the detail section, put the creditcard information
in the group footer, you can use the Sum funciton to total the credit card 
gross, and fees and do any other calculations you need.
-- 
Dave Hargis, Microsoft Access MVP


"the bp Guy" wrote:

> it will be a report and would like to see line item data.
> 
> thanks
> 
> "Klatuu" wrote:
> 
> > Right track, wrong train.  Not suprised, I kind of expected that.  How we 
> > approach it depends on a couple of things.
> > 1. Do you need to see line item data by date for the credit card data or can 
> > in be accumulated into one line?
> > 2. Is this going to be on a report, or how will you output the data?
> > 
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "the bp Guy" wrote:
> > 
> > > tblInvoice.DateCustomer Invoice#Amount	Gross     Fee        tblcreditcard.Date	
> > > 9/1/2008	Stuart	 55550	 50000	$1,500.36 $78.25   8/23/2008
> > > 9/1/2008	Stuart	 55550	 50000	$1.00	  $1.00   8/24/2008
> > > 9/1/2008	Stuart	 55550	 50000	$1,800.36 $128.36  8/25/2008
> > > 9/1/2008	Stuart	 55550	 50000	$1,000.00 $95.00    8/26/2008
> > > 9/1/2008	Stuart	 55550	 50000	$1,500.00 $100.00  8/27/2008
> > > 
> > > We are on the right track but as you can see the invoice shows up for every 
> > > line that there is credit card data. the credit cards accumulate until the 
> > > fuel is invoiced and subtracted of of the invoice
> > > 
> > > Thanks
> > > "Klatuu" wrote:
> > > 
> > > > It seems strange to me that the credit card dates would be earlier than the 
> > > > purchase dates, but let's see how this works.  It is totally untested and may 
> > > > take some fixing, but I think it is headed in the right direction:
> > > > 
> > > > SELECT tblinvoice.[date], tblinvoice.[Customer], tblinvoice.[Invoice#], 
> > > > tblinvoice.[amount], tblcreditcard.[gross], tblcreditcard.[fee]
> > > > FROM tblInvoice
> > > > LEFT JOIN tblcreditcard ON tblinvoice.[Customer] = tblcreditcard.[customer]
> > > > WHERE tblinvoice.[date] = #9/1/2008# AND tblcreditcard.[date] BETWEEN 
> > > > #8/23/2008# AND #8/28/2008#;
> > > > 
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "the bp Guy" wrote:
> > > > 
> > > > > this is a check and balance to show the credit cards that we have been paid 
> > > > > for gets reimbursed to customer. what i want to see on the report is total 
> > > > > amt of invoice for fuel purchased ($25,000 on 9/1/08) and list credit cards 
> > > > > to be credited ( 08/23/08 to 08/28/08) which might be ($19,000) and that 
> > > > > difference we would eft from customer
> > > > > 
> > > > > Thanks for your help
> > > > > 
> > > > > "Klatuu" wrote:
> > > > > 
> > > > > > I am surprised your tblinvoice does not have a payment method field or child 
> > > > > > table that allows you to track how the invoice was paid and for checks a 
> > > > > > check number and R&T for the bank and a card number and type for card 
> > > > > > purchases.
> > > > > > 
> > > > > > If what you have posted it all the fields in both tables, I see no reliable 
> > > > > > way to join the two so that all data are captured correctly.
> > > > > > -- 
> > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > 
> > > > > > 
> > > > > > "the bp Guy" wrote:
> > > > > > 
> > > > > > > we are a wholesale company selling fuel and on a daily basis we track the 
> > > > > > > credit cards that customer uses [tblcreditcard] when customer buys fuel 
> > > > > > > [tblinvoice] they want to deduct creditcards collected from invoice. want to 
> > > > > > > make report to show invoice for fuel purchased minus credit cards in the time 
> > > > > > > frame 
> > > > > > > 
> > > > > > > thanks again
> > > > > > > 
> > > > > > > "Klatuu" wrote:
> > > > > > > 
> > > > > > > > At what point in time are the two records in the two different tables created?
> > > > > > > > I can see where the dates may be different because often credit cards or 
> > > > > > > > debit cards clearing date may be some time after the transaction date.
> > > > > > > > Is it possible you could add the invoice number to the credit card table or 
> > > > > > > > the credit card number to both tables?
> > > > > > > > 
> > > > > > > > To be able to join the two, you need some common field or fields.  The only 
> > > > > > > > thing that would appear to be in common would be customer and amount, but I 
> > > > > > > > don't know if those will always be a match.  If they are, you could join on a 
> > > > > > > > combination of those two fields.
> > > > > > > > -- 
> > > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > > 
> > > > > > > > 
> > > > > > > > "the bp Guy" wrote:
> > > > > > > > 
> > > > > > > > > have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> > > > > > > > > tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> > > > > > > > > setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> > > > > > > > > [fee] they sometimes do not have same dates. I can get info from one or the 
> > > > > > > > > other tried to join date fields and do outer join but cannot get info if 
> > > > > > > > > there is not data for both only shows for either invoice or credit card
> > > > > > > > > 
> > > > > > > > > Thanks for your help

Works great thanks

"Klatuu" wrote:

> Okay, then the query will work for you.
> In your report use the sorting and grouping to create a group on the 
> customer and or Invoice number (depending on how you want it to sort and 
> whether the same customer might have multiple invoices).  Create a header and 
> footer section for this group.
> 
> In the group header, put the customer/invoice information
> In the detail section, put the creditcard information
> in the group footer, you can use the Sum funciton to total the credit card 
> gross, and fees and do any other calculations you need.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "the bp Guy" wrote:
> 
> > it will be a report and would like to see line item data.
> > 
> > thanks
> > 
> > "Klatuu" wrote:
> > 
> > > Right track, wrong train.  Not suprised, I kind of expected that.  How we 
> > > approach it depends on a couple of things.
> > > 1. Do you need to see line item data by date for the credit card data or can 
> > > in be accumulated into one line?
> > > 2. Is this going to be on a report, or how will you output the data?
> > > 
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "the bp Guy" wrote:
> > > 
> > > > tblInvoice.DateCustomer Invoice#Amount	Gross     Fee        tblcreditcard.Date	
> > > > 9/1/2008	Stuart	 55550	 50000	$1,500.36 $78.25   8/23/2008
> > > > 9/1/2008	Stuart	 55550	 50000	$1.00	  $1.00   8/24/2008
> > > > 9/1/2008	Stuart	 55550	 50000	$1,800.36 $128.36  8/25/2008
> > > > 9/1/2008	Stuart	 55550	 50000	$1,000.00 $95.00    8/26/2008
> > > > 9/1/2008	Stuart	 55550	 50000	$1,500.00 $100.00  8/27/2008
> > > > 
> > > > We are on the right track but as you can see the invoice shows up for every 
> > > > line that there is credit card data. the credit cards accumulate until the 
> > > > fuel is invoiced and subtracted of of the invoice
> > > > 
> > > > Thanks
> > > > "Klatuu" wrote:
> > > > 
> > > > > It seems strange to me that the credit card dates would be earlier than the 
> > > > > purchase dates, but let's see how this works.  It is totally untested and may 
> > > > > take some fixing, but I think it is headed in the right direction:
> > > > > 
> > > > > SELECT tblinvoice.[date], tblinvoice.[Customer], tblinvoice.[Invoice#], 
> > > > > tblinvoice.[amount], tblcreditcard.[gross], tblcreditcard.[fee]
> > > > > FROM tblInvoice
> > > > > LEFT JOIN tblcreditcard ON tblinvoice.[Customer] = tblcreditcard.[customer]
> > > > > WHERE tblinvoice.[date] = #9/1/2008# AND tblcreditcard.[date] BETWEEN 
> > > > > #8/23/2008# AND #8/28/2008#;
> > > > > 
> > > > > -- 
> > > > > Dave Hargis, Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "the bp Guy" wrote:
> > > > > 
> > > > > > this is a check and balance to show the credit cards that we have been paid 
> > > > > > for gets reimbursed to customer. what i want to see on the report is total 
> > > > > > amt of invoice for fuel purchased ($25,000 on 9/1/08) and list credit cards 
> > > > > > to be credited ( 08/23/08 to 08/28/08) which might be ($19,000) and that 
> > > > > > difference we would eft from customer
> > > > > > 
> > > > > > Thanks for your help
> > > > > > 
> > > > > > "Klatuu" wrote:
> > > > > > 
> > > > > > > I am surprised your tblinvoice does not have a payment method field or child 
> > > > > > > table that allows you to track how the invoice was paid and for checks a 
> > > > > > > check number and R&T for the bank and a card number and type for card 
> > > > > > > purchases.
> > > > > > > 
> > > > > > > If what you have posted it all the fields in both tables, I see no reliable 
> > > > > > > way to join the two so that all data are captured correctly.
> > > > > > > -- 
> > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > 
> > > > > > > 
> > > > > > > "the bp Guy" wrote:
> > > > > > > 
> > > > > > > > we are a wholesale company selling fuel and on a daily basis we track the 
> > > > > > > > credit cards that customer uses [tblcreditcard] when customer buys fuel 
> > > > > > > > [tblinvoice] they want to deduct creditcards collected from invoice. want to 
> > > > > > > > make report to show invoice for fuel purchased minus credit cards in the time 
> > > > > > > > frame 
> > > > > > > > 
> > > > > > > > thanks again
> > > > > > > > 
> > > > > > > > "Klatuu" wrote:
> > > > > > > > 
> > > > > > > > > At what point in time are the two records in the two different tables created?
> > > > > > > > > I can see where the dates may be different because often credit cards or 
> > > > > > > > > debit cards clearing date may be some time after the transaction date.
> > > > > > > > > Is it possible you could add the invoice number to the credit card table or 
> > > > > > > > > the credit card number to both tables?
> > > > > > > > > 
> > > > > > > > > To be able to join the two, you need some common field or fields.  The only 
> > > > > > > > > thing that would appear to be in common would be customer and amount, but I 
> > > > > > > > > don't know if those will always be a match.  If they are, you could join on a 
> > > > > > > > > combination of those two fields.
> > > > > > > > > -- 
> > > > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > > > 
> > > > > > > > > 
> > > > > > > > > "the bp Guy" wrote:
> > > > > > > > > 
> > > > > > > > > > have tblinvoice with fields [id] [date] [Customer] {invoice#] [amount] and 
> > > > > > > > > > tblcreditcard with fields [id] [date] [customer] [gross] [fee]. I want to 
> > > > > > > > > > setup query to get [date] [customer] [invoice#] [amount] and [gross] and 
> > > > > > > > > > [fee] they sometimes do not have same dates. I can get info from one or the 
> > > > > > > > > > other tried to join date fields and do outer join but cannot get info if 
> > > > > > > > > > there is not data for both only shows for either invoice or credit card
> > > > > > > > > > 
> > > > > > > > > > Thanks for your help