Search results

Thanks Paul. I forgot one small part of this equation. My date field is InputDate. While I want to group by month, I need to filter by InputDate, i.e., HAVING InputDate BETWEEN 1/1/04 and 6/1/04. When I do that, I get the infamous aggregate function error: You tried to execute a query that...

I am guessing this has been done many times, but can't quite get it right. Each day I input to the db the date (myDate), the number of items produced (Items) on that date and the number of hours worked on that date (myHours). What I want to do is create a query that will bring back the total...

I am working with a what I feel is a poorly designed database. A typical record has a productID and then seven different colors. Each color is a text field with a value of "Yes" or "No". I am trying to write a query that given the ID number, will return only the fields from that record with a...

I am trying to get creative here. Maybe you guys will have a better (easier) way to do this. I am actually trying to do this on a .ASP page, but my question I think relates to the query itself. I am trying to get not the top 10 newest records but the second 10 newest. So I am using this SQL...

Can I combine the two in my SQL to form one valid date string??? Something like: SELECT * From Table WHERE DateSerial(mymonthfield, mydayfield, myyearfield)&TimeSerial(myhourfield, myminutefield)=Now()

In my db, the parts of a date are broken into 5 separate numeric fields, namely, month, day, year, hour, minutes. I need to retrieve records with a date after today. Is there a way I can group those fields together to form a valid date that I can then compare to today's date? Was wondering if...

I have several hundred records in a db. My primary key is simply an ID field that is autonumber. I want to add a second ID field that will be similar except I need it to restart at 1 when the year changes. So if there are 1200 records for 2003, they will have a secondary ID somewhere between...

I need to add a field for Invoice number. I want the field to be the year the record is submitted followed by a dash and then the ID of the record (AutoNumber field). Can I put such a field right in my table or would it have to be part of a query? How would I go about formatting the field?

Exactly! Thanks Pat.

Re: you can give this a try Thanks Craw. Both tables have a primary key, but I can't join on them. The primary key in the video table is ID. There is a primary key in the Reviews table titled ID too, but the two don't relate. The Reviews table also has a field titled VideoID that contains...

Smarter than I used to be, but not smart enough yet. I have two tables. One lists videos. The second contains people's ratings of those videos. I need a query that will display all videos and their average rating. I managed to get one that almost works. My snag came because some videos...

I'm stumped on this one. My db has a birthday field. However, the b-days are stored as text in YYYYMMDD format. I need to write a query that will return all records where the birthdate field is within 15 days, before or after the current date. So, I really only need to compare today's month...

Found the answer by accident. I figured all relationships showed in the Relationships Window by default. Guess not. Once I right-clicked in the window and chose the "Show All" option, I was able to see the relationships I needed to delete. I am guessing that the ones created automatically by...

I have a column in a table that pulls data from a lookup table. I no longer need the column, but can't seem to delete it. When I try, I get this message: You can't delete the field 'Status.' It is part of one or more relationships. If you want to delete this field, first delete its...

Outstanding. Thanks Pat.

I want to create a database to track my extensive CD collection. What I am wondering is the best way to design the db. For example, I want to record the Artist, CD Title, each track and perhaps the release date. The part that confuses me, is how best to capture the tracks since the number of...

I must admit I have never a written a function, can you give me an example of what this one would be?

I need a count of total items from three columns of my db. The columns are Col_A, Col_B and Col_C. Some of the items in the columns are single items, but others are comma-delimited strings. Is it possible to construct a query that will count each item in the string? If so, what would the...

Curiosity question. If I use EMP's example without "ABS" the values all show as negative numbers. Why is that?

As my 13-year old would say, "You guys rock!" Most of the database stuff I do is to run my ASP pages. I frequent a FrontPage forum for most everything, but whenever I get stuck on a query, I know I can get the answer here. Thanks. FYI - I used EMP's example for my ASP page. I will try...