two table query

[the bp Guy]

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

 

[Klatuu]

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.

 

[the bp Guy]

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

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

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

 

[Klatuu]

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

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

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

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

 

[the bp Guy]

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

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

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

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


:

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

 

[Klatuu]

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

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

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

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

:

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


:

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

 

[the bp Guy]

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

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

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

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


:

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

:

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


:

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

 

[Klatuu]

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

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

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

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

:

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


:

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

:

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


:

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

 

[the bp Guy]

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

thanks

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

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

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


:

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

:

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


:

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

:

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


:

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

 

[Klatuu]

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

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

thanks

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

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
:

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


:

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

:

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


:

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

:

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


:

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

 

[the bp Guy]

Works great thanks

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

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

thanks

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


:

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
:

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


:

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

:

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


:

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

:

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


:

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