Records Do Not Show In "Committed" Form

B

Bob Bower

Please,

I use Win XP Pro, Access 2002. I am not an experienced Access guy.



I have two forms. “Master Customer List” and “Committed Exhibitors”.

My problem: When I add a new record to the “Master Customer List”, and the
customer becomes committed to space, it does not show up on the “Committed
Exhibitors form as it has in the past. These new records appear in the
query, and in the table, but not in the forms. If I remove the “Exhibit
Data” table from the “Master Customer List” query, the records show. I can
manage without the “Exhibit Data” table in the “Master Customer List” query,
but not in the “Committed Exhibitors” query.

The record source for the “Master Customer List” is a query with two tables.
“Contact Data”, and "Exhibit Data”.

The record source for the Committed Exhibitors form is a query where the
criteria is “Committed”. This query has three tables. “Contact Data”,
“Exhibit Data”, and “Payment Data”.

I don’t know enough about Access to ask a smart question, but I hope the
fine people in this group can help me toward a solution.

Thank you for your help.



Bob
 
J

Jeff Boyce

Bob

Does your data design include a relationship between Master and Committed
(i.e., both include a "CustomerID" field)? What are the underlying queries
you use to populate the Master and Committed forms -- please post the SQL
statements.

More info, please

Jeff Boyce
<Access MVP>
 
B

Bob Bower

Thank you for your help Jeff,
These things seem huge. I hope I am not violating any rules of this group by
posting this.

The SQL statement for the Master Contact form query:
SELECT [2004 Exhibitor Contact Data].ContactID, [2004 Exhibitor Contact
Data].[Date Entered], [2004 Exhibitor Contact Data].First, [2004 Exhibitor
Contact Data].[Last Name], [2004 Exhibitor Contact Data].Company, [2004
Exhibitor Contact Data].ContactTypeID, [2004 Exhibitor Contact Data].Title,
[2004 Exhibitor Contact Data].Phone, [2004 Exhibitor Contact Data].[Phone
Extension], [2004 Exhibitor Contact Data].Fax, [2004 Exhibitor Contact
Data].[Mob Phone], [2004 Exhibitor Contact Data].Pager, [2004 Exhibitor
Contact Data].Address, [2004 Exhibitor Contact Data].City, [2004 Exhibitor
Contact Data].State, [2004 Exhibitor Contact Data].Country, [2004 Exhibitor
Contact Data].Zip, [2004 Exhibitor Contact Data].Email, [2004 Exhibitor
Contact Data].Website, [2004 Exhibitor Contact Data].[More Info], [2004
Exhibitor Contact Data].[Custom 4], [2004 Exhibitor Contact Data].Note,
[2004 Exhibitor Contact Data].[04 Categroy], [2004 Exhibitor Contact
Data].Category, [2004 Exhibitor Contact Data].[Mkt Segment], [2004 Exhibitor
Contact Data].[Business Type], [2004 Exhibitor Contact Data].[Mktg Pkg
Sent], [2004 Exhibitor Contact Data].[Alt Contact 1 Last Name], [2004
Exhibitor Contact Data].[Alt Contact 1 First], [2004 Exhibitor Contact
Data].[Alt Contact 1 Title], [2004 Exhibitor Contact Data].[Alt Contact 1
Phone], [2004 Exhibitor Contact Data].[Phone Extension1], [2004 Exhibitor
Contact Data].[Alt Contact 1 Email], [2004 Exhibitor Contact Data].[Alt
Contact1 Address], [2004 Exhibitor Contact Data].[Alt Contact1 City], [2004
Exhibitor Contact Data].[Alt Contact1 State], [2004 Exhibitor Contact
Data].[Alt Contact1 Zip], [2004 Exhibitor Contact Data].[Alt Contact 2 Last
Name], [2004 Exhibitor Contact Data].[Alt Contact 2 First], [2004 Exhibitor
Contact Data].[Alt Contact 2 Title], [2004 Exhibitor Contact Data].[Alt
Contact 2 Phone], [2004 Exhibitor Contact Data].[Phone Extension2], [2004
Exhibitor Contact Data].[Alt Contact 2 Email], [2004 Exhibitor Contact
Data].[Alt Contact2 Address], [2004 Exhibitor Contact Data].[Alt Contact2
City], [2004 Exhibitor Contact Data].[Alt Contact2 State], [2004 Exhibitor
Contact Data].[Alt Contact2 Zip], [2004 Exhibitor Contact Data].[Alt Contact
3 Last Name], [2004 Exhibitor Contact Data].[Alt Contact 3 First], [2004
Exhibitor Contact Data].[Alt Contact 3 Title], [2004 Exhibitor Contact
Data].[Alt Contact 3 Phone], [2004 Exhibitor Contact Data].[Phone
Extension3], [2004 Exhibitor Contact Data].[Alt Contact 3 Email], [2004
Exhibitor Contact Data].[Alt Contact3 Address], [2004 Exhibitor Contact
Data].[Alt Contact3 City], [2004 Exhibitor Contact Data].[Alt Contact3
State], [2004 Exhibitor Contact Data].[Alt Contact3 Zip], [2004 Exhibitor
Contact Data].[Alt Contact 4 Last Name], [2004 Exhibitor Contact Data].[Alt
Contact 4 First], [2004 Exhibitor Contact Data].[Alt Contact 4 Title], [2004
Exhibitor Contact Data].[Alt Contact 4 Phone], [2004 Exhibitor Contact
Data].[Alt Contact 4 Email], [2004 Exhibitor Contact Data].[Alt Contact4
Address], [2004 Exhibitor Contact Data].[Alt Contact4 City], [2004 Exhibitor
Contact Data].[Alt Contact4 State], [2004 Exhibitor Contact Data].[Alt
Contact4 Zip], [2004 Exhibit Space Data].[2000 Space], [2004 Exhibit Space
Data].[2001Space], [2004 Exhibit Space Data].[2002 Space], [2004 Exhibit
Space Data].[2003 Space], [2004 Exhibit Space Data].[2004 Space], [00 Show
Points]+[01 Show Points]+[02 Show Points]+[03 Show Points]+[04 Show Points]
AS [Current Points], [00 Show Points]+[01 Show Points]+[02 Show Points]+[03
Show Points] AS [2003 Points Total], [2004 Exhibit Space Data].[00 Show
Points], [2004 Exhibit Space Data].[01 Show Points], [2004 Exhibit Space
Data].[02 Show Points], [2004 Exhibit Space Data].[03 Show Points], [2004
Exhibit Space Data].[04 Show Points]
FROM [2004 Exhibitor Contact Data] INNER JOIN [2004 Exhibit Space Data] ON
[2004 Exhibitor Contact Data].ContactID = [2004 Exhibit Space
Data].ExhibitID
ORDER BY [2004 Exhibitor Contact Data].Company;

