Help with SQL

D

DevilDog1978

Why does this not work?

INSERT INTO SHIP CALSEL (Model Number, Part Of, CAGE, Nomenclature, CV QTY,
CV NOTES)
SELECT Model Number, Part Of, CAGE, Nomenclature, QTY, NOTES
FROM FY09_CVN_CALSEL
INSERT SHIP CALSEL (Model Number, Part Of, CAGE, Nomenclature, LH QTY, LH
NOTES)
SELECT Model Number, Part Of, CAGE, Nomenclature, QTY, NOTES
FROM FY09_LH_CALSEL
Group By Model Number

There are two different tables: FY09_LH_CALSEL AND FY09_CV_CALSEL
that I want to combine into SHIP CALSEL

There are several Model Number/CAGE combinations that are unique to the two
tables. Thos two tables also have many Model Number/CAGE combinations in
common. The Quanities (QTY) vary for the various Model Numbers (that are in
common) and I want that captured. Does this make any sense?
 
J

John Spencer

First problem is your field names have spaces in them so they must be
surrounded by square brackets.

Second problem is you have TWO queries. Access allows only one query so

INSERT INTO [SHIP CALSEL] ([Model Number], [Part Of], CAGE,
Nomenclature, [CV QTY], [CV NOTES])
SELECT [Model Number], [Part Of], CAGE, Nomenclature, QTY, NOTES
FROM FY09_CVN_CALSEL

Then in a separate query fix this one since you have GROUP BY clause,
you need to group by all the fields not just one.

INSERT [SHIP CALSEL] (Model Number, Part Of, CAGE, Nomenclature, LH QTY,
LH NOTES)
SELECT [Model Number], [Part Of], CAGE, Nomenclature, QTY, NOTES
FROM FY09_LH_CALSEL
GROUP By [Model Number], [Part Of], CAGE, Nomenclature, QTY, NOTES

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
D

DevilDog1978

John,

This gets me a lot closer. I want the table to be "Grouped by." In other
words I want my table to have Model Number, Part Of, CAGE, Nomenclature, CV
QTY, CV NOTES, LH QTY, LH NOTES. I want the Model Number "Grouped" so that
there is only one occurance of the Model Number in the table but still list
the different quantitis from the CV and LH.

John Spencer said:
First problem is your field names have spaces in them so they must be
surrounded by square brackets.

Second problem is you have TWO queries. Access allows only one query so

INSERT INTO [SHIP CALSEL] ([Model Number], [Part Of], CAGE,
Nomenclature, [CV QTY], [CV NOTES])
SELECT [Model Number], [Part Of], CAGE, Nomenclature, QTY, NOTES
FROM FY09_CVN_CALSEL

Then in a separate query fix this one since you have GROUP BY clause,
you need to group by all the fields not just one.

INSERT [SHIP CALSEL] (Model Number, Part Of, CAGE, Nomenclature, LH QTY,
LH NOTES)
SELECT [Model Number], [Part Of], CAGE, Nomenclature, QTY, NOTES
FROM FY09_LH_CALSEL
GROUP By [Model Number], [Part Of], CAGE, Nomenclature, QTY, NOTES

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Why does this not work?

INSERT INTO SHIP CALSEL (Model Number, Part Of, CAGE, Nomenclature, CV QTY,
CV NOTES)
SELECT Model Number, Part Of, CAGE, Nomenclature, QTY, NOTES
FROM FY09_CVN_CALSEL
INSERT SHIP CALSEL (Model Number, Part Of, CAGE, Nomenclature, LH QTY, LH
NOTES)
SELECT Model Number, Part Of, CAGE, Nomenclature, QTY, NOTES
FROM FY09_LH_CALSEL
Group By Model Number

There are two different tables: FY09_LH_CALSEL AND FY09_CV_CALSEL
that I want to combine into SHIP CALSEL

There are several Model Number/CAGE combinations that are unique to the two
tables. Thos two tables also have many Model Number/CAGE combinations in
common. The Quanities (QTY) vary for the various Model Numbers (that are in
common) and I want that captured. Does this make any sense?
Click to expand...
Click to expand...
 
J

John Spencer

Well, you can't really do that. Table and queries are homogeneous -
that is they have the same number of columns and you cannot suppress the
value of a column.

On the other hand, in a REPORT you can hide a repeating value or
structure the report so that you have a group header with the data you
want to appear once for the group and then detail lines for all the
information that varies within the group.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,

This gets me a lot closer. I want the table to be "Grouped by." In other
words I want my table to have Model Number, Part Of, CAGE, Nomenclature, CV
QTY, CV NOTES, LH QTY, LH NOTES. I want the Model Number "Grouped" so that
there is only one occurance of the Model Number in the table but still list
the different quantitis from the CV and LH.

John Spencer said:
First problem is your field names have spaces in them so they must be
surrounded by square brackets.

Second problem is you have TWO queries. Access allows only one query so

INSERT INTO [SHIP CALSEL] ([Model Number], [Part Of], CAGE,
Nomenclature, [CV QTY], [CV NOTES])
SELECT [Model Number], [Part Of], CAGE, Nomenclature, QTY, NOTES
FROM FY09_CVN_CALSEL

Then in a separate query fix this one since you have GROUP BY clause,
you need to group by all the fields not just one.

INSERT [SHIP CALSEL] (Model Number, Part Of, CAGE, Nomenclature, LH QTY,
LH NOTES)
SELECT [Model Number], [Part Of], CAGE, Nomenclature, QTY, NOTES
FROM FY09_LH_CALSEL
GROUP By [Model Number], [Part Of], CAGE, Nomenclature, QTY, NOTES

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Why does this not work?

INSERT INTO SHIP CALSEL (Model Number, Part Of, CAGE, Nomenclature, CV QTY,
CV NOTES)
SELECT Model Number, Part Of, CAGE, Nomenclature, QTY, NOTES
FROM FY09_CVN_CALSEL
INSERT SHIP CALSEL (Model Number, Part Of, CAGE, Nomenclature, LH QTY, LH
NOTES)
SELECT Model Number, Part Of, CAGE, Nomenclature, QTY, NOTES
FROM FY09_LH_CALSEL
Group By Model Number

There are two different tables: FY09_LH_CALSEL AND FY09_CV_CALSEL
that I want to combine into SHIP CALSEL

There are several Model Number/CAGE combinations that are unique to the two
tables. Thos two tables also have many Model Number/CAGE combinations in
common. The Quanities (QTY) vary for the various Model Numbers (that are in
common) and I want that captured. Does this make any sense?
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

SQL not working 8
combining queries into 1 8
Query Help 3
Can't Enter New Record in Subform 4
Form Problem 1
Nulls and Strings comming from SQL 2
Changing Test Color to alert user 1
Help with VBA sql code 2

Top