Querying from a Query

[jo15765]

I don't know if this is possible, but I ran a query to make a field (SKP B) always be 5 digits. The query works great. Now, I am wanting to query from this data, and combine two fields (SKP A and SKP B). The problem I am running into is that, when I ran the first query (adding the leading zero's) Access named the output data as Expression, and when I try to build my query to combine the two I get a dialog box asking me for the data for expression. How can I successfully run this query from a query, or can I?



I have it working, where I run the query to add leading 0's, then create a form, and then from that form, query again to combine the SKPA and SKPB, but I would rather do it all in one query to make it easier!

[HiTechCoach]

I do not see why you can't do what you want.

try something like this:

SKPAB: Format([SKP_A], "00000") & [SKP_B]

[jo15765]

I tried to use the code:

SELECT SKPAB: Format([SKP_A], "00000") & [SKP_B]
FROM Sheet1;

And I got this error:

Syntax Error (missing operator) in query expression 'SKAB: Format([SKP_A], "00000") & [SKP_B]'.

[jo15765]

Okay, I am extremely confused now. This code works:

SELECT =Format ([SKP B], "00000") & "-" &  [SKP A] AS SKPA
FROM Sheet1;

It's in the wrong order, I need SKPA first and then SKPB second

But this code does not:

SELECT =Format [SKP A] & "-" &  ([SKP B], "00000") AS SKPA
FROM Sheet1;

I get an error that states:

Syntax Error (Missing Operator) in query expression '=SELECT =Format [SKP A] & "-" &  ([SKP B], "00000").'

Is it not EXACTLY the same code?

[HiTechCoach]

The example I gave you was what you woudl us int he Query Design, not SQL.

I would suggest the you switch to the Query Design mode. It does a not of validation for you to help you write teh SQL.

Try:

SELECT [SKP A] & "-" &  Format([SKP B], "00000") AS SKPA
FROM Sheet1;

[jo15765]

Ah, the code that you posted worked! So I can input code in the Query Design Mode, as well as the SQL View?

[HiTechCoach]

Ah, the code that you posted worked! So I can input code in the Query Design Mode, as well as the SQL View?

Yes. That is correct. There are many advantage to learning how to use the Query Designer.

I really prefer the Query Design mode over SQL View. The only time I have to use the SQL view is for Pass-Thru queries and Union Queries.

I not SQL very well and could write all my queries by hand in the SQL view. I find it much faster and more efficient to use the Query Designer Tool. It really is a great tool. I wish I had something like it in other SQL Server environments.


When training any user on Access query writing I teach them how use the Query Designer Tool.

[jo15765]

When training any user on Access query writing I teach them how use the Query Designer Tool.[/QUOTE]


Any recommendation on where I can get further training on how to use this Tool? I am still a newbie, but am learning a lot from these forums, and want to learn more since I have "inherited" databases designed by someone else.

[HiTechCoach]

Any recommendation on where I can get further training on how to use this Tool? I am still a newbie, but am learning a lot from these forums, ...

Is there anything specific that you would like to learn about writing queries in Access?

If you were to watch a short training video, what would you like to learn?

... and want to learn more since I have "inherited" databases designed by someone else.

Inheriting work by other developer is usually not an easy task even for experienced people.

Back up, Back up, Back up ....

TIP 1: Make lots of back ups.

Back up, Back up, Back up ....

TIP 2: Hopefully the database are already split into a front end and back end. Id not, that is the first thing I would do.

Back up, Back up, Back up ....

TIP 3: Document, Back up, Document, Back up, Document, Back up, ...

Back up, Back up, Back up ....

Oh yeah, make lots of back ups.

[jo15765]

Yes, it is definitely a chore with the databases! Also, some of the VB code and macros that were used are painful to decipher, so I may be here frequently .

Currently the databases are not split into a front end and back end. The users are using the original files, and we have been zipping them for a backup at the end of each day. How would I create FE and BE files?

Nothing in particular comes to mind at this moment with queries, would just like to familiarize myself more with queries (like the one you showed me earlier).

Also, I can google it, but are there any specific videos that you recommend that are better than others?

[HiTechCoach]

Yes, it is definitely a chore with the databases! Also, some of the VB code and macros that were used are painful to decipher, so I may be here frequently .

If would have to say it is not limited to just database. It is any application development platform.

The biggest problem I see with most software development, especially in-house or custom stuff, is the lack of documentation and manuals.

Currently the databases are not split into a front end and back end. The users are using the original files, and we have been zipping them for a backup at the end of each day. How would I create FE and BE files?

See if this article I wrote helps:
Splitting your Access database into application and data

Nothing in particular comes to mind at this moment with queries, would just like to familiarize myself more with queries (like the one you showed me earlier).

Also, I can google it, but are there any specific videos that you recommend that are better than others?

I really have not seen any. So I don't have any recommendations at this time.

The reason I ask is that I am considering releasing some videos I have made.

[jo15765]

Personally, I would love to see some video's that you made on anything to do with access! It would be very beneficial and helpful for me. This website came recommended by a friend (it's not free) he used it and said it was wonderful, so I am considering checking it out.

http://www.lynda.com/

I am reading the article about splitting the Front End and Back End files, it is very informative, and I am going to email it to myself (This project is a work project, but due to my lack of knowledge most of it carries over to my personal time) at work, and begin testing it tomorrow. So if any problems or further questions I run into, I will be sure to post and let you know!

Thanks for all of your assistance!