Querie from 2 tables, adding new records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Before I ask the question a little background.

I have a querie that is build on One table (Hybe) that has Hybe # as Primary
Key and another table (Hybe Channel) that has hybe # and Channel as Primary
Keys. The Channel can only have two values (Cy3 and Cy5)

Then I build a query that gives my a result for each record, all the
information from Hybe table, the information from Hybe channel for Cy3 and
also for Cy5.

So in the design view of the querie I have the Hybe table once and then the
Hybe Channel table twice, once for each channel.
To link the tables together I use join properties #2
This works fine and I get all the information right.

But then there is the problem, I can't add new records to the query ( or
more in the form based on the query) Why is that ??

Thanks
Karen Jenny
 
Ok so her is the SQL statemetn, though I am good working directly in SQL I
can take simple directions :)

SELECT Hybridization.Hybe_Number, Hybridization.Chip_Barcode,
Hybridization.Experiment_ID, [Cy3 Hybe_Channel].[Labeling barcode], [Cy3
Hybe_Channel].[Amount used sample], [Cy5 Hybe_Channel_1].[Labeling barcode],
[Cy5 Hybe_Channel_1].[Amount used sample], Hybridization.Hyb_method,
Hybridization.Hybed_by, Hybridization.Results_date, Hybridization.Comments,
Hybridization.Results, [Cy3 Hybe_Channel].PMT, [Cy5 Hybe_Channel_1].PMT, [Cy5
Hybe_Channel_1].Channel, [Cy3 Hybe_Channel].Channel
FROM (Hybridization LEFT JOIN Hybe_Channel AS [Cy3 Hybe_Channel] ON
Hybridization.Hybe_Number = [Cy3 Hybe_Channel].[Hybe Number]) LEFT JOIN
Hybe_Channel AS [Cy5 Hybe_Channel_1] ON Hybridization.Hybe_Number = [Cy5
Hybe_Channel_1].[Hybe Number]
WHERE ((([Cy5 Hybe_Channel_1].Channel)="Cy5") AND (([Cy3
Hybe_Channel].Channel)="Cy3"));


Thanks
Karen Jenny
 
Karen

Since you are trying to write to what sounds like a "many" side
(Hybe_Channel) table, I wonder if you need to include the primary key
fields?

Are you working with a main form/subform design?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Karen Jenny said:
Ok so her is the SQL statemetn, though I am good working directly in SQL I
can take simple directions :)

SELECT Hybridization.Hybe_Number, Hybridization.Chip_Barcode,
Hybridization.Experiment_ID, [Cy3 Hybe_Channel].[Labeling barcode], [Cy3
Hybe_Channel].[Amount used sample], [Cy5 Hybe_Channel_1].[Labeling barcode],
[Cy5 Hybe_Channel_1].[Amount used sample], Hybridization.Hyb_method,
Hybridization.Hybed_by, Hybridization.Results_date, Hybridization.Comments,
Hybridization.Results, [Cy3 Hybe_Channel].PMT, [Cy5 Hybe_Channel_1].PMT, [Cy5
Hybe_Channel_1].Channel, [Cy3 Hybe_Channel].Channel
FROM (Hybridization LEFT JOIN Hybe_Channel AS [Cy3 Hybe_Channel] ON
Hybridization.Hybe_Number = [Cy3 Hybe_Channel].[Hybe Number]) LEFT JOIN
Hybe_Channel AS [Cy5 Hybe_Channel_1] ON Hybridization.Hybe_Number = [Cy5
Hybe_Channel_1].[Hybe Number]
WHERE ((([Cy5 Hybe_Channel_1].Channel)="Cy5") AND (([Cy3
Hybe_Channel].Channel)="Cy3"));


Thanks
Karen Jenny

Jeff Boyce said:
Karen

Please post the SQL statement of your query. You can open the query in
design mode, change the view to SQL, and copy the SQL statement. Paste it
into your post.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


then
the
Click to expand...
Click to expand...
 
OK I am not sure what you mean by "many" side table. Hybe Channel is only
one table but I name it Cy3 and Cy5 in the querie because I need results from
2 records that match the one from hybe table.
So should I then include the primary keys in the query ??

I'm not using a main form/subform but only one form based on that one query

Regards
Karen


Jeff Boyce said:
Karen

Since you are trying to write to what sounds like a "many" side
(Hybe_Channel) table, I wonder if you need to include the primary key
fields?

Are you working with a main form/subform design?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Karen Jenny said:
Ok so her is the SQL statemetn, though I am good working directly in SQL I
can take simple directions :)

