Search results for query: *

The first reply was a good try, but, nonetheless incorrect. If you read the post carefully, and examine the data structures carefully, you will discover that i) the poster has asked that the results be grouped by name and value "and" include a sum of values for each name and ii) that...

Here is a symptom I currently have which I have previously encountered several times using several brands of desktop connected to KVM (keyboard/video/mouse) switches. The one constant is that it only happens to either Windows 2000 Pro or Windows 2000 Server (both running service pack 3). In...

Hi Paul and Kirsty, Before we go any further, I'd like to make sure I understand your requirement. I do understand that you've got a table structure which allows for duplication of title across multiple first line writers ... no problem ... I also assume that one of the fields in your table is...

You're on the right track, but, selecting the 6 right-most characters won't work since, when applied to "STREET NAME BLDG 123", it has incorrectly determined that the letter "D" is the cut-off point rather than the letter "B". The key is to look for the starting...

This one's fairly simple ... I've added a date-effective tax rate table too in case you find it useful. 1) Parent invoice table: tblInvoice (ServiceRecordID, InvoiceDate) - Primary key: ServiceRecordID Sample data: ServiceRecordId InvoiceDate =========== =========== 1...

No problem ... this will work just fine: Using table tblGender (Site, Gender, GenderCount): TRANSFORM (Sum(Switch([Gender]="F",[GenderCount],True,0))/Sum([GenderCount]))*100 AS FemalePercent SELECT "Female %" AS GenderF FROM tblGender GROUP BY "Female %" PIVOT...

Hi Dyer, If I understand your question correctly, I have two suggestions, one being to go with Rudy's solution, the other with the subroutine I provided. We know that your tblDefects table currently contains Team, DefectDate and DefectCount. Next, can we confirm that you can or cannot have...

In answer to your first question, yes, BusinessUnitID is a number (long integer) in the tblCostCenter table. By default, any newly created number field is created as a long integert, however, verify in the field properties in table design view. It needs to be long integer to be...

Hi Rudy, Your reasoning is sound ... I do find it interesting though that your experience has typically allowed you the ability to JOIN via a UNIQUE indentifier. During the many years I've worked with SQL in real-world business environments, "date" fields are typically NOT part of...

Good idea Rudy ... I had tried this too, but, unfortunately it does not correctly apply the TOP n value to the recordset results. Given tblDefects records: Team DefectDate DefectCount A 1/1/02 1 A 1/1/02 1 A 1/2/02 1 B 1/1/02...

Sorry I took so long to reply ... I was away for a few days. Okay, I understand your new dilemna ... the way to solve this one is a little bit more complicated since the "TOP n" SQL qualifier cannot be "sub-assigned" to a GROUP ... it's assigned to the entire recordset. So...

Stephan, I posted a solution for you yesterday for this exact same problem. You responded that it worked perfectly. What's up with posting the same post again. We're all here to help everyone with their problems, not waste our time solving the same problem for the same person several times...

It would be helpful if you divulged more of what the environment and processes were that are involved here. In their absence, my guess is that you're wanting to provide a quick lookup mechanism for your users to see a list of valid cost centers that they can charge to if they belong to a...

First, let me list the assumptions I need to make before I give you one solution that you might want to consider: ASSUMPTIONS: 1) Create a lookup table entitled tblBusinessUnit with two fields: BusinessUnitID (AutoNumber) and BusinessUnitName (Text) NOTE: The primary key is BusinessUnitID...

Just a few comments: You need to seriously look at redesigning your solution to this problem ... your use of a function to provide for this type of comprehensive data validation is sinful at best ... look at creating a two-column table having BusinessUnit and CostCenter as the primary key and...

If I'm understanding you correctly, you're wondering if this solution is dynamic i.e. if the user enters any value into the tblProduct table for the Product field, will this solution handle this new value and create new columns as necessary? The answer is yes. By default, a crosstab query will...

Hi Stefan, Here's a trick I've used in many similar situations across the years ... just substitute the field names in your table for the one's I've used here: TRANSFORM Sum(tblProduct_1.ProductTotal) AS SumOfProductTotal SELECT tblProduct.ProductDate FROM tblProduct INNER JOIN tblProduct AS...

Hi Starbird, The solution is simple. The error in your SQL is that you only allow for two conditions in your WHERE clause ... records which are in the date range or records which have no date specified ... in doing so, you have filtered "out" those records with a date outside your...

The only question I had from your post is how the street numbers were stored in your table. I've assumed that the primary key to your table is the combination of District, Street_Name and Street_No and furthermore that Street_No is numeric - I assume that you only displayed your comma delimited...

Here are a couple of questions I have that might help solve this one: 1) Are you running this SQL as a saved Query ? If so, can you view the query in design view as well as SQL view ? 2) Is Items a local Access table and, if not, is it a linked Access table or a linked ODBC datasource e.g...