This might be confusing

[djclntn]

In my Music Library Database I made some changes to my fld names (weedy out the spcl chrctrs). One of the neat queries (I got help creating) was a count of ALL of my Artist (ArtistID) first letters from my old ML DB

eg.
A 4
B 20
C 25
Etc.

I copy/pasted the old SQL from my original Music Library Database query showing me what I illustrated above & I VERY VERY carefully typed in the new query & fld name 5-different times, yet I’m getting an error message saying that it cannot find the input query or table.

Here’s the query SQL I’m trying to pull the date from: Music Library Alpha (Query)
SELECT [Music Library].mlbrID, [Music Library].mlbrArtistID AS ArtistID, [Music Library].mlbrArtist AS Artist, [Music Library].mlbrAlbumn AS [Albumn\CD], [Music Library].mlbrNumTrcks AS NumTrcks, [Music Library].mlbrRD AS RD, [Music Library].[mlbrRecord Label] AS RecordLabel, [Music Library].mlbrGenre AS Genre, [Music Library].mlbrGH AS GrstHts
FROM [Music Library]
ORDER BY [Music Library].mlbrArtist, [Music Library].mlbrAlbumn;

Here’s the query I’m trying:
SELECT DISTINCT Left([mlbrArtistID],1) AS ArtistFirstLetter, Count(Left([mlbrArtistID],1)) AS CountmlbrArtistID
FROM [Music Library Alpha\mlbrArtistID]
GROUP BY Left([mlbrArtistID],1)
HAVING (((Left([mlbrArtistID],1)) Is Not Null))
ORDER BY Left([mlbrArtistID],1);

*Note in my Music Library QUERIES I made the [mlbrArtistID] to just ArtistID: [mlbrArtistID], but I tried using both ArtistID & mlbrArtistID & it still cannot6 find the input query or table…?

[aytee111]

What is this - "FROM [Music Library Alpha\mlbrArtistID]"?

OK, I've never seen"\" that before but Access is telling you that this is where your problem lies. Did you not change the name by saying "[Music Library].mlbrArtistID AS ArtistID"? Shouldn't the name therefore be ArtistID?

[orange]

Try this, I tried putting both queries together. Untested, but give it a try.

NOTE: When you do this

mlbrArtistID AS ArtistID

The field will be known as ArtistID in the result, so when you query the query, you have to use the assigned name.

Also you still have special characters in your names ( [Albumn\CD]) and
([mlbrRecord Label])

SELECT DISTINCT Left(ArtistID,1) AS ArtistFirstLetter
, Count(Left(ArtistID,1)) AS CountmlbrArtistID
FROM
(
SELECT mlbrID
, mlbrArtistID AS ArtistID
, mlbrArtist AS Artist
, mlbrAlbumn AS [Albumn\CD]
, mlbrNumTrcks AS NumTrcks
, mlbrRD AS RD
, [mlbrRecord Label] AS RecordLabel
, mlbrGenre AS Genre
, mlbrGH AS GrstHts
FROM [Music Library]
ORDER BY
mlbrArtist
, mlbrAlbumn
)
GROUP BY Left(ArtistID,1)
HAVING (((Left(ArtistID,1)) Is Not Null))
ORDER BY Left(ArtistID,1);

[orange]

djcIntn,

How did this work? Did you get it resolved?

[djclntn]

Hi Orange,

To answer your question, yes & no. What do I mean? It worked in the database at the time I was using, however I since had to strip that database & am now in the process of rebuilding my Music Library Database again! Can I share something with you that I told somebody else on this forum – its database related.

I don't like bashing other people; someone here sent me a link on naming tbl & qry flds names when creating tbls & their advice was to put an initial before each fld name correlated with the tbl name. Well I thought that was wise & I changed all of my dbs over with using this concept. Here's the problem I later discovered, if you have X-amount of tbls without any common flds then how do I relate the tbls to one another. Either it was suggested on this webpage or I thought of creating qrys for each tbl & renaming some of the fld names so this way I can relate my qrys/tbls with common names. Then I started thinking about to myself all of the changes I made & what I'd described just now is BS! Do you use or have ever heard of such a system? I don't want to blame or get mad at whoever sent me that link because maybe there are many benefits with using that fld naming system, I just didn't see the benefit, rather more work.

I'm sorry for being long-winded & ranting here; out of 4-major dbs I was able to revert back to the 2-original dbs, but I'm having major problems with converting back to this one dbs. Now, my whole Access program seems to be acting dfuny & malfunctioning, I'm having trouble with keeping my felds' sizes to stay fitted in my qrys in datasheet view & I'm encountering other problems too which seem to be occurring in many of my databases. Something changed & I don’t know what I did.

Thanks for getting back to me. :-)

[orange]

Hi,
Sorry to hear you're having a lot of issues with databases. Instead of changing your operational database, it would be beneficial to make a copy and work on the copy - even save the latest good operational database to a special folder.

I don't use any naming that involves identifying the query with a field - at least not intentionally. If I have a table Animal, I would/could have a field AnimalId or just Id.

Since queries deal with tables, I use the field names from the underlying table(s) as needed. If I have to use aliases for field and or table names (such as with subqueries), I tend to make them meaningful to me in the context.

You can go overboard with any approach that tends to add some discipline to your efforts. I once knew an outfit that had data elements EL0001, EL0002 etc. not too user friendly. Others wanted to have their fields names positioned in their tables such that the fields occurred in alphabetic sequence... too much!

I think you should have a workable set of rules that makes sense to you.
- Do regular backups.
- Do regular Compact and repairs to reduce bloat and chances of corruption.
- Don't have special characters or blanks in field or object names
- Use meaningful names for fields, tables and objects
- Use Option Explicit to force DIMming all variables.
- Use autonumber PK in all tables
- Use Debug.Prints to evaluate expressions before attempting to update tables

[djclntn]

Thanks orange for your suggestions. I do make copies of my original databases, but after a while I become overwhelmed & lose my path. My problem is that I'm ver annal about keeping thre original databasse name when i make major changes to the database & that what messes me up & I lose the original database. I managed to figure out one of my problems on one of my databases which is a BIG relief!

Pofessionally, I like corresponding with you. Can I keep this thread & knock on your door whenever I have a queestion/problem, or ought I go through the forum?
Thanks orange for your suggestions. I do make copies of my original databases, but after a while I become so overwhelmed & lose my path. My problem is that I'm very anal about keeping the original database name when I make major changes to the database & that what messes me up & I lose the original database. I managed to figure out one of my problems on one of my databases which is a BIG relief!

Professionally, I like corresponding with you. Can I keep this thread & knock on your door whenever I have a question/problem, or ought I go through the forum?

Dave

[orange]

There are lots of people on the forum who have experience and have solved many problems. I think if you go through the forum you will get a broader set of advice than from any one person.
I tend to follow the forums from time to time and will answer most posts if I feel I have anything even comments or questions to offer. I often respond to PMs as well. If you have specific questions, send them along.

As for your databases, you might consider creating a database to record information about your databases. In your case, you seem to have a few that are quite important and have been changing them. Things like purpose, main features/lessons learned, or keywords. You could have a few forms to interrogate the contents for a variety of
topics/questions.

There are a number of excellent tutorials at dataPig -- these are free video tutorials
http://www.datapigtechnologies.com/AccessMain.htm

Great tutorials at Martin Green's site
http://www.fontstuff.com/access/index.htm

One of the best overviews of RDBMS is
http://forums.aspfree.com/attachment...achmentid=4712

For database design, the first few topics here are excellent
http://www.rogersaccesslibrary.com/forum/topic238.html