SELECT Hybridization.Hybe_Number, Hybridization.Chip_Barcode,
Hybridization.Experiment_ID, [Cy3 Hybe_Channel].[Labeling barcode], [Cy3
Hybe_Channel].[Amount used sample], [Cy5 Hybe_Channel_1].[Labeling barcode],
[Cy5 Hybe_Channel_1].[Amount used sample], Hybridization.Hyb_method,
Hybridization.Hybed_by, Hybridization.Results_date, Hybridization.Comments,
Hybridization.Results, [Cy3 Hybe_Channel].PMT, [Cy5 Hybe_Channel_1].PMT, [Cy5
Hybe_Channel_1].Channel, [Cy3 Hybe_Channel].Channel
FROM (Hybridization LEFT JOIN Hybe_Channel AS [Cy3 Hybe_Channel] ON
Hybridization.Hybe_Number = [Cy3 Hybe_Channel].[Hybe Number]) LEFT JOIN
Hybe_Channel AS [Cy5 Hybe_Channel_1] ON Hybridization.Hybe_Number = [Cy5
Hybe_Channel_1].[Hybe Number]
WHERE ((([Cy5 Hybe_Channel_1].Channel)="Cy5") AND (([Cy3
Hybe_Channel].Channel)="Cy3"));


Thanks
Karen Jenny

Jeff Boyce said:
Karen

Please post the SQL statement of your query. You can open the query in
design mode, change the view to SQL, and copy the SQL statement. Paste it
into your post.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Before I ask the question a little background.

I have a querie that is build on One table (Hybe) that has Hybe # as
Primary
Key and another table (Hybe Channel) that has hybe # and Channel as
Primary
Keys. The Channel can only have two values (Cy3 and Cy5)

Then I build a query that gives my a result for each record, all the
information from Hybe table, the information from Hybe channel for Cy3 and
also for Cy5.

So in the design view of the querie I have the Hybe table once and then
the
Hybe Channel table twice, once for each channel.
To link the tables together I use join properties #2
This works fine and I get all the information right.

But then there is the problem, I can't add new records to the query ( or
more in the form based on the query) Why is that ??

Thanks
Karen Jenny
Click to expand...
Click to expand...
Click to expand...
 
Karen

Take a look at Access HELP re: updateable queries.

It sounded like your [Hybe Channel] is a "child" ("many") table to your main
table.

Try including the primary key from the "many" side table.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Karen Jenny said:
OK I am not sure what you mean by "many" side table. Hybe Channel is only
one table but I name it Cy3 and Cy5 in the querie because I need results from
2 records that match the one from hybe table.
So should I then include the primary keys in the query ??

I'm not using a main form/subform but only one form based on that one query

Regards
Karen


Jeff Boyce said:
Karen

Since you are trying to write to what sounds like a "many" side
(Hybe_Channel) table, I wonder if you need to include the primary key
fields?

Are you working with a main form/subform design?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Karen Jenny said:
Ok so her is the SQL statemetn, though I am good working directly in
Click to expand...
SQL
I
can take simple directions :)

SELECT Hybridization.Hybe_Number, Hybridization.Chip_Barcode,
Hybridization.Experiment_ID, [Cy3 Hybe_Channel].[Labeling barcode], [Cy3
Hybe_Channel].[Amount used sample], [Cy5 Hybe_Channel_1].[Labeling barcode],
[Cy5 Hybe_Channel_1].[Amount used sample], Hybridization.Hyb_method,
Hybridization.Hybed_by, Hybridization.Results_date, Hybridization.Comments,
Hybridization.Results, [Cy3 Hybe_Channel].PMT, [Cy5
Click to expand...
Hybe_Channel_1].PMT,
[Cy5
Hybe_Channel_1].Channel, [Cy3 Hybe_Channel].Channel
FROM (Hybridization LEFT JOIN Hybe_Channel AS [Cy3 Hybe_Channel] ON
Hybridization.Hybe_Number = [Cy3 Hybe_Channel].[Hybe Number]) LEFT JOIN
Hybe_Channel AS [Cy5 Hybe_Channel_1] ON Hybridization.Hybe_Number = [Cy5
Hybe_Channel_1].[Hybe Number]
WHERE ((([Cy5 Hybe_Channel_1].Channel)="Cy5") AND (([Cy3
Hybe_Channel].Channel)="Cy3"));


Thanks
Karen Jenny

:

Karen

