Combing Queries

[ElcoyotldeAztlan]

Hello everyone
I'm try to combine two queries that do work fine when separated. I put the two together on one Query so that I can reduce the number of query processes when transferring data into another table (I want to only have to press the run button once not may)

I have on my database the following code

SELECT color.ColorID, Cardata_xlsx.[Color ]
FROM color INNER JOIN Cardata_xlsx ON color.ColorName = Cardata_xlsx.[Color ]
INSERT INTO MainTable (ColorID)
SELECT ColorID
From Query1;


Error message says

syntax error (missing operator) in query expression
color.ColorName = Cardata_xlsx.[Color ]
INSERT INTO MainTable (ColorID)
SELECT ColorID
From Query1;

any ideas
Thanks!

sidenote - Cardata_xlsx is just a name of one of my tables that I imported form excel I didnt bother to rename it

 

[isladogs]

Remove the space at the end of the field name
[Color ]

 

[ElcoyotldeAztlan]

I removed the space still same error

 

[Galaxiom]

INSERT INTO MainTable (ColorID) 
SELECT ColorID
FROM color 
INNER JOIN Cardata_xlsx 
ON color.ColorName = Cardata_xlsx.[Color]
;

 

[ElcoyotldeAztlan]

Thanks Galaxiom
that code works fine

 

[ElcoyotldeAztlan]

is it still possible for me to add more code to this one query performing the same task but on other tables?

 

[Minty]

No an Insert Query can only insert records on one table.

 

[ElcoyotldeAztlan]

Thanks If thats the case I know one other way which would be to create a VBA code but if I have a large database of 3000 unique names and they all have shared related fields then I would have to create a VBA code stating if statements that have go though every cell in each columns and state which unique ID goes into each cell seems like a very long code and process to write, when I can just use this SQL query which I have about 35 tables to match with. Even if its 35 queries

thoughts?

 

[plog]

Big picture time. Tell us what you are starting with and where you hope to end up. So far you've only let us in on an issue you are having in the middle.

So, in plain english (no database jargon) explain what this data represents and how your organization uses it.

Then, you may use database jargon and explain what you start with and what you hope to end up with. Sample data would be helpful here.

 

[ElcoyotldeAztlan]

Basically I'm looking for way to automatic fill everything (data) in Access from an Excel sheet into specific tables and have all related IDs connected. (I dont think that is possible when in less you create a large VBA code with IF statements)
I already have a code where I can import an Excel sheet in Access as a table
From here
I know I can use Append in query mode to transfer Excel columns to Access table fields one table at a time (is there way where I can do that for all tables at the same time? get the data from the Excel or the one Access table that is the excel data that I imported?)

I would then like to find a way to automatically fill the IDs in all related table fields. I know can do this by using SQL but I can only do it one table at a time. (can I find a way to do that to multiple tables) thats why I was wondering if I can combine SQL quieres together

 

[plog]

You are still discussing middle parts and not ultimate goal. Also, didn't include an overview of what your organization does.

Why must this data get into Access? What does that do for you that Excel isn't?

 

[isladogs]

Yes its possible.

The way I do it is as follows:
As part of my schools database, I use a scheduled task to run a routine each night when no users are on the system. This routine:
1.Remotely logs in and exports 30 CSV files from the school management system database
2. Imports the CSV files to 30 buffer tables in Access
3. Modifies the data in various fields in each buffer table then in turn appends new data and updates existing data in the main SQL server tables
4. Writes a log file of the changes made
5. Empties all buffer tables, tidies up and closes the db.

The process takes a while as there is a lot of data to handle
You will realise that the code for that is complex and not something I can upload.

However, I am happy to advise with parts of this if you would find that helpful

You would need to do each import in sequence with the order dependant on table links.
Make sure that the tables with common id fields have cascade update and delete switched on.

I'm not really sure why you raised the issue of table IDs.
That's simple to manage

 

[ElcoyotldeAztlan]

well I bring up table IDs because I have tables that are related to each other so
for example If my main table is cars and my related tables are colors, year,manufactures

I would like to automatically fill those columns in with their IDs

so for example

CarID Car ColorID YearID ManufactureID

I'm looking for a quick fast way to fill these IDs in because of course many cars have the same color I want to reduce the duplication so I will have the ID number of Blue repeat it's self in this main table but not in the color table