Help with Update Query

[Yula]

I have a form which is based on a query that is joining several tables all
related by a primary key "Tracking Number". I am using that query "Test2" as
the control source for my form " form2". I got the form to add records and
its working just fine, when I go into the query, those records that I added
are not showing up in that Test2. In fact, i can't even add a record in the
datasheet view of the query. I am posting the SQL for Test2 and I would
appreciate all the help I could get.


Thanks!
Yula

SELECT [Customer Table].[Tracking Number], [Customer Table].[Network Login
ID], [Customer Table].[Submitted By], [Customer Table].[Delivery Building],
[Customer Table].[Delivery Floor], [Customer Table].[Cost Center], [Customer
Table].BU, [Customer Table].Telephone, [Customer Table].Department, [Customer
Table].Division, [Date Table].[Time Submitted], [Finish Table].[Type of
Finish], [Impressions Table].Machine, [Impressions Table].[Black_White or
Color], [Date Table].[Date Required], [Paper Table].Paper,
Quantities.[Impressions Per Original], Quantities.[1 or 2 sided], [Date
Table].[Time Required], [Date Table].[Date Completed], [Date Table].[Time
Completed], [Date Table].[Date Submitted], Quantities.[Start Meter],
Quantities.[End Meter], Quantities.[File Type], Size.[Width IN], Size.[Length
IN], Quantities.[Total Copies Requested], [Operator Table].Operator
FROM [Paper Lookup] INNER JOIN ([Machine Lookup] INNER JOIN ([Finish Lookup]
INNER JOIN (((((([Finish Table] INNER JOIN ([Customer Table] INNER JOIN
Quantities ON [Customer Table].[Tracking Number] = Quantities.[Tracking
Number]) ON [Finish Table].[Tracking Number] = [Customer Table].[Tracking
Number]) INNER JOIN [Impressions Table] ON [Customer Table].[Tracking Number]
= [Impressions Table].[Tracking Number]) INNER JOIN [Operator Table] ON
[Customer Table].[Tracking Number] = [Operator Table].[Tracking Number])
INNER JOIN [Paper Table] ON [Customer Table].[Tracking Number] = [Paper
Table].[Tracking Number]) INNER JOIN [Date Table] ON [Customer
Table].[Tracking Number] = [Date Table].[Tracking Number]) INNER JOIN [Size]
ON ([Finish Table].[Tracking Number] = Size.[Tracking Number]) AND ([Paper
Table].[Tracking Number] = Size.[Tracking Number]) AND ([Customer
Table].[Tracking Number] = Size.[Tracking Number]) AND ([Paper
Table].[Tracking Number] = Size.[Tracking Number]) AND ([Customer
Table].[Tracking Number] = Size.[Tracking Number]) AND (Quantities.[Tracking
Number] = Size.[Tracking Number])) ON [Finish Lookup].[Finish Description] =
[Finish Table].[Type of Finish]) ON [Machine Lookup].[Machine Name] =
[Impressions Table].Machine) ON [Paper Lookup].[Paper Description] = [Paper
Table].Paper;

 

[John W. Vinson]

I have a form which is based on a query that is joining several tables all
related by a primary key "Tracking Number".

Are these one to one relationships? If so... your design is almost surely
wrong.

And if they're one to many relationships, you would be a lot better basing the
Form on the "one" side table (or "one-est" table), and using multiple Subforms
for the child tables. A seven-table join query will almost certainly NOT be
updateable.

I am using that query "Test2" as
the control source for my form " form2". I got the form to add records and
its working just fine, when I go into the query, those records that I added
are not showing up in that Test2. In fact, i can't even add a record in the
datasheet view of the query. I am posting the SQL for Test2 and I would
appreciate all the help I could get.

I rather doubt that Test2 is in fact the Recordsource of the Form -
doublecheck! Is it, or is the form based on some other query?

Test2 will return nothing unless *every single one* of the seven tables has
matching data. If even one table is missing data for this tracking number
you'll see nothing, because you're using INNER JOIN - which requires data in
both tables. For a Report you may want to use some LEFT OUTER JOINs to link
tables which only optionally contain data.

 

[Yula]

John,
Thanks for the reply, but now I am completly confused. My forms record
source is based on test2. The 7 tables all have one to one relationship and 3
of those tables have lookup. Let me give you an example and maybe it will be
easier to see why I need to update this query in a form. I need to create a
form for print operators to fill in certain info and at the back end it will
calculate the cost for each choice that they make.

1. A customer places an order for a specific print job (Finish coil, paper
8.5 X 11, qty 10, copies in each booklet 20.) This job get an assigned
Tracking# 123

2. manager delegates job to operator

3. Operator fills in a form

Customer info
Finish (Coil, velo... they choose from my finish lookup)
paper ("")
Quantity
Impressions (# of copies per item) single *1 Double/2
Operator: value list
Size: if this is an over size job like a poster there is a cost per size.
value list

When the operator fills this in a form that I created, all this info is
populated into my query and I can run my calc quary as well as other reports.

Thanks for your help,
Julia