Finding records in categories

[Rozza]

Hi,

I have a main transaction table which is related to a department table via a department_id field. The department table is then related to a workgroup table via a group_id field and then the workgroup table is related to a business table via a business_id. This then enables you to search by different business id's to identify transactions made in those businesses. The department_id field is recorded as a single number in the transaction table, this then relates to a bunch of departments in the dept table, which is related/grouped in the workgroup table which is related to business table. So you can search for business id 1 for example, and it will find all relevant department id's associated with that business.
There are 7 business id's in the business table relating to the following businesses:

Hair
Beauty
SunBed
.
.
.
.
etc

What I want to be able to do is to find people who have made a transaction in more than one business eg I want to be able to find someone who has spent in Hair , Beauty and Sunbed. At the moment I can search by one business by running a query and searching for a single businees id in the business_id field, but i need to search by more than one busines_id. How do i do it??


Cheers


Paul

 

[vb6novice]

Paul,

Your description of the table relationships is not clear. Tell me if this is right.

There are 7 businesses.
Each business has multiple workgroups.
Each workgroup has multiple departments.
Each department has multiple transactions.

That's what you described. Is it really that way.

 

[Rozza]

Correct!!

I knew ther was an easier way to write it down on paper!!!

Cheers

Paul

 

[vb6novice]

Ok, Paul.

Now we are getting some where. Perhaps it would be helpful if you gave examples of the workgroups and departments also. I'm trying to envision what workgroups and departments you would have in the several businesses. For Hair I can think of Workgroups called Washing, Cutting, Curling, Straightening, Coloring. But I would have also thought those to be good department names, and also good transaction names, so I need more info about your table fields. Please give an end to end example of one business, its workgroups, the departments for one of those workgroups, and the possible transactions for one of those departments.

 

[Rozza]

OK, I will post a complete example in the morning when I get back to work.

Thanks


Paul

 

[Rozza]

Hi,

The example is as follows:

Business_id = 1 (Hair Business) Stored in the Business Table

Workgroups within this business (Stored in the Workgroup Table):

Group_id = 38 (styling)
Group_id = 54 (colour)
Group_id = 55 (perm)
Group_id = 56 (treatment)
Group_id = 78 (general hair)

Departments within these workgroups (Stored in the Departments Table):

Group_id = 38:
Dept_id = 42 (cut/finish)
Dept_id = 150 (finish)
Dept_id = 151 (restyle)
Dept_id = 152 (extensions)
Dept_id = 153 (styling misc)

Group_id = 54:
Dept_id = 154 (tint)
Dept_id = 155 (highlights)
Dept_id = 156 (semi permanent)
Dept_id = 157 (tsg)
Dept_id = 158 (colour misc)

Group_id = 55:
Dept_id = 159 (perm)

Group_id = 56:
Dept_id = 160 (treatment)

Group_id = 78:
Dept_id = 207 (general hair)

The listing above is for one business only. Each transaction then has one department number stored in the department_id field within the transaction table. I need to be able to find people who have made transactions in more than one business. At the moment I can find, for example, people who have made transactions in the hair business by searching for business_id 1, but I can’t find people who have made transactions in the hair business “and” the beauty business etc.

 

[vb6novice]

Paul,

With the table relationships you have described, I don't know if it is possible to create a single query that returns ONLY the clients that have spent in multiple businesses, but it is possible to produce a series of queries that returns all the businesses that anyone has spent in, then you could run a query that returns only the clients that have spent in more than one department.

Query 1 is called qry1
SELECT tblbusinesses.businessnumber, tblTransactions.customer, tblbusinesses.[business name]
FROM ((tblbusinesses INNER JOIN tblworkgroups ON tblbusinesses.businessnumber = tblworkgroups.businessnumber) INNER JOIN tblDepartments ON tblworkgroups.workgroupnumber = tblDepartments.workgroup) INNER JOIN tblTransactions ON tblDepartments.departmentnumber = tblTransactions.departmentnumber
GROUP BY tblbusinesses.businessnumber, tblTransactions.customer, tblbusinesses.[business name]
ORDER BY tblTransactions.customer;

Query 2 is called qry2

SELECT qry1.customer
FROM qry1
GROUP BY qry1.customer
HAVING (((Count(qry1.businessnumber))>1));

Of course, you'll have to substitute you table names and field names for what's in these query designs.

That would return the names of any customer who has spent in more than one business, but it will not give the names of those businesses. That will have to be looked upin a different query individually by providing the customer names as criteria in on the transaction table.


I have a comment on your numbering scheme.
Your table relationships seem sound. The use of 1 digit for a business, two digits for a workgroup, and three digits for a department is good. I would, however, suggest something a little different if the values you gave are not set in stone, and that is that the workgroups start with the same digit as the business, e.g. styling = 11, colour = 12, perm = 13, treatment = 14, general hair = 15.

Then the departments would follow suit. In styling (workgroup = 11), the departments would be cut/finish = 111, finish = 112, restyle = 113, extensions = 114, styling misc = 115.

