I connected to QB via ODBC from MS Access and linked to the following:
- QBReportAdminGroup_v_txn_invoice_hdr
- QBReportAdminGroup_v_txn_invoice_line
- QBReportAdminGroup_v_txn_received_payment_hdr
- QBReportAdminGroup_v_txn_received_payment_line
I want to create a query to join payments and invoices but I cannot find a common key between them. Is there something I'm missing? LE (added table fields):
QBReportAdminGroup_v_txn_invoice_hdr
- cf_name_id
- cleared_type_h
- contact_id_h
- currency_id_h
- custom_style_id_h
- customer_id_h
- customer_message_id
- db_created_tms
- db_modified_tms
- delivery_date
- doc_num_h
- due_date_h
- employee_id_h
- end_balance_amt
- exchange_rate_h
- expected_date
- fob
- is_active
- is_condensed
- is_deposited_h
- is_finance_charge
- is_home_currency_adjustment
- is_opening_balance
- is_paid_h
- is_purchase_h
- is_purchase_order_h
- is_received_h
- is_refunded
- is_sale_h
- is_shown_in_home_currency
- item_id_h
- last_auditable_action
- last_edit_by
- memo_h
- modtimestamp_tms
- other
- other_id_h
- payment_method_id_h
- po_num
- sales_order_num
- sales_rep_id
- sales_tax_code_id_h
- ship_date
- ship_to_id
- ship_to_name_id
- ship_via_id
- target_id_h
- tax_code_id_h
- terms_id
- trans_num
- transaction_date_h
- transaction_id
- transaction_name_type_h
- transaction_view_type_h
- txn_ident
- vendor_id_h
- vendor_message
QBReportAdminGroup_v_txn_invoice_line
- cf_name_id
- cleared_type_h
- contact_id_h
- currency_id_h
- custom_style_id_h
- customer_id_h
- customer_message_id
- db_created_tms
- db_modified_tms
- delivery_date
- doc_num_h
- due_date_h
- employee_id_h
- end_balance_amt
- exchange_rate_h
- expected_date
- fob
- is_active
- is_condensed
- is_deposited_h
- is_finance_charge
- is_home_currency_adjustment
- is_opening_balance
- is_paid_h
- is_purchase_h
- is_purchase_order_h
- is_received_h
- is_refunded
- is_sale_h
- is_shown_in_home_currency
- item_id_h
- last_auditable_action
- last_edit_by
- memo_h
- modtimestamp_tms
- other
- other_id_h
- payment_method_id_h
- po_num
- sales_order_num
- sales_rep_id
- sales_tax_code_id_h
- ship_date
- ship_to_id
- ship_to_name_id
- ship_via_id
- target_id_h
- tax_code_id_h
- terms_id
- trans_num
- transaction_date_h
- transaction_id
- transaction_name_type_h
- transaction_view_type_h
- txn_ident
- vendor_id_h
- vendor_message
- cf_item_id
- account_id
- amount_amt
- bank_account_type
- bank_ref_name
- billed_date
- class_id
- cleared_type
- cogs_amt
- contact_id
- currency_id
- customer_id
- doc_num
- due_date
- employee_id
- estimate_amt
- exchange_rate
- foreign_amount_amt
- is_adjustment
- is_applied_discount
- is_arap
- is_based_on_time_activity
- is_begin_balance
- is_build
- is_deposited
- is_discount
- is_estimated
- is_gain_loss_target
- is_groupend_row
- is_inactive
- is_invoiced
- is_item_from_estimate
- is_item_from_sales_order
- is_less_cashback
- is_manually_closed
- is_paid
- is_purchase
- is_purchase_order
- is_receipt
- is_received
- is_sale
- is_sales_order
- is_source
- is_trans_tax
- is_uses_billed_date
- is_value_adjust
- item_id
- lineitem_name_type
- manufacturer_part_num
- markup_amt
- markup_amt_pct
- memo
- next_target_id
- nom_total_amt
- open_balance_amt
- open_qnty
- other_id
- other1
- other2
- payment_entry_type
- payment_method_id
- quantity_qnty
- reconcile_date
- reimbursed_type
- sales_order_qnty
- sales_tax_code_id
- service_date
- sibling_account_id
- site_id
- target_id
- tax_code_id
- taxable_discount_amt
- transaction_date
- transaction_name_type
- transaction_view_type
- txnline_ident
- unit_id
- unit_price_amt
- unit_price_pct
- vendor_id
QBReportAdminGroup_v_txn_received_payment_hdr
- cf_name_id
- cleared_type_h
- contact_id_h
- currency_id_h
- custom_style_id
- customer_id_h
- customer_message_id
- db_created_tms
- db_modified_tms
- delivery_date
- doc_num_h
- due_date_h
- employee_id_h
- end_balance_amt
- exchange_rate_h
- expected_date
- fob
- is_active
- is_condensed
- is_deposited_h
- is_finance_charge
- is_home_currency_adjustment
- is_opening_balance
- is_paid_h
- is_purchase_h
- is_purchase_order_h
- is_received_h
- is_refunded
- is_sale_h
- is_shown_in_home_currency
- item_id_h
- last_auditable_action
- last_edit_by
- memo_h
- modtimestamp_tms
- other
- other_id_h
- payment_method_id_h
- po_num
- sales_order_num
- sales_rep_id
- sales_tax_code_id_h
- ship_date
- ship_to_id
- ship_to_name_id
- ship_via_id
- target_id_h
- tax_code_id_h
- terms_id
- trans_num
- transaction_date_h
- transaction_id
- transaction_name_type_h
- transaction_view_type_h
- txn_ident
- vendor_id_h
- vendor_message
QBReportAdminGroup_v_txn_received_payment_line
- cf_name_id
- cleared_type_h
- contact_id_h
- currency_id_h
- custom_style_id
- customer_id_h
- customer_message_id
- db_created_tms
- db_modified_tms
- delivery_date
- doc_num_h
- due_date_h
- employee_id_h
- end_balance_amt
- exchange_rate_h
- expected_date
- fob
- is_active
- is_condensed
- is_deposited_h
- is_finance_charge
- is_home_currency_adjustment
- is_opening_balance
- is_paid_h
- is_purchase_h
- is_purchase_order_h
- is_received_h
- is_refunded
- is_sale_h
- is_shown_in_home_currency
- item_id_h
- last_auditable_action
- last_edit_by
- memo_h
- modtimestamp_tms
- other
- other_id_h
- payment_method_id_h
- po_num
- sales_order_num
- sales_rep_id
- sales_tax_code_id_h
- ship_date
- ship_to_id
- ship_to_name_id
- ship_via_id
- target_id_h
- tax_code_id_h
- terms_id
- trans_num
- transaction_date_h
- transaction_id
- transaction_name_type_h
- transaction_view_type_h
- txn_ident
- vendor_id_h
- vendor_message
- cf_item_id
- account_id
- amount_amt
- bank_account_type
- bank_ref_name
- billed_date
- class_id
- cleared_type
- cogs_amt
- contact_id
- currency_id
- customer_id
- doc_num
- due_date
- employee_id
- estimate_amt
- exchange_rate
- foreign_amount_amt
- is_adjustment
- is_applied_discount
- is_arap
- is_based_on_time_activity
- is_begin_balance
- is_build
- is_deposited
- is_discount
- is_estimated
- is_gain_loss_target
- is_groupend_row
- is_inactive
- is_invoiced
- is_item_from_estimate
- is_item_from_sales_order
- is_less_cashback
- is_manually_closed
- is_paid
- is_purchase
- is_purchase_order
- is_receipt
- is_received
- is_sale
- is_sales_order
- is_source
- is_trans_tax
- is_uses_billed_date
- is_value_adjust
- item_id
- lineitem_name_type
- manufacturer_part_num
- markup_amt
- markup_amt_pct
- memo
- next_target_id
- nom_total_amt
- open_balance_amt
- open_qnty
- other_id
- other1
- other2
- payment_entry_type
- payment_method_id
- quantity_qnty
- reconcile_date
- reimbursed_type
- sales_order_qnty
- sales_tax_code_id
- service_date
- sibling_account_id
- site_id
- target_id
- tax_code_id
- taxable_discount_amt
- transaction_date
- transaction_name_type
- transaction_view_type
- unit_id
- unit_price_amt
- unit_price_pct
- vendor_id
You should use v_txn_link table in order to relate payment and invoices.
These are the relations