Query Problem

J

Jeremy Dove

I have a problem. Here is the setup.

5 tables Main,Customer,Billing,Service,Production.

There is a primary key of Contract_ID and Contract_date
on the main table. The other 4 tables are joined using
the primary key.

If you look at the tables, all 5 tables have 721 records.
All of which are connected.

Here is the issue, when I write a query for main only
requesting contract_id & contract_date it shows 721.

When i write a query requesting contract_id and
contract_date and a field for billing I get 648. Customer
shows 703. Production is 720. Service is 720.

When I pull fields from main customer and billing it goes
down to 644.

When I pull fields from main customer and production it
is 703

When I pull fields from main customer and service it is
683.

Does anyone have any idea what is going on?
 
D

Duane Hookom

You probably don't have a single related record in each table. You can use
joins that select all records from one table and only records from the other
table where they match. Double-click your join lines to view these options.
 
N

Newguy

My guess is that if you fix your joins (i.e. highlight the
join lines and select all from main and only those in
billing where the joined fields are equal.

Often times if you have customer id that occasionally has
a leading zero, store the numbers as text. Otherwise, the
joins may not work properly.
 
J

Jeremy Dove

I tried fixing the joins, i can't really store the
customer_id as text as I need that field to auto
increment.

Is there anything else that you can think of?
 
J

Jeremy Dove

I have the joins set correctly. I went and made sure that
they are select all from Main and only matching from the
other table. When I go to the table and look at the view
the record is there and I can do a break out and see the
information on the other tables. So I don't understand
why that small number of records don't show.
 
J

Jeremy Dove

Here are the SQL Views that I am talking about

Production :
SELECT main.contract_id, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
production.bldg_permit, production.mech_permit,
production.foreman_tick_print_date,
production.permit_submit_date,
production.rough_complete_date,
production.rough_pcwk_amt,
production.indoor_complete_date,
production.indoor_pcwk_amt,
production.rough_inspected_by,
production.rough_inspected_date,
production.rough_inspect_results,
production.wp_hour_rate_1,
production.wp_work_performed_1,
production.wp_work_date_1, production.wp_hour_rate_2,
production.wp_work_performed_2,
production.wp_work_date_2, production.wp_hour_rate_3,
production.wp_work_performed_3,
production.wp_work_date_3, production.extra_po_amt_1,
production.extra_po_date_1, production.extra_po_amt_2,
production.extra_po_date_2, production.extra_po_amount_3,
production.extra_po_date_3,
production.tubing_tick_printed_date,
production.tubing_install_tech,
production.tubing_install_date,
production.tubing_install_amt, production.
[r+g_complete_date], production.pc_worker_name,
production.outdoor_tick_print_date,
production.outdoor_install_tech,
production.outdoor_install_date,
production.outdoor_install_amt, production.notes AS
production_notes, production.bldg_permit_b,
production.mech_permit_b, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, inspection.[inspection reqd]
FROM ((main INNER JOIN inspection ON (main.contract_id =
inspection.contract_id) AND (main.contract_date =
inspection.contract_date)) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN production ON (main.contract_id =
production.contract_id) AND (main.contract_date =
production.contract_date);

Billing :
SELECT main.contract_id, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes, customerinfo.cust_fname_b,
customerinfo.cust_lname_b, customerinfo.c_homephone_b,
customerinfo.c_workphone_b, customerinfo.c_cellphone_b,
customerinfo.c_email_b, billing.rough_bill_date,
billing.indoor_bill_date, billing.outdoor_bill_date,
billing.final_bill_date, billing.options_bill_date,
billing.po_number, billing.po_issue_date,
billing.po_bill_date, billing.po_number_2,
billing.po_issue_date_2, billing.po_bill_date_2,
billing.po_number_3, billing.po_issue_date_3,
billing.po_bill_date_3, billing.po_number_4,
billing.po_issue_date_4, billing.po_bill_date_4
FROM (main INNER JOIN customerinfo ON (main.contract_id =
customerinfo.contract_id) AND (main.contract_date =
customerinfo.contract_date)) INNER JOIN billing ON
(main.contract_id = billing.contract_id) AND
(main.contract_date = billing.Contract_date);

Service :
SELECT main.contract_id, main.contract_date,
main.Builder, housetype.z1_furn_model AS
housetype_z1_furn_model, housetype.z1_coil_model AS
housetype_z1_coil_model, housetype.z1_ac_model AS
housetype_z1_ac_model, housetype.z2_furn_model AS
housetype_z2_furn_model, housetype.z2_coil_model AS
housetype_z2_coil_model, housetype.z2_ac_model AS
housetype_z2_ac_model, housetype.z3_furn_model AS
housetype_z3_furn_model, housetype.z3_coil_model AS
housetype_z3_coil_model, housetype.z3_ac_model AS
housetype_z3_ac_model, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
service.z1_furn_model AS service_z1_furn_model,
service.z1_serial_furn, service.z1_location_furn,
service.z1_coil_model AS service_z1_coil_model,
service.z1_serial_coil, service.z1_location_coil,
service.z1_ac_model AS service_z1_ac_model,
service.z1_serial_ac, service.z1_location_ac,
service.z2_furn_model AS service_z2_furn_model,
service.z2_serial_furn, service.z2_location_furn,
service.z2_coil_model AS service_z2_coil_model,
service.z2_serial_coil, service.z2_location_coil,
service.z2_ac_model AS service_z2_ac_model,
service.z2_serial_ac, service.z2_location_ac,
service.z3_furn_model AS service_z3_furn_model,
service.z3_serial_furn, service.z3_location_furn,
service.z3_coil_model AS service_z3_coil_model,
service.z3_serial_coil, service.z3_location_coil,
service.z3_ac_model AS service_z3_ac_model,
service.z3_serial_ac, service.z3_location_ac,
service.humid_z1_model, service.humid_z1_install_date,
service.humid_z1_tech, service.humid_z2_model,
service.humid_z2_install_date, service.humid_z2_tech,
service.humid_z3_model, service.humid_z3_install_date,
service.humid_z3_tech, service.elecac_z1_model,
service.elecac_z1_install_date, service.elecac_z1_tech,
service.elecac_z2_model, service.eleac_z2_install_date,
service.elecac_z2_tech, service.elecac_z3_model,
service.elecac_z3_install_date, service.elecac_z3_tech,
service.ptstat_z1_model, service.ptstat_z1_install_date,
service.ptstat_z1_tech, service.ptstat_z2_model,
service.ptstat_z2_install_date, service.ptstat_z2_tech,
service.ptstat_z3_model, service.ptstat_z3_install_date,
service.ptstat_z3_tech, service.zoning_systems_notes,
service.other_options_notes, service.st_check_comp_date,
service.st_check_tech, service.settle_date,
service.provided_by, service.final_inspected_by,
service.final_results, service.notes AS service_notes,
service.service_history, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, main.s_option_1, main.s_option_2,
main.s_option_3, main.s_option_4, main.s_option_5,
main.s_option_6
FROM ((housetype INNER JOIN main ON housetype.house_name
= main.Housetype) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN service ON (main.contract_id = service.contract_id)
AND (main.contract_date = service.contract_date);


Those are the main ones with issues. Let me know what you
think. Thanks
 
D

Duane Hookom

I would remove one table at a time from the query until you find which one
causes the missing records. Then use the un-matched query wizard. I'm would
be surprised if all relationships would/should have a one-to-one
relationship. I suppose this is possible with an un-normalized table
structure.

--
Duane Hookom
MS Access MVP


Jeremy Dove said:
Here are the SQL Views that I am talking about

Production :
SELECT main.contract_id, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
production.bldg_permit, production.mech_permit,
production.foreman_tick_print_date,
production.permit_submit_date,
production.rough_complete_date,
production.rough_pcwk_amt,
production.indoor_complete_date,
production.indoor_pcwk_amt,
production.rough_inspected_by,
production.rough_inspected_date,
production.rough_inspect_results,
production.wp_hour_rate_1,
production.wp_work_performed_1,
production.wp_work_date_1, production.wp_hour_rate_2,
production.wp_work_performed_2,
production.wp_work_date_2, production.wp_hour_rate_3,
production.wp_work_performed_3,
production.wp_work_date_3, production.extra_po_amt_1,
production.extra_po_date_1, production.extra_po_amt_2,
production.extra_po_date_2, production.extra_po_amount_3,
production.extra_po_date_3,
production.tubing_tick_printed_date,
production.tubing_install_tech,
production.tubing_install_date,
production.tubing_install_amt, production.
[r+g_complete_date], production.pc_worker_name,
production.outdoor_tick_print_date,
production.outdoor_install_tech,
production.outdoor_install_date,
production.outdoor_install_amt, production.notes AS
production_notes, production.bldg_permit_b,
production.mech_permit_b, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, inspection.[inspection reqd]
FROM ((main INNER JOIN inspection ON (main.contract_id =
inspection.contract_id) AND (main.contract_date =
inspection.contract_date)) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN production ON (main.contract_id =
production.contract_id) AND (main.contract_date =
production.contract_date);

Billing :
SELECT main.contract_id, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes, customerinfo.cust_fname_b,
customerinfo.cust_lname_b, customerinfo.c_homephone_b,
customerinfo.c_workphone_b, customerinfo.c_cellphone_b,
customerinfo.c_email_b, billing.rough_bill_date,
billing.indoor_bill_date, billing.outdoor_bill_date,
billing.final_bill_date, billing.options_bill_date,
billing.po_number, billing.po_issue_date,
billing.po_bill_date, billing.po_number_2,
billing.po_issue_date_2, billing.po_bill_date_2,
billing.po_number_3, billing.po_issue_date_3,
billing.po_bill_date_3, billing.po_number_4,
billing.po_issue_date_4, billing.po_bill_date_4
FROM (main INNER JOIN customerinfo ON (main.contract_id =
customerinfo.contract_id) AND (main.contract_date =
customerinfo.contract_date)) INNER JOIN billing ON
(main.contract_id = billing.contract_id) AND
(main.contract_date = billing.Contract_date);

Service :
SELECT main.contract_id, main.contract_date,
main.Builder, housetype.z1_furn_model AS
housetype_z1_furn_model, housetype.z1_coil_model AS
housetype_z1_coil_model, housetype.z1_ac_model AS
housetype_z1_ac_model, housetype.z2_furn_model AS
housetype_z2_furn_model, housetype.z2_coil_model AS
housetype_z2_coil_model, housetype.z2_ac_model AS
housetype_z2_ac_model, housetype.z3_furn_model AS
housetype_z3_furn_model, housetype.z3_coil_model AS
housetype_z3_coil_model, housetype.z3_ac_model AS
housetype_z3_ac_model, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
service.z1_furn_model AS service_z1_furn_model,
service.z1_serial_furn, service.z1_location_furn,
service.z1_coil_model AS service_z1_coil_model,
service.z1_serial_coil, service.z1_location_coil,
service.z1_ac_model AS service_z1_ac_model,
service.z1_serial_ac, service.z1_location_ac,
service.z2_furn_model AS service_z2_furn_model,
service.z2_serial_furn, service.z2_location_furn,
service.z2_coil_model AS service_z2_coil_model,
service.z2_serial_coil, service.z2_location_coil,
service.z2_ac_model AS service_z2_ac_model,
service.z2_serial_ac, service.z2_location_ac,
service.z3_furn_model AS service_z3_furn_model,
service.z3_serial_furn, service.z3_location_furn,
service.z3_coil_model AS service_z3_coil_model,
service.z3_serial_coil, service.z3_location_coil,
service.z3_ac_model AS service_z3_ac_model,
service.z3_serial_ac, service.z3_location_ac,
service.humid_z1_model, service.humid_z1_install_date,
service.humid_z1_tech, service.humid_z2_model,
service.humid_z2_install_date, service.humid_z2_tech,
service.humid_z3_model, service.humid_z3_install_date,
service.humid_z3_tech, service.elecac_z1_model,
service.elecac_z1_install_date, service.elecac_z1_tech,
service.elecac_z2_model, service.eleac_z2_install_date,
service.elecac_z2_tech, service.elecac_z3_model,
service.elecac_z3_install_date, service.elecac_z3_tech,
service.ptstat_z1_model, service.ptstat_z1_install_date,
service.ptstat_z1_tech, service.ptstat_z2_model,
service.ptstat_z2_install_date, service.ptstat_z2_tech,
service.ptstat_z3_model, service.ptstat_z3_install_date,
service.ptstat_z3_tech, service.zoning_systems_notes,
service.other_options_notes, service.st_check_comp_date,
service.st_check_tech, service.settle_date,
service.provided_by, service.final_inspected_by,
service.final_results, service.notes AS service_notes,
service.service_history, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, main.s_option_1, main.s_option_2,
main.s_option_3, main.s_option_4, main.s_option_5,
main.s_option_6
FROM ((housetype INNER JOIN main ON housetype.house_name
= main.Housetype) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN service ON (main.contract_id = service.contract_id)
AND (main.contract_date = service.contract_date);


Those are the main ones with issues. Let me know what you
think. Thanks
-----Original Message-----
You might want to show us some of your SQL views.

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
J

Jeremy Dove

The reason that they are currently all having the same
style of join is because each one while needing to be
able to be reached from each form are seperate areas.

If you have any ideas on how to make them work better, I
would love to hear it.

Also where/what is the un-matched query wizard?

Thanks
-----Original Message-----
I would remove one table at a time from the query until you find which one
causes the missing records. Then use the un-matched query wizard. I'm would
be surprised if all relationships would/should have a one-to-one
relationship. I suppose this is possible with an un- normalized table
structure.

--
Duane Hookom
MS Access MVP


Here are the SQL Views that I am talking about

Production :
SELECT main.contract_id, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
production.bldg_permit, production.mech_permit,
production.foreman_tick_print_date,
production.permit_submit_date,
production.rough_complete_date,
production.rough_pcwk_amt,
production.indoor_complete_date,
production.indoor_pcwk_amt,
production.rough_inspected_by,
production.rough_inspected_date,
production.rough_inspect_results,
production.wp_hour_rate_1,
production.wp_work_performed_1,
production.wp_work_date_1, production.wp_hour_rate_2,
production.wp_work_performed_2,
production.wp_work_date_2, production.wp_hour_rate_3,
production.wp_work_performed_3,
production.wp_work_date_3, production.extra_po_amt_1,
production.extra_po_date_1, production.extra_po_amt_2,
production.extra_po_date_2, production.extra_po_amount_3,
production.extra_po_date_3,
production.tubing_tick_printed_date,
production.tubing_install_tech,
production.tubing_install_date,
production.tubing_install_amt, production.
[r+g_complete_date], production.pc_worker_name,
production.outdoor_tick_print_date,
production.outdoor_install_tech,
production.outdoor_install_date,
production.outdoor_install_amt, production.notes AS
production_notes, production.bldg_permit_b,
production.mech_permit_b, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, inspection.[inspection reqd]
FROM ((main INNER JOIN inspection ON (main.contract_id =
inspection.contract_id) AND (main.contract_date =
inspection.contract_date)) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN production ON (main.contract_id =
production.contract_id) AND (main.contract_date =
production.contract_date);

Billing :
SELECT main.contract_id, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes, customerinfo.cust_fname_b,
customerinfo.cust_lname_b, customerinfo.c_homephone_b,
customerinfo.c_workphone_b, customerinfo.c_cellphone_b,
customerinfo.c_email_b, billing.rough_bill_date,
billing.indoor_bill_date, billing.outdoor_bill_date,
billing.final_bill_date, billing.options_bill_date,
billing.po_number, billing.po_issue_date,
billing.po_bill_date, billing.po_number_2,
billing.po_issue_date_2, billing.po_bill_date_2,
billing.po_number_3, billing.po_issue_date_3,
billing.po_bill_date_3, billing.po_number_4,
billing.po_issue_date_4, billing.po_bill_date_4
FROM (main INNER JOIN customerinfo ON (main.contract_id =
customerinfo.contract_id) AND (main.contract_date =
customerinfo.contract_date)) INNER JOIN billing ON
(main.contract_id = billing.contract_id) AND
(main.contract_date = billing.Contract_date);

Service :
SELECT main.contract_id, main.contract_date,
main.Builder, housetype.z1_furn_model AS
housetype_z1_furn_model, housetype.z1_coil_model AS
housetype_z1_coil_model, housetype.z1_ac_model AS
housetype_z1_ac_model, housetype.z2_furn_model AS
housetype_z2_furn_model, housetype.z2_coil_model AS
housetype_z2_coil_model, housetype.z2_ac_model AS
housetype_z2_ac_model, housetype.z3_furn_model AS
housetype_z3_furn_model, housetype.z3_coil_model AS
housetype_z3_coil_model, housetype.z3_ac_model AS
housetype_z3_ac_model, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
service.z1_furn_model AS service_z1_furn_model,
service.z1_serial_furn, service.z1_location_furn,
service.z1_coil_model AS service_z1_coil_model,
service.z1_serial_coil, service.z1_location_coil,
service.z1_ac_model AS service_z1_ac_model,
service.z1_serial_ac, service.z1_location_ac,
service.z2_furn_model AS service_z2_furn_model,
service.z2_serial_furn, service.z2_location_furn,
service.z2_coil_model AS service_z2_coil_model,
service.z2_serial_coil, service.z2_location_coil,
service.z2_ac_model AS service_z2_ac_model,
service.z2_serial_ac, service.z2_location_ac,
service.z3_furn_model AS service_z3_furn_model,
service.z3_serial_furn, service.z3_location_furn,
service.z3_coil_model AS service_z3_coil_model,
service.z3_serial_coil, service.z3_location_coil,
service.z3_ac_model AS service_z3_ac_model,
service.z3_serial_ac, service.z3_location_ac,
service.humid_z1_model, service.humid_z1_install_date,
service.humid_z1_tech, service.humid_z2_model,
service.humid_z2_install_date, service.humid_z2_tech,
service.humid_z3_model, service.humid_z3_install_date,
service.humid_z3_tech, service.elecac_z1_model,
service.elecac_z1_install_date, service.elecac_z1_tech,
service.elecac_z2_model, service.eleac_z2_install_date,
service.elecac_z2_tech, service.elecac_z3_model,
service.elecac_z3_install_date, service.elecac_z3_tech,
service.ptstat_z1_model, service.ptstat_z1_install_date,
service.ptstat_z1_tech, service.ptstat_z2_model,
service.ptstat_z2_install_date, service.ptstat_z2_tech,
service.ptstat_z3_model, service.ptstat_z3_install_date,
service.ptstat_z3_tech, service.zoning_systems_notes,
service.other_options_notes, service.st_check_comp_date,
service.st_check_tech, service.settle_date,
service.provided_by, service.final_inspected_by,
service.final_results, service.notes AS service_notes,
service.service_history, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, main.s_option_1, main.s_option_2,
main.s_option_3, main.s_option_4, main.s_option_5,
main.s_option_6
FROM ((housetype INNER JOIN main ON housetype.house_name
= main.Housetype) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN service ON (main.contract_id = service.contract_id)
AND (main.contract_date = service.contract_date);


Those are the main ones with issues. Let me know what you
think. Thanks
-----Original Message-----
You might want to show us some of your SQL views.

--
Duane Hookom
MS Access MVP


I have the joins set correctly. I went and made sure that
they are select all from Main and only matching from the
other table. When I go to the table and look at the view
the record is there and I can do a break out and see the
information on the other tables. So I don't understand
why that small number of records don't show.
-----Original Message-----
You probably don't have a single related record in each
table. You can use
joins that select all records from one table and only
records from the other
table where they match. Double-click your join
Click to expand...
lines
to
view these options.

--
Duane Hookom
MS Access MVP


"Jeremy Dove" <[email protected]>
wrote in message
I have a problem. Here is the setup.

5 tables Main,Customer,Billing,Service,Production.

There is a primary key of Contract_ID and Contract_date
on the main table. The other 4 tables are joined using
the primary key.

If you look at the tables, all 5 tables have 721
records.
All of which are connected.

Here is the issue, when I write a query for main only
requesting contract_id & contract_date it shows 721.

When i write a query requesting contract_id and
contract_date and a field for billing I get 648.
Customer
shows 703. Production is 720. Service is 720.

When I pull fields from main customer and billing it
goes
down to 644.

When I pull fields from main customer and production it
is 703

When I pull fields from main customer and service it is
683.

Does anyone have any idea what is going on?


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
J

Jeremy Dove

I tried the unmatched record query. The records I was
looking at aren't there.

The problem record is still missing. I am still trying to
figure out if I missed something.
-----Original Message-----
I would remove one table at a time from the query until you find which one
causes the missing records. Then use the un-matched query wizard. I'm would
be surprised if all relationships would/should have a one-to-one
relationship. I suppose this is possible with an un- normalized table
structure.

--
Duane Hookom
MS Access MVP


Here are the SQL Views that I am talking about

Production :
SELECT main.contract_id, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
production.bldg_permit, production.mech_permit,
production.foreman_tick_print_date,
production.permit_submit_date,
production.rough_complete_date,
production.rough_pcwk_amt,
production.indoor_complete_date,
production.indoor_pcwk_amt,
production.rough_inspected_by,
production.rough_inspected_date,
production.rough_inspect_results,
production.wp_hour_rate_1,
production.wp_work_performed_1,
production.wp_work_date_1, production.wp_hour_rate_2,
production.wp_work_performed_2,
production.wp_work_date_2, production.wp_hour_rate_3,
production.wp_work_performed_3,
production.wp_work_date_3, production.extra_po_amt_1,
production.extra_po_date_1, production.extra_po_amt_2,
production.extra_po_date_2, production.extra_po_amount_3,
production.extra_po_date_3,
production.tubing_tick_printed_date,
production.tubing_install_tech,
production.tubing_install_date,
production.tubing_install_amt, production.
[r+g_complete_date], production.pc_worker_name,
production.outdoor_tick_print_date,
production.outdoor_install_tech,
production.outdoor_install_date,
production.outdoor_install_amt, production.notes AS
production_notes, production.bldg_permit_b,
production.mech_permit_b, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, inspection.[inspection reqd]
FROM ((main INNER JOIN inspection ON (main.contract_id =
inspection.contract_id) AND (main.contract_date =
inspection.contract_date)) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN production ON (main.contract_id =
production.contract_id) AND (main.contract_date =
production.contract_date);

Billing :
SELECT main.contract_id, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes, customerinfo.cust_fname_b,
customerinfo.cust_lname_b, customerinfo.c_homephone_b,
customerinfo.c_workphone_b, customerinfo.c_cellphone_b,
customerinfo.c_email_b, billing.rough_bill_date,
billing.indoor_bill_date, billing.outdoor_bill_date,
billing.final_bill_date, billing.options_bill_date,
billing.po_number, billing.po_issue_date,
billing.po_bill_date, billing.po_number_2,
billing.po_issue_date_2, billing.po_bill_date_2,
billing.po_number_3, billing.po_issue_date_3,
billing.po_bill_date_3, billing.po_number_4,
billing.po_issue_date_4, billing.po_bill_date_4
FROM (main INNER JOIN customerinfo ON (main.contract_id =
customerinfo.contract_id) AND (main.contract_date =
customerinfo.contract_date)) INNER JOIN billing ON
(main.contract_id = billing.contract_id) AND
(main.contract_date = billing.Contract_date);

Service :
SELECT main.contract_id, main.contract_date,
main.Builder, housetype.z1_furn_model AS
housetype_z1_furn_model, housetype.z1_coil_model AS
housetype_z1_coil_model, housetype.z1_ac_model AS
housetype_z1_ac_model, housetype.z2_furn_model AS
housetype_z2_furn_model, housetype.z2_coil_model AS
housetype_z2_coil_model, housetype.z2_ac_model AS
housetype_z2_ac_model, housetype.z3_furn_model AS
housetype_z3_furn_model, housetype.z3_coil_model AS
housetype_z3_coil_model, housetype.z3_ac_model AS
housetype_z3_ac_model, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
service.z1_furn_model AS service_z1_furn_model,
service.z1_serial_furn, service.z1_location_furn,
service.z1_coil_model AS service_z1_coil_model,
service.z1_serial_coil, service.z1_location_coil,
service.z1_ac_model AS service_z1_ac_model,
service.z1_serial_ac, service.z1_location_ac,
service.z2_furn_model AS service_z2_furn_model,
service.z2_serial_furn, service.z2_location_furn,
service.z2_coil_model AS service_z2_coil_model,
service.z2_serial_coil, service.z2_location_coil,
service.z2_ac_model AS service_z2_ac_model,
service.z2_serial_ac, service.z2_location_ac,
service.z3_furn_model AS service_z3_furn_model,
service.z3_serial_furn, service.z3_location_furn,
service.z3_coil_model AS service_z3_coil_model,
service.z3_serial_coil, service.z3_location_coil,
service.z3_ac_model AS service_z3_ac_model,
service.z3_serial_ac, service.z3_location_ac,
service.humid_z1_model, service.humid_z1_install_date,
service.humid_z1_tech, service.humid_z2_model,
service.humid_z2_install_date, service.humid_z2_tech,
service.humid_z3_model, service.humid_z3_install_date,
service.humid_z3_tech, service.elecac_z1_model,
service.elecac_z1_install_date, service.elecac_z1_tech,
service.elecac_z2_model, service.eleac_z2_install_date,
service.elecac_z2_tech, service.elecac_z3_model,
service.elecac_z3_install_date, service.elecac_z3_tech,
service.ptstat_z1_model, service.ptstat_z1_install_date,
service.ptstat_z1_tech, service.ptstat_z2_model,
service.ptstat_z2_install_date, service.ptstat_z2_tech,
service.ptstat_z3_model, service.ptstat_z3_install_date,
service.ptstat_z3_tech, service.zoning_systems_notes,
service.other_options_notes, service.st_check_comp_date,
service.st_check_tech, service.settle_date,
service.provided_by, service.final_inspected_by,
service.final_results, service.notes AS service_notes,
service.service_history, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, main.s_option_1, main.s_option_2,
main.s_option_3, main.s_option_4, main.s_option_5,
main.s_option_6
FROM ((housetype INNER JOIN main ON housetype.house_name
= main.Housetype) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN service ON (main.contract_id = service.contract_id)
AND (main.contract_date = service.contract_date);


Those are the main ones with issues. Let me know what you
think. Thanks
-----Original Message-----
You might want to show us some of your SQL views.

--
Duane Hookom
MS Access MVP


I have the joins set correctly. I went and made sure that
they are select all from Main and only matching from the
other table. When I go to the table and look at the view
the record is there and I can do a break out and see the
information on the other tables. So I don't understand
why that small number of records don't show.
-----Original Message-----
You probably don't have a single related record in each
table. You can use
joins that select all records from one table and only
records from the other
table where they match. Double-click your join
Click to expand...
lines
to
view these options.

--
Duane Hookom
MS Access MVP


"Jeremy Dove" <[email protected]>
wrote in message
I have a problem. Here is the setup.

5 tables Main,Customer,Billing,Service,Production.

There is a primary key of Contract_ID and Contract_date
on the main table. The other 4 tables are joined using
the primary key.

If you look at the tables, all 5 tables have 721
records.
All of which are connected.

Here is the issue, when I write a query for main only
requesting contract_id & contract_date it shows 721.

When i write a query requesting contract_id and
contract_date and a field for billing I get 648.
Customer
shows 703. Production is 720. Service is 720.

When I pull fields from main customer and billing it
goes
down to 644.

When I pull fields from main customer and production it
is 703

When I pull fields from main customer and service it is
683.

Does anyone have any idea what is going on?


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
J

Jeremy Dove

Thanks,
I finally found the problem. There was an error in one of
the spelling of a piece of data in one of the connecting
fields.

I have resolved all the missing records problems. It
wasn't my structure it was a error in data.

Thank you so much for the help, it was invaluable.
-----Original Message-----
I would remove one table at a time from the query until you find which one
causes the missing records. Then use the un-matched query wizard. I'm would
be surprised if all relationships would/should have a one-to-one
relationship. I suppose this is possible with an un- normalized table
structure.

--
Duane Hookom
MS Access MVP


Here are the SQL Views that I am talking about

Production :
SELECT main.contract_id, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
production.bldg_permit, production.mech_permit,
production.foreman_tick_print_date,
production.permit_submit_date,
production.rough_complete_date,
production.rough_pcwk_amt,
production.indoor_complete_date,
production.indoor_pcwk_amt,
production.rough_inspected_by,
production.rough_inspected_date,
production.rough_inspect_results,
production.wp_hour_rate_1,
production.wp_work_performed_1,
production.wp_work_date_1, production.wp_hour_rate_2,
production.wp_work_performed_2,
production.wp_work_date_2, production.wp_hour_rate_3,
production.wp_work_performed_3,
production.wp_work_date_3, production.extra_po_amt_1,
production.extra_po_date_1, production.extra_po_amt_2,
production.extra_po_date_2, production.extra_po_amount_3,
production.extra_po_date_3,
production.tubing_tick_printed_date,
production.tubing_install_tech,
production.tubing_install_date,
production.tubing_install_amt, production.
[r+g_complete_date], production.pc_worker_name,
production.outdoor_tick_print_date,
production.outdoor_install_tech,
production.outdoor_install_date,
production.outdoor_install_amt, production.notes AS
production_notes, production.bldg_permit_b,
production.mech_permit_b, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, inspection.[inspection reqd]
FROM ((main INNER JOIN inspection ON (main.contract_id =
inspection.contract_id) AND (main.contract_date =
inspection.contract_date)) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN production ON (main.contract_id =
production.contract_id) AND (main.contract_date =
production.contract_date);

Billing :
SELECT main.contract_id, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes, customerinfo.cust_fname_b,
customerinfo.cust_lname_b, customerinfo.c_homephone_b,
customerinfo.c_workphone_b, customerinfo.c_cellphone_b,
customerinfo.c_email_b, billing.rough_bill_date,
billing.indoor_bill_date, billing.outdoor_bill_date,
billing.final_bill_date, billing.options_bill_date,
billing.po_number, billing.po_issue_date,
billing.po_bill_date, billing.po_number_2,
billing.po_issue_date_2, billing.po_bill_date_2,
billing.po_number_3, billing.po_issue_date_3,
billing.po_bill_date_3, billing.po_number_4,
billing.po_issue_date_4, billing.po_bill_date_4
FROM (main INNER JOIN customerinfo ON (main.contract_id =
customerinfo.contract_id) AND (main.contract_date =
customerinfo.contract_date)) INNER JOIN billing ON
(main.contract_id = billing.contract_id) AND
(main.contract_date = billing.Contract_date);

Service :
SELECT main.contract_id, main.contract_date,
main.Builder, housetype.z1_furn_model AS
housetype_z1_furn_model, housetype.z1_coil_model AS
housetype_z1_coil_model, housetype.z1_ac_model AS
housetype_z1_ac_model, housetype.z2_furn_model AS
housetype_z2_furn_model, housetype.z2_coil_model AS
housetype_z2_coil_model, housetype.z2_ac_model AS
housetype_z2_ac_model, housetype.z3_furn_model AS
housetype_z3_furn_model, housetype.z3_coil_model AS
housetype_z3_coil_model, housetype.z3_ac_model AS
housetype_z3_ac_model, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
service.z1_furn_model AS service_z1_furn_model,
service.z1_serial_furn, service.z1_location_furn,
service.z1_coil_model AS service_z1_coil_model,
service.z1_serial_coil, service.z1_location_coil,
service.z1_ac_model AS service_z1_ac_model,
service.z1_serial_ac, service.z1_location_ac,
service.z2_furn_model AS service_z2_furn_model,
service.z2_serial_furn, service.z2_location_furn,
service.z2_coil_model AS service_z2_coil_model,
service.z2_serial_coil, service.z2_location_coil,
service.z2_ac_model AS service_z2_ac_model,
service.z2_serial_ac, service.z2_location_ac,
service.z3_furn_model AS service_z3_furn_model,
service.z3_serial_furn, service.z3_location_furn,
service.z3_coil_model AS service_z3_coil_model,
service.z3_serial_coil, service.z3_location_coil,
service.z3_ac_model AS service_z3_ac_model,
service.z3_serial_ac, service.z3_location_ac,
service.humid_z1_model, service.humid_z1_install_date,
service.humid_z1_tech, service.humid_z2_model,
service.humid_z2_install_date, service.humid_z2_tech,
service.humid_z3_model, service.humid_z3_install_date,
service.humid_z3_tech, service.elecac_z1_model,
service.elecac_z1_install_date, service.elecac_z1_tech,
service.elecac_z2_model, service.eleac_z2_install_date,
service.elecac_z2_tech, service.elecac_z3_model,
service.elecac_z3_install_date, service.elecac_z3_tech,
service.ptstat_z1_model, service.ptstat_z1_install_date,
service.ptstat_z1_tech, service.ptstat_z2_model,
service.ptstat_z2_install_date, service.ptstat_z2_tech,
service.ptstat_z3_model, service.ptstat_z3_install_date,
service.ptstat_z3_tech, service.zoning_systems_notes,
service.other_options_notes, service.st_check_comp_date,
service.st_check_tech, service.settle_date,
service.provided_by, service.final_inspected_by,
service.final_results, service.notes AS service_notes,
service.service_history, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, main.s_option_1, main.s_option_2,
main.s_option_3, main.s_option_4, main.s_option_5,
main.s_option_6
FROM ((housetype INNER JOIN main ON housetype.house_name
= main.Housetype) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN service ON (main.contract_id = service.contract_id)
AND (main.contract_date = service.contract_date);


Those are the main ones with issues. Let me know what you
think. Thanks
-----Original Message-----
You might want to show us some of your SQL views.

--
Duane Hookom
MS Access MVP


I have the joins set correctly. I went and made sure that
they are select all from Main and only matching from the
other table. When I go to the table and look at the view
the record is there and I can do a break out and see the
information on the other tables. So I don't understand
why that small number of records don't show.
-----Original Message-----
You probably don't have a single related record in each
table. You can use
joins that select all records from one table and only
records from the other
table where they match. Double-click your join
Click to expand...
lines
to
view these options.

--
Duane Hookom
MS Access MVP


"Jeremy Dove" <[email protected]>
wrote in message
I have a problem. Here is the setup.

5 tables Main,Customer,Billing,Service,Production.

There is a primary key of Contract_ID and Contract_date
on the main table. The other 4 tables are joined using
the primary key.

If you look at the tables, all 5 tables have 721
records.
All of which are connected.

Here is the issue, when I write a query for main only
requesting contract_id & contract_date it shows 721.

When i write a query requesting contract_id and
contract_date and a field for billing I get 648.
Customer
shows 703. Production is 720. Service is 720.

When I pull fields from main customer and billing it
goes
down to 644.

When I pull fields from main customer and production it
is 703

When I pull fields from main customer and service it is
683.

Does anyone have any idea what is going on?


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Apparently Contract_ID is not an autonumber field. I never join tables where
one of the fields is not an Autonumber primary key. While this doesn't work
for everyone, I have never had a problem or misspelling.

--
Duane Hookom
MS Access MVP


Jeremy Dove said:
Thanks,
I finally found the problem. There was an error in one of
the spelling of a piece of data in one of the connecting
fields.

I have resolved all the missing records problems. It
wasn't my structure it was a error in data.

Thank you so much for the help, it was invaluable.
-----Original Message-----
I would remove one table at a time from the query until you find which one
causes the missing records. Then use the un-matched query wizard. I'm would
be surprised if all relationships would/should have a one-to-one
relationship. I suppose this is possible with an un- normalized table
structure.

--
Duane Hookom
MS Access MVP


Here are the SQL Views that I am talking about

Production :
SELECT main.contract_id, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
production.bldg_permit, production.mech_permit,
production.foreman_tick_print_date,
production.permit_submit_date,
production.rough_complete_date,
production.rough_pcwk_amt,
production.indoor_complete_date,
production.indoor_pcwk_amt,
production.rough_inspected_by,
production.rough_inspected_date,
production.rough_inspect_results,
production.wp_hour_rate_1,
production.wp_work_performed_1,
production.wp_work_date_1, production.wp_hour_rate_2,
production.wp_work_performed_2,
production.wp_work_date_2, production.wp_hour_rate_3,
production.wp_work_performed_3,
production.wp_work_date_3, production.extra_po_amt_1,
production.extra_po_date_1, production.extra_po_amt_2,
production.extra_po_date_2, production.extra_po_amount_3,
production.extra_po_date_3,
production.tubing_tick_printed_date,
production.tubing_install_tech,
production.tubing_install_date,
production.tubing_install_amt, production.
[r+g_complete_date], production.pc_worker_name,
production.outdoor_tick_print_date,
production.outdoor_install_tech,
production.outdoor_install_date,
production.outdoor_install_amt, production.notes AS
production_notes, production.bldg_permit_b,
production.mech_permit_b, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, inspection.[inspection reqd]
FROM ((main INNER JOIN inspection ON (main.contract_id =
inspection.contract_id) AND (main.contract_date =
inspection.contract_date)) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN production ON (main.contract_id =
production.contract_id) AND (main.contract_date =
production.contract_date);

Billing :
SELECT main.contract_id, main.contract_date,
main.Builder, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes, customerinfo.cust_fname_b,
customerinfo.cust_lname_b, customerinfo.c_homephone_b,
customerinfo.c_workphone_b, customerinfo.c_cellphone_b,
customerinfo.c_email_b, billing.rough_bill_date,
billing.indoor_bill_date, billing.outdoor_bill_date,
billing.final_bill_date, billing.options_bill_date,
billing.po_number, billing.po_issue_date,
billing.po_bill_date, billing.po_number_2,
billing.po_issue_date_2, billing.po_bill_date_2,
billing.po_number_3, billing.po_issue_date_3,
billing.po_bill_date_3, billing.po_number_4,
billing.po_issue_date_4, billing.po_bill_date_4
FROM (main INNER JOIN customerinfo ON (main.contract_id =
customerinfo.contract_id) AND (main.contract_date =
customerinfo.contract_date)) INNER JOIN billing ON
(main.contract_id = billing.contract_id) AND
(main.contract_date = billing.Contract_date);

Service :
SELECT main.contract_id, main.contract_date,
main.Builder, housetype.z1_furn_model AS
housetype_z1_furn_model, housetype.z1_coil_model AS
housetype_z1_coil_model, housetype.z1_ac_model AS
housetype_z1_ac_model, housetype.z2_furn_model AS
housetype_z2_furn_model, housetype.z2_coil_model AS
housetype_z2_coil_model, housetype.z2_ac_model AS
housetype_z2_ac_model, housetype.z3_furn_model AS
housetype_z3_furn_model, housetype.z3_coil_model AS
housetype_z3_coil_model, housetype.z3_ac_model AS
housetype_z3_ac_model, customerinfo.cust_fname,
customerinfo.cust_lname, customerinfo.c_address1,
customerinfo.c_address2, customerinfo.c_city,
customerinfo.c_state, customerinfo.c_zip,
customerinfo.c_homephone, customerinfo.c_workphone,
customerinfo.c_cellphone, customerinfo.c_email,
customerinfo.notes AS customerinfo_notes,
customerinfo.cust_fname_b, customerinfo.cust_lname_b,
customerinfo.c_homephone_b, customerinfo.c_workphone_b,
customerinfo.c_cellphone_b, customerinfo.c_email_b,
service.z1_furn_model AS service_z1_furn_model,
service.z1_serial_furn, service.z1_location_furn,
service.z1_coil_model AS service_z1_coil_model,
service.z1_serial_coil, service.z1_location_coil,
service.z1_ac_model AS service_z1_ac_model,
service.z1_serial_ac, service.z1_location_ac,
service.z2_furn_model AS service_z2_furn_model,
service.z2_serial_furn, service.z2_location_furn,
service.z2_coil_model AS service_z2_coil_model,
service.z2_serial_coil, service.z2_location_coil,
service.z2_ac_model AS service_z2_ac_model,
service.z2_serial_ac, service.z2_location_ac,
service.z3_furn_model AS service_z3_furn_model,
service.z3_serial_furn, service.z3_location_furn,
service.z3_coil_model AS service_z3_coil_model,
service.z3_serial_coil, service.z3_location_coil,
service.z3_ac_model AS service_z3_ac_model,
service.z3_serial_ac, service.z3_location_ac,
service.humid_z1_model, service.humid_z1_install_date,
service.humid_z1_tech, service.humid_z2_model,
service.humid_z2_install_date, service.humid_z2_tech,
service.humid_z3_model, service.humid_z3_install_date,
service.humid_z3_tech, service.elecac_z1_model,
service.elecac_z1_install_date, service.elecac_z1_tech,
service.elecac_z2_model, service.eleac_z2_install_date,
service.elecac_z2_tech, service.elecac_z3_model,
service.elecac_z3_install_date, service.elecac_z3_tech,
service.ptstat_z1_model, service.ptstat_z1_install_date,
service.ptstat_z1_tech, service.ptstat_z2_model,
service.ptstat_z2_install_date, service.ptstat_z2_tech,
service.ptstat_z3_model, service.ptstat_z3_install_date,
service.ptstat_z3_tech, service.zoning_systems_notes,
service.other_options_notes, service.st_check_comp_date,
service.st_check_tech, service.settle_date,
service.provided_by, service.final_inspected_by,
service.final_results, service.notes AS service_notes,
service.service_history, main.Subdivision, main.Section,
main.Housetype, main.Lot, main.Lotb, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, main.s_option_1, main.s_option_2,
main.s_option_3, main.s_option_4, main.s_option_5,
main.s_option_6
FROM ((housetype INNER JOIN main ON housetype.house_name
= main.Housetype) INNER JOIN customerinfo ON
(main.contract_id = customerinfo.contract_id) AND
(main.contract_date = customerinfo.contract_date)) INNER
JOIN service ON (main.contract_id = service.contract_id)
AND (main.contract_date = service.contract_date);


Those are the main ones with issues. Let me know what you
think. Thanks
-----Original Message-----
You might want to show us some of your SQL views.

--
Duane Hookom
MS Access MVP


"Jeremy Dove" <[email protected]>
wrote in message
I have the joins set correctly. I went and made sure
that
they are select all from Main and only matching from
the
other table. When I go to the table and look at the
view
the record is there and I can do a break out and see
the
information on the other tables. So I don't understand
why that small number of records don't show.
-----Original Message-----
You probably don't have a single related record in
each
table. You can use
joins that select all records from one table and only
records from the other
table where they match. Double-click your join lines
to
view these options.

--
Duane Hookom
MS Access MVP


"Jeremy Dove" <[email protected]>
wrote in message
I have a problem. Here is the setup.

5 tables Main,Customer,Billing,Service,Production.

There is a primary key of Contract_ID and
Contract_date
on the main table. The other 4 tables are joined
using
the primary key.

If you look at the tables, all 5 tables have 721
records.
All of which are connected.

Here is the issue, when I write a query for main
only
requesting contract_id & contract_date it shows 721.

When i write a query requesting contract_id and
contract_date and a field for billing I get 648.
Customer
shows 703. Production is 720. Service is 720.

When I pull fields from main customer and billing it
goes
down to 644.

When I pull fields from main customer and
production it
is 703

When I pull fields from main customer and service
it is
683.

Does anyone have any idea what is going on?


.



.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Crosstab Query to Existing Table 0
QBF & Query Malfunction 0
Concatenating 2 Fields 1
can't find the right query 0
query to filter by current record id? 4
Query to select values after a certain date 0
If statment as condition in query 5
Non-updateable query issue 1

Top