The SQL Statement for the Committed form query:
SELECT ([Deposit Amount 04]+[2nd Payment 04]+[3rd Payment 04]+[4th Payment
04]+[Balance Paid 04])-[Commission Chargeback 1 Amt 04] AS ROA, ([Net
Sale]-[ROA]) AS [New Balance], [Bulk Space Amount]+[Other Unit
Amount]+[Corner 4 Amount]+[Vehicle Display Amount]+[Std 4 Amount]-[Discount
Amount 04] AS [Net Sale], ([Net Sale]*0.5) AS [Deposit Due], [Bulk Space
04]*1.5 AS [Bulk Space Amount], [Other Unit04]*450 AS [Other Unit Amount],
[Corner04]*750 AS [Corner 4 Amount], [Vehicle04]*300 AS [Vehicle Display
Amount], [Std04]*650 AS [Std 4 Amount], ([00 Show Points]+[01 Show
Points]+[02 Show Points])+[03 Show Points] AS [Seniority Point Total], [00
Show Points]+[01 Show Points]+[02 Show Points]+[03 Show Points]+[04 Show
Points] AS [Current Points], [2004 Exhibitor Contact Data].ContactID, [2004
Exhibitor Contact Data].[Date Entered], [2004 Exhibitor Contact Data].First,
[2004 Exhibitor Contact Data].[Last Name], [2004 Exhibitor Contact
Data].Company, [2004 Exhibitor Contact Data].ContactTypeID, [2004 Exhibitor
Contact Data].Title, [2004 Exhibitor Contact Data].Phone, [2004 Exhibitor
Contact Data].[Phone Extension], [2004 Exhibitor Contact Data].Fax, [2004
Exhibitor Contact Data].[Mob Phone], [2004 Exhibitor Contact Data].Pager,
[2004 Exhibitor Contact Data].Address, [2004 Exhibitor Contact Data].City,
[2004 Exhibitor Contact Data].State, [2004 Exhibitor Contact Data].Country,
[2004 Exhibitor Contact Data].Zip, [2004 Exhibitor Contact Data].Email,
[2004 Exhibitor Contact Data].Website, [2004 Exhibitor Contact Data].[More
Info], [2004 Exhibitor Contact Data].[Custom 4], [2004 Exhibitor Contact
Data].Note, [2004 Exhibitor Contact Data].[04 Categroy], [2004 Exhibitor
Contact Data].Category, [2004 Exhibitor Contact Data].[Mkt Segment], [2004
Exhibitor Contact Data].[Business Type], [2004 Exhibitor Contact Data].[Mktg
Pkg Sent], [2004 Exhibitor Contact Data].[Alt Contact 1 Last Name], [2004
Exhibitor Contact Data].[Alt Contact 1 First], [2004 Exhibitor Contact
Data].[Alt Contact 1 Title], [2004 Exhibitor Contact Data].[Alt Contact 1
Phone], [2004 Exhibitor Contact Data].[Phone Extension1], [2004 Exhibitor
Contact Data].[Alt Contact 1 Email], [2004 Exhibitor Contact Data].[Alt
Contact1 Address], [2004 Exhibitor Contact Data].[Alt Contact1 City], [2004
Exhibitor Contact Data].[Alt Contact1 State], [2004 Exhibitor Contact
Data].[Alt Contact1 Zip], [2004 Exhibitor Contact Data].[Alt Contact 2 Last
Name], [2004 Exhibitor Contact Data].[Alt Contact 2 First], [2004 Exhibitor
Contact Data].[Alt Contact 2 Title], [2004 Exhibitor Contact Data].[Alt
Contact 2 Phone], [2004 Exhibitor Contact Data].[Phone Extension2], [2004
Exhibitor Contact Data].[Alt Contact 2 Email], [2004 Exhibitor Contact
Data].[Alt Contact2 Address], [2004 Exhibitor Contact Data].[Alt Contact2
City], [2004 Exhibitor Contact Data].[Alt Contact2 State], [2004 Exhibitor
Contact Data].[Alt Contact2 Zip], [2004 Exhibitor Contact Data].[Alt Contact
3 Last Name], [2004 Exhibitor Contact Data].[Alt Contact 3 First], [2004
Exhibitor Contact Data].[Alt Contact 3 Title], [2004 Exhibitor Contact
Data].[Alt Contact 3 Phone], [2004 Exhibitor Contact Data].[Phone
Extension3], [2004 Exhibitor Contact Data].[Alt Contact 3 Email], [2004
Exhibitor Contact Data].[Alt Contact3 Address], [2004 Exhibitor Contact
Data].[Alt Contact3 City], [2004 Exhibitor Contact Data].[Alt Contact3
State], [2004 Exhibitor Contact Data].[Alt Contact3 Zip], [2004 Exhibitor
Contact Data].[Alt Contact 4 Last Name], [2004 Exhibitor Contact Data].[Alt
Contact 4 First], [2004 Exhibitor Contact Data].[Alt Contact 4 Title], [2004
Exhibitor Contact Data].[Alt Contact 4 Phone], [2004 Exhibitor Contact
Data].[Alt Contact 4 Email], [2004 Exhibitor Contact Data].[Alt Contact4
Address], [2004 Exhibitor Contact Data].[Alt Contact4 City], [2004 Exhibitor
Contact Data].[Alt Contact4 State], [2004 Exhibitor Contact Data].[Alt
Contact4 Zip], [2004 Exhibit Space Data].ExhibitID, [2004 Exhibit Space
Data].[2004 Space], [2004 Exhibit Space Data].[2004 Vehicle Space Numbers],
[2004 Exhibit Space Data].[00 Show Points], [2004 Exhibit Space Data].[01
Show Points], [2004 Exhibit Space Data].[02 Show Points], [2004 Exhibit
Space Data].[03 Show Points], [2004 Exhibit Space Data].[04 Show Points],
[2004 Exhibit Space Data].Std04, [2004 Exhibit Space Data].Corner04, [2004
Exhibit Space Data].Vehicle04, [2004 Exhibit Space Data].[Other Unit04],
[2004 Exhibit Space Data].[Reservation Date 04], [2004 Exhibit Space
Data].[Bulk Space 04], [2004 Exhibit Space Data].[Bulk Length 04], [2004
Exhibit Space Data].[Bulk Width 04], [2004 Exhibit Space Data].[Aisle Name
04], [2004 Exhibit Space Data].[Aisle Address 04], [2004 Exhibit Space
Data].[2nd Aisle Name 04], [2004 Exhibit Space Data].[2nd Aisle Address 04],
[2004 Exhibit Space Data].[Exhbit Shape 04], [2004 Exhibit Space
Data].[10X10 04], [2004 Exhibit Space Data].[10X20 04], [2004 Exhibit Space
Data].[10X30 04], [2004 Exhibit Space Data].[10X40 04], [2004 Exhibit Space
Data].[10X50 04], [2004 Exhibit Space Data].[20X20 04], [2004 Exhibit Space
Data].[20X30 04], [2004 Exhibit Space Data].[20X40 04], [2004 Exhibit Space
Data].[20X50 04], [2004 Exhibit Space Data].[20X60 04], [2004 Exhibit Space
Data].[2000 Space], [2004 Exhibit Space Data].[2001Space], [2004 Exhibit
Space Data].[2002 Space], [2004 Exhibit Space Data].[2003 Space], [2004
Exhibitor Payment Data].PaymentID, [2004 Exhibitor Payment Data].[Discount
Amount 04], [2004 Exhibitor Payment Data].[Deposit Date 04], [2004 Exhibitor
Payment Data].[Deposit Amount 04], [2004 Exhibitor Payment Data].[Balance
Due 04], [2004 Exhibitor Payment Data].[2nd Payment 04], [2004 Exhibitor
Payment Data].[3rd Payment 04], [2004 Exhibitor Payment Data].[4th Payment
04], [2004 Exhibitor Payment Data].[Balance Paid 04], [2004 Exhibitor
Payment Data].[Paid In Full Date 04], [2004 Exhibitor Payment Data].[Refunds
04], [2004 Exhibitor Payment Data].[Commission Payment 1 Date 04], [2004
Exhibitor Payment Data].[Commission Payment 1 Amt 04], [2004 Exhibitor
Payment Data].[Commission Payment 2 Date 04], [2004 Exhibitor Payment
Data].[Commission Payment 2 Amt 04], [2004 Exhibitor Payment
Data].[Commission Payment 3 Date 04], [2004 Exhibitor Payment
Data].[Commission Payment 3 Amt 04], [2004 Exhibitor Payment
Data].[Commission Charge Back 1 Date 04], [2004 Exhibitor Payment
Data].[Commission Chargeback 1 Amt 04], [2004 Exhibitor Payment
Data].[Commission Chargeback 2 Date 04], [2004 Exhibitor Payment
Data].[Commission Payment 2 Amt 04], [2004 Exhibitor Payment
Data].[Commission Payment 3 Date 04], [2004 Exhibitor Payment
Data].[Commission Payment 3 Amt 04], [2004 Exhibitor Payment
Data].[Commission Charge Back 1 Date 04], [2004 Exhibitor Payment
Data].[Commission Chargeback 1 Amt 04], [2004 Exhibitor Payment
Data].[Commission Chargeback 2 Date 04], [2004 Exhibitor Payment
Data].[Commission Chargeback 2 Amt 04], [2004 Exhibitor Payment
Data].[Commission Paid 1 04], [2004 Exhibitor Payment Data].[Commission Paid
2 04], [2004 Exhibitor Payment Data].[Commission Paid 3 04]
FROM [2004 Exhibitor Payment Data] INNER JOIN ([2004 Exhibitor Contact Data]
INNER JOIN [2004 Exhibit Space Data] ON [2004 Exhibitor Contact
Data].ContactID = [2004 Exhibit Space Data].ExhibitID) ON [2004 Exhibitor
Payment Data].PaymentID = [2004 Exhibit Space Data].ExhibitID
WHERE ((([2004 Exhibitor Contact Data].[04 Categroy])="04 Committed To
Space"));
 
J

Jeff Boyce

Bob

A quick look over your SQL statement revealed that you have (potentially)
many contacts, and many years, but you're putting all that info into one
"wide" table. If you were working in Excel, this would be the way you would
organize your data. Don't "commit spreadsheet" on Access!

Take a look at the HELP topic of normalization. You will find your querying
and forms work should get easier if you spend a bit more time on the table
structure.
 

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

Trying to create a report that will list unmatched records by Ticket Primary Key 0
Copy previous record into form field 0
Help creating a search form in Access XP 0
Form Will Not Save Data Upon Closing 12
Drop down lists in seperate columns 5
subfrom - many to many relationship 0
Duplicate hashtags in OneNote 0
Adding data to MS Access from csv file 0

Top