MS Access Question

Factual Questions
1

If there any way to get a table like this:


(Customer) (Product)
John Smith  Computer
John Smith  Mouse
John Smith  Keyboard

into a query like this:


(Customer) (Product)
John Smith  Computer, Mouse, Keyboard

for any amount of products, for many customers? Sort of a like a SUM for text fields.

2

Your original table isn’t normalized, which makes it rather difficult. You should have a seperate table for customers and products, making the join trivial.

3

I don’t know Access, I’m a Filemaker person, but sometimes the same concepts apply even if the implementation is a little different.

In Filemaker, I’d create a relationship between the file and itself (a self-join relationship) based on Customer Name. Then I’d drag a portal onto the layout and select the self-join relationship I’d just created, and drag the field Product onto the portal. Enter browse mode and bingo, a list of all products for which the current customer is the customer.

That would look like this:

John Smith

Computer
Mouse
keyboard

In FileMaker it’s a little less convenient if you want it to appear as you originally typed it (with the values in a horizontal row to the side of the customer name rather than arrayed below the customer name) and although I know how to make it do that, it is less likely to have any bearing on how to do so in Accessl.

4

Realize that I am a self-taught MS Access user, and as such, I do not know what “trivial” or “normalized” mean.

5

First, you decide which is the crucial, central data. In this case, it would seem to be the name, “John Smith”. You then have a table centering around name. It will undoubtedly be useful to assign some kind of code to that, for instance, Social Security Number, or some assigned Employee code. Use this as the “key” field. You really, really dont want to use “John Smith”.

In the table “Equipment” track John Smiths number (say, “001”) relative to his equipment thus

001 Mouse
001 Computer
001 Mouse pad

etc. etc. This table will center around the number, but not as a “key” field because you need to be able to repeat the number.

Look for Peach.Ease.Lsoft.com. There is an Access board there that is very helpful, and many of the denizens are eager to help out the neophyte.

6

Sorry…normalization is the process of organizing your data such that you eliminate redundancy. This is the whole purpose of relational databases.

In your current setup, you have one table which includes the person and the product. Since the same person can be associated with more than one product, you have to enter that person’s name several times; this is redundant, and called a “one to many relationship.” The proper way to do it would be to have one table full of people, with a number for each person, and one table full of products, which a number for each product. You could then have a third table relating each product with each person, for example:


Products:
ID Name
1. Keyboard
2. Mouse
3. Computer

People:
ID Name
1. John Smith
2. Bob Jones

Now, to keep track of who has what, you would make a table like this:


Person  Product
1       1
1       2
2       3

You could then to a simple select on the third table to see that John Smith has a keyboard and a mouse, and Bob Jones has a computer. (Look up “joins” in your Access manual.)

Hope this helps.

7

OK, yes, I understand the concept of a relational database. I’ve been working with this stuff for a while now, just havne’t taken any courses. But you’ve missed my question.

I have my SELECT query. It looks like this:


(Customer) (Product)
John Smith  Computer
John Smith  Mouse
John Smith  Keyboard
Jack Wilson Computer
Jack Wilson Modem
etc...

I want it to look like this:


(Customer)   (Products)
John Smith   Computer, Mouse, Keyboard
Jack Wilson  Computer, Modem
8

This would be simple if Access had an aggregate function that could concatenate text similar to the way the Sum function adds values together. Unfortunately, Access 97 (the version I have) doesn’t have such a function. I wish it did. Does anyone know if any SQL implementation or a later version of access has such a function?

You’ll need more than an Access query to accomplish this. It could be programed in Visual Basic for Access. It could even be done using spreadsheet calculations.

Data normalization won’t help you here but it is worth learning.

9

You can do this in a report using code, but I can’t think of a way using just SQL. You’re kind of working against the grain of the whole relational thing. Would a report work as well?

10

How? I’ve been working with VB a bit…

And no, reports will not do. I need it in a query so that I can add HTML code, export it to a TXT file and include it in a SHTML file. Got that? :smiley:

(Note: I realize reports can be exported to HTML, but that will not work.)

11
      • The Visual Basic to do this is fairly hairy stuff; not a short answer. Something that kind of gets what you want is to make a query on the database that selects all the fields, and then use Excel to import the Access query/data into a spreadsheet. This will “copy” the original data arrangement into a spreadsheet the same way it is in the database, but then in Excel you can use the data in a pivot table with the names on the left and the products on top of the pivot table. If all the product options are checked to display, Excel gives each different product its own column). You get final results that look something like this:

------------------------------------------------------
Name          Keyboard     Monitor      Mouse     Computer
John Doe          1                       1
Anne Smith                     1          1            1
Jim Baker         1            1
Mary Jones                                1            1
--------------------------------------------------------
  • That is, the number in the product column is the number of *that item[/i that the person has. -I couldn’t find any easy way to get it to put the name of the item into the table, just the number.
  • You should be able to export the spreadsheet all the same ways you can export the database/query results. - MC
12
      • The problem with that you want to do is, you want the database to take one single column of many items and separate it into many columns of single items. For spreadsheet pivot tables that’s easy because that’s what they’re for. I just finished an advanced Access course (Final grade:97%-yay!) and I don’t ever remember seeing this capability mentioned anywhere in Access. - MC
13

I don’t know how pivot tables work, but I do know that they are suported by MS Access (I have 97 & 2000). Look up pivot tables in the help for more information. Perhaps MC can explain Pivot tables further?

Hope this helps, as the others have said though you would do much better to reorgainise your data in to separate tables.

Good Luck

14

Actually, the VB Code is pretty simple. Using VBScript and ADO on a web page, this would be one way to do it:


Const adOD = 2
Const adLO = 3
Const adCTD = 512

Dim CN
Dim RSTable
Dim RSQuery
Dim SQL

Dim Customer
Dim Product

Set CN = Server.CreateObject("ADODB.Connection")
Set RSTable = Server.CreateObject("ADODB.Recordset")
Set RSQuery = Server.CreateObject("ADODB.Recordset")

CN.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=MyData.mdb"

RSTable.Open "Customers", CN, adOD, adLO, adCTD
RSTable.Index = "indCustomer"

Do Until RSTable.EOF
   Customer = RSTable.Fields("Customer").Value
   Response.Write Customer & "   "
   SQL = "Select * From [Customers] Where [Customer] = '" _
         & Customer & "'Order By [Product]"
   RSQuery.Open SQL, CN, adOD, adLO, adCTD
   Do Until RSQuery.EOF
      Product = RSQuery.Fields("Product").Value
      Response.Write ", " & Product
      RSQuery.MoveNext
   Loop
   RSQuery.Close
   RSTable.MoveNext
Loop

RSTable.Close
CN.Close

Set RSQuery = Nothing
Set RSTable = Nothing
Set CN = Nothing
15
    • A pivot table allows you to sort a spreadsheet based on different columns or rows of data. You select the “rows” and “columns” fields and it builds a spreadsheet with as many rows x columns as different values in each, and then fills in the rest of the table accurately, based on the rows and columns. ~ A crosstab query does basically the same thing, for a database dataset.
  • You can do a crosstab query and get pretty close, but not quite:
    (Office 2K) I used a table named CustProd, with fields named Customer and Product. You start with a regular query in design view, and go up to the toolbar and switch it to a crosstab query (there’s a button with several types on a pop-up menu).

On the design grid, the first query column reads like this:
Field: Product
Table: Custprod
Total: Group By (the default value)
Crosstab: Value
Sort (blank)
Criteria (blank)

The second field reads like this:
Field: Customer Name
Table: Custprod
Total: Group By (default value)
Crosstab: Column Heading
Sort: (blank)
Criteria: (blank)

The third field reads like this:
Field: Product
Table: Custprod
Total: Expression
Crosstab: Row Heading
Sort: (blank)
Criteria: (blank)

  • The problem is that I don’t know if you can hide the first field in the results that you want. You can adjust the first column width to zero and hide it on-screen, but I don’t know that doing so keeps it out of any conversion results. - MC
16

As a general principal, SQL (queries) is used to retrieve data, reports and forms are use to present the data. You’re trying to get SQL to do the presentation. I think your best bet for an answer, if there is one, is here.

17

If you can’t use Libertarian’s method of an ASP page doing all the work, and you absolutely have to use a query, here’s an approach you can use. Create a calculated field in your query, have that field call a custom function created in a VB module, pass in the Customer name (or Customer ID if normalized) into the custom function, then use it to walk a recordset and have the function return the created string. You can use Lib’s code as a guide, but you don’t need the Index or the outer Do Loop.

Let me know if this doesn’t make sense.

18

Here’s my stab at it. I should have created this code a long time ago rather than messing with Excel.

Assuming that you have a table named “test” with the fields “Product” and “Customer”, use the following query:


SELECT test.Customer, CommaList("test","Product","Customer",[Customer]) AS Expr1
FROM test GROUP BY test.Customer;

This query calls the CommaList function. The code for this function is as follows.


'--------------------------------------------------------------------------
' CommaList returns a comma-delimited list of values associated with
' a key value from multiple records in a Microsoft Access table.
' tableName - the table containing the values
' commaField - the field whose values will be returned
' keyField - the field where the key value is found
' keyValue - the key value
'--------------------------------------------------------------------------
Public Function CommaList(tableName, commaField, keyField, keyValue) As String

    Dim dbs As Database, rst As Recordset
    Dim SqlString As String

    'Format strings with " and dates with #
    If IsDate(keyValue) Then
        keyValue = "#" & keyValue & "#"
    ElseIf Not (IsNumeric(keyValue)) Then
        keyValue = """" & keyValue & """"
    End If

    'Get commaField from every record where keyField = keyValue
    SqlString = "SELECT " & commaField & " FROM " & tableName & _
                " WHERE (" & keyField & " = " & keyValue & ");"
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(SqlString, dbOpenForwardOnly)

    'Put the first commaField value into CommaList
    CommaList = rst.Fields(0)

    'Step through any additional commaFields and append their values
    rst.MoveNext
    Do While Not rst.EOF
        CommaList = CommaList & ", " & rst.Fields(0)
        rst.MoveNext
    Loop

End Function
19

Similar to MC’s answer:

Query1:
Crosstab query, based on the Name/Product table, with
Name with Total=“GroupBy” and Crosstab=“RowHeading”
Product with “GroupBy” and “ColumnHeading”
Product with “Max” and “Value”

Query2:
Simple query based on Query1, with
Name as the first column
“Products” as the second column with the formula
Products: Computer & IIF(Computer<>"",",","") & _
Mouse & IIF(Mouse<>"",",","") & _
Keyboard & IIF(Keyboard<>"",",","")

      etc., for as many products as you want to
      specify. You *must* specify every product in
      the formula to make this work. It would also
      behoove you to restrict entry in the Product
      field of the table to just these values. If
      you got too many products, you might prefer to
      create a function to concatenate, but for a
      limited number, this would probably be easiest.
20

I don’t wanna sound like a total idiot, but can you please change your coding so that… "Customers becomes “Website” and “Product” becomes “Webmaster”. I tried, but I’m getting a headache just looking at it. Thank you.