Please post the SQL statement of your query. You can open the query in
design mode, change the view to SQL, and copy the SQL statement.
Click to expand...
Paste
it
into your post.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Before I ask the question a little background.

I have a querie that is build on One table (Hybe) that has Hybe # as
Primary
Key and another table (Hybe Channel) that has hybe # and Channel as
Primary
Keys. The Channel can only have two values (Cy3 and Cy5)

Then I build a query that gives my a result for each record, all the
information from Hybe table, the information from Hybe channel for
Click to expand...
Cy3
and
also for Cy5.

So in the design view of the querie I have the Hybe table once and then
the
Hybe Channel table twice, once for each channel.
To link the tables together I use join properties #2
This works fine and I get all the information right.

But then there is the problem, I can't add new records to the
Click to expand...
query
( or
more in the form based on the query) Why is that ??

Thanks
Karen Jenny
Click to expand...
Click to expand...
Click to expand...
 
Thanks for all your help, but I am still stuck with this problem. Where
exactly do i find Access HELP re: updateable queries ??

I have tried to put the primary keys in for the Channel table but that
didn't change anything.
Is the problem maybe that I have the same primary keys in both tables (hybe
number)

Regards
Karen Jenny

Jeff Boyce said:
Karen

Take a look at Access HELP re: updateable queries.

It sounded like your [Hybe Channel] is a "child" ("many") table to your main
table.

Try including the primary key from the "many" side table.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Karen Jenny said:
OK I am not sure what you mean by "many" side table. Hybe Channel is only
one table but I name it Cy3 and Cy5 in the querie because I need results from
2 records that match the one from hybe table.
So should I then include the primary keys in the query ??

I'm not using a main form/subform but only one form based on that one query

Regards
Karen


Jeff Boyce said:
Karen

Since you are trying to write to what sounds like a "many" side
(Hybe_Channel) table, I wonder if you need to include the primary key
fields?

Are you working with a main form/subform design?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Ok so her is the SQL statemetn, though I am good working directly in SQL
I
can take simple directions :)

SELECT Hybridization.Hybe_Number, Hybridization.Chip_Barcode,
Hybridization.Experiment_ID, [Cy3 Hybe_Channel].[Labeling barcode], [Cy3
Hybe_Channel].[Amount used sample], [Cy5 Hybe_Channel_1].[Labeling
barcode],
[Cy5 Hybe_Channel_1].[Amount used sample], Hybridization.Hyb_method,
Hybridization.Hybed_by, Hybridization.Results_date,
Hybridization.Comments,
Hybridization.Results, [Cy3 Hybe_Channel].PMT, [Cy5 Hybe_Channel_1].PMT,
[Cy5
Hybe_Channel_1].Channel, [Cy3 Hybe_Channel].Channel
FROM (Hybridization LEFT JOIN Hybe_Channel AS [Cy3 Hybe_Channel] ON
Hybridization.Hybe_Number = [Cy3 Hybe_Channel].[Hybe Number]) LEFT JOIN
Hybe_Channel AS [Cy5 Hybe_Channel_1] ON Hybridization.Hybe_Number = [Cy5
Hybe_Channel_1].[Hybe Number]
WHERE ((([Cy5 Hybe_Channel_1].Channel)="Cy5") AND (([Cy3
Hybe_Channel].Channel)="Cy3"));


Thanks
Karen Jenny

:

Karen

Please post the SQL statement of your query. You can open the query in
design mode, change the view to SQL, and copy the SQL statement. Paste
it
into your post.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Before I ask the question a little background.

I have a querie that is build on One table (Hybe) that has Hybe # as
Primary
Key and another table (Hybe Channel) that has hybe # and Channel as
Primary
Keys. The Channel can only have two values (Cy3 and Cy5)

Then I build a query that gives my a result for each record, all the
information from Hybe table, the information from Hybe channel for Cy3
and
also for Cy5.

So in the design view of the querie I have the Hybe table once and
then
the
Hybe Channel table twice, once for each channel.
To link the tables together I use join properties #2
This works fine and I get all the information right.

But then there is the problem, I can't add new records to the query
( or
more in the form based on the query) Why is that ??

Thanks
Karen Jenny
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

more than one detail section in a report 1
Solve query? 14
table: using multiple fields to define a duplicate 3
merging 2 tables 1
Combine data from 2 queries 1
Copy selected records from two tables to two new tables 2
With a Query in Access 2007, How can I Create This Query 3
Query from three tables, problem when adding a record 1

Back
Top