Likewise, business 2 workgroups would be 21, 22, 23, etc. and the deaprtments in workgroup 21 would be 211, 212, 213, and so on.

This type of numbering would allow you to pick off the first digit of any department listed in a transaction to get to the business, rather than having to go through all the relationships and double queries to get the result you want.

Hope this helps.

 

[Rozza]

Hi vb6novice,
Thanks for taking the time to reply, i'm sorry to ask stupid questions, but I don't understand sql. Can you please explain the two queries so that I can create them using the "Access" query builder.

Best Regards

Paul

 

[vb6novice]

Paul,

The two SQL statements I provided were copied from the SQL view of Access query builder after I created some tables and fields based on what you described.

To get queries from those statements, do the following:

From the Database window, select Queries, then Select New.
When the the query builder opens, choose Design View and click OK.
When you are prompted to add tables, click Close.
On the Query Design Menu bar, click SQL. The SQL window will appear with the word "SELECT:" highlighted.
Copy the first SQL statement above and paste it in the SQL view.
Substitute your table names for mine (I tried to make them obvious, such as tblbusinesses for the businesses table, etc.) and change the field names (that's the part after the dot), for example, the field name in "tblworkgroups.workgroupnumber" is workgroupnumber.
If your field names have spaces in them, enclose them in brackets, such as [work group number].

Once you have made all the changes, click the design view button on the Query Design toolbar and it will go to the Query Design grid, or it will give you a message box telling you about a Syntax error if something was not changed correctly. You must be careful with the changes because if you mis-type a table name or a field name, Access will asume you want to create a new field and will show up as Expr1: followed by the mis-spelled table or field name.

Save the new query as you normally do.

Repeat all the above for the second query.

If it still doesn't work, come back with the exact names of your tables and the names of the related fields and I'll build the exact query string to paste in.

 

[Rozza]

I'm not sure that this is going to do what I want as I need to be able to see which customers have made a transaction in the following businesses:

Hair only
Hair and Beauty no Retail
Hair and Beaty and Retail
Beauty and Retail no Hair
Hair and Retail no Beauty
Beauty Only
Retail Only

From what I understand, the queries you have written will only find customers that have made transactions in more than one business, but won't find specifically which businesses those are.
I do appreciate your work so far, and I think we're nearly there, hopefully I can have this resolved soon.

Cheers

Paul

 

[vb6novice]

Paul,

You are correct about the queries I have offered, they will only find customers which have made sactions in more than one business, but that is what you stated as you original goal. Now that you have stated what you really wanted to get, things are different.


Going back to your original post, you said you could search and get results for a single business ID but wanted to do it for multiple business ID's. Did you set that criteria in the query design grid by putting a 1 in the business ID field criteria? If so, and if all you want is to put multiple business ID's in the criteria, then I have led you down a path you were not prepared to go. If all you want is to be able to set the criteria to business 1, or 1 and 2, or 1 and 3, or 2 and 3, etc., then all you need to do in your query design grid is put the numbers you are looking for separated by the word 'Or'. For example, if you want businesses 1 and 2, your criteria would read "1 Or 2" (without the quotes). If you want 1 and 3 and 5, you criteria is "1 Or 3 Or 5".

Since I do not know all the fields you have in each table, I don't know what result you are going to get. If you tables are structured like you say, then you are likely to get every instance of transaction in each of the business listed, but it will not give you ONLY the customers that have spent in multiple businesses. It will give you ALL the people that have spent in ANY of the businesses you listed. It will be up to you to determine if any of the customers is represented in more than one business. The easiest way to dtermine that is to sort your query by customer.

If you want results more granular than that, you will have to resort to some sort of code processing.

Let me know if I am off track.

 

[Rozza]

Hi vb6novice,
I'm sorry if I was unclear about the problem at the start of the thread. What i want is to find "customers" who have made transactions in businesses such as:

Hair only
Hair and Beauty
Hair and Beaty and Retail

From what you explained in your last post, this will only find customers who may have spent in one instance of a business but not all the businesses listed above, and so I think it makes the problem more complicated.

Cheers

Paul

 

[vistor]

Wouldn't you want to Build relationship tables between your tables?

tblRel_Business_Customer
Business_id = Primary key
Customer_id = Primary key

Then create a Select query.
Pull both fields from the tblRel_Business_Customer
Pull Customer_Name from the Customer table
Pull Bussiness_Name fromt the Business table

 

[vb6novice]

There are several major pieces of information I don't have that are making it difficult to solve your problem. Some of those peices are:

Are you using controls on a form to set which businesses should be included in the query result or are you working only in a query design grid?

What type of output do you want for the results? Will you just print out query results or will this go into a structured report with sortings and groupings?

At this point, if you are willing, it would be most helpful to me if you would send me your database so I can see how you are trying to get the results. That way I can offer a better solution. You can send it to me at bkoran@newbreed.com




How are you getting the information on which business are to