A Query For Multiple Cjheck Box Combinations

Feb 1, 2008

All,

I have a problem building a query using multiple check boxes. The situation is as follows:

I have a database to monitor enquiries which come into a central email account. I have devised a system where several check boxes are used to define the nature of the enquiry. Users can tick any number or none of the options to describe the enquiry.

I would like to create a query where users can search the database based on the check boxes which have been ticked. For example, if users want to search for enquiries where a specific three check boxes have been ticked, I would like all of the records where these three boxes are checked to appear, and so on for all different combinations of check boxes ticked.

I've tried a few methods, including creating a seperate form for the query where users check their search criteria in line with the enquiry form, but I can't link this with the original form.

I hope my description adequetly conveys the problem I have!

Any thoughts would be most welcome.

Rob
:confused:

View Replies

A Query For Multiple Check Box Combinations

Feb 1, 2008

View 2 Replies View Related

Multiple Combinations

Jun 8, 2005

Hi Everyone,

I want to perform queries that count the number of customers using combinations of different products.
The data is organised like so:

CustomerID ProductType No of Items
1 A 5
1 B 1
1 C 2
2 D 2
3 B 2
3 B 3

and so on.

There are 5 or 6 product types. I need to be able to produce a table showing all the various combinations. For example, number of customers using JUST product type A and nothing else, number of customers using product type A and product type B, customers using product type A, B and C
and so on.

I think there are around 64 different combinations!
I was wondering if there is a quicker way to do this apart from writing 64 different queries!!

Hope this clear and hope someone can help!

thanks!

Sasha.

View 3 Replies View Related

Creating A Table With All Possible Combinations

Apr 6, 2007

Hi,

I have the following dilemma. I have to make multiple tables with many columns and if I were to type in the data manually it would take me a few weeks. But if I could somehow specify the number of columns(and the number of choices in each column) and then have Access(or Excel) create a table with all the possible combinations it would make my job a LOT easier. For example, let's say I have department stores in multiple cities. I need to create the following table:

CITY DEPT CLOTHING

San Jose Men's Shirts
San Jose Men's Pants
San Jose Men's Shoes
San Jose Women's Shirts
San Jose Women's Pants
San Jose Women's Shoes
Los AngelesMen's Shirts
Los AngelesMen's Pants
Los AngelesMen's Shoes
Los AngelesWomen's Shirts
Los AngelesWomen's Pants
Los AngelesWomen's Shoes

Instead of typing that out manually, is it possible to create some sort of list like:

Cities: San Jose, Los Angeles
Dept: Men's, Women's
Clothing: Shirts, Pants, Shoes

And have Access or Excel create the table with all of the possible combinations?

Thanks for your time.

View 1 Replies View Related

How To Search For Duplicate Combinations

Apr 16, 2012

I need to be able to find duplicate combinations (or, I suppose, permutations). I have four columns in a table that all contain inter values. I want to be able to query so that I can see if there are more than one records that have, say, 12, 28, 5, 14 in columns 1, 2, 3, and 4, respectively.

View 5 Replies View Related

8 Table Need To Design Queries For All Possible Combinations...

May 21, 2007

Hi,

HELP!

I have 8 tables all with a membership number as the unique identifier, I need to find all the possible combinations and then create queries for each combination, is there a quick and easy way to do this? or do I have to sit and define a query for each of the 250ish combinations?!

I'm pulling all the data together using a union query. What I need to do now is to find the combinations using either "is null" or "is not null" criteria (in the queries).

Regards
H.

View 14 Replies View Related

Choosing All Possible Combinations And Adding Their Values

Aug 25, 2004

ok this is weird post but im wonderin how to go about it easily cause i can think of a few ways, dunno if they will work of not, so im asking for ur opinion. I need to find out possible cominations for a salary cap football league based on dollar amounts for the players and points based on last years results, yet it cant go past $100.00...so one possible choice is (QB Tom Brady $22.56 183.50 points). its 1 QB, 2 RB, 3 WR, 1 TE 1 Kicker, 1 DEF team and i have to do all 9 for it to count...so would i have to set up 6 columns, one for each position and have a way of picking 1 of A 2 of B 3 of C 1 of D 1 of E 1 of F for 1 qb, 2 Rb, etc? and have it so sum of values is < $100 and it also must add the points so i can see the best possible combination/most points i can get for $100.00...i know its last years points and this year will be diff but I am just comparing...all i need is an outline i dont need u guys to do it for me i have some experience with access...thanks for anyone who can help

View 2 Replies View Related

Modules & VBA :: Combinations Of Records In A Field That Sum Up To Given Value

Oct 6, 2014

I am looking for a piece of code (either in access or excel) which will return all combinations of records in a particular field (or column of cells) which will sum up to a given value (or a close approximation thereof - ie a margin of error of, say, 2).

In other words, imagine you have a number field with 100 records in it. I would like to know all combinations of those records which add up to a value that the code could specify (eg 223). This would be incredibly valuable to assist in performing reconciliations that I perform in my role.

View 1 Replies View Related

Tables :: Cross Join TABLE - All Combinations

Sep 5, 2014

I am trying to create a cross join or Cartesian product TABLE, not a query.

I am creating a training database. For each and employee and each training event, I want to know - is this event required, who approved it, and when was it completed. The table I envision looks like this:

Code:
EMPLOYEE_ID EVENT_ID REQUIRED APPROVER EVENT_DATE
1 1 Y WPD 9/5/14
1 2 N

I currently have 39 employees and 473 events - 39*473 = 18447 records

I was able to make a cross join query and use make table, but whenever I add a new employee or event, if I update the make table query I will lose all my existing data.

View 3 Replies View Related

Queries :: How To Reduce Down Data Set To Get A List Of All Unique Combinations Of Column

Jul 8, 2014

I am new to Access and am trying to reduce a dataset I am working with to make it managable for Excel. I have three columns which have three unique parameters and one column with numberical content.

Column A Column B Column C Column D
NJ Red Monday 10
NJ Red Tuesday 20
NJ Yellow Wednesday 30
NY Red Monday 35
NY Green Tuesday 40
NY Green Wednesday 60

I want to elimiate column C from the dataset, and sum column D for every unique combination of Column A & Column B. I have figured out how to reduce down the dataset to get a list of all unique combinations of Column A&B,what formula I can use in a query to sum column D on my table for every unique combination of column A&B. This is a simple SUMIF in excel, but my dataset is too large for excel.

View 4 Replies View Related

Multiple Fields Of Multiple Tables To One Table Query Or Report

Apr 12, 2013

I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?

View 1 Replies View Related

Multiple Joins In Multiple Table Search Query

Sep 21, 2004

I am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.

I would like them to be able to query Retailers, Distributors and Products.

The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.

Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products

All retailers have at least one distributor BUT a retailer may or may not have ordered any products.

I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:

Code:

View 5 Replies View Related

Append Query, Multiple Tables To Multiple Tables In Another Database

Nov 29, 2007

Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?

Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...

View 4 Replies View Related

Query To List Multiple Records In A List From Multiple Queries

Jul 11, 2013

I have a DB where you there's 5 tables all linked together by one project ID

tables below

Project , Staff, Asset, allowances, travel, mark up

What I can do is create a new project, then add records to each of the other tables on what different items I require,

i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)

Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.

At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.

View 7 Replies View Related

Multiple-use Query

Sep 26, 2007

Hello Forum,

Right now I have a query that filters data based on unbound fields on a form using criteria in the format of "[Forms]![Form1]![Field1]" and it works fine.

I have multiple forms that all have the unbound fields (named the same on each form). Is there a way to have the query to filter by the data from the active form, so I can have one query that can be used with any form (instead of having a 1:1 query-to-form ratio)?

Thanks in advance!
Rehnahvah

View 1 Replies View Related

Multiple HAVING In Query

Jun 30, 2014

I have the query

SELECT tbl_ImportedRepairs.InvoiceNumber
FROM tbl_ImportedRepairs
WHERE tbl_ImportedRepairs.TrinityBatch IS NULL
GROUP BY tbl_ImportedRepairs.InvoiceNumber
HAVING (((Sum(IIf([BlakeApproval]=False,1,0)))=0));

This query finds line items in a table and gives me the invoice number ONLY if all the line items have been approved.I would like to build on that and say:

Give me Invoice Number if ALL line items have been approved
AND
All The Updated Responsibility code are 1

I am thinking it might be like this

SELECT tbl_ImportedRepairs.InvoiceNumber
FROM tbl_ImportedRepairs
WHERE tbl_ImportedRepairs.TrinityBatch IS NULL
GROUP BY tbl_ImportedRepairs.InvoiceNumber
HAVING (((Sum(IIf([UpdatedResponsibilityCode]=1,1,0)))=0)) AND HAVING (((Sum(IIf([BlakeApproval]=False,1,0)))=0));

But of course that doesnt work and I am getting Syntax error (missing operator) in query expression '(((Sum(IIf(BlakeApproval=Flase,1,0)))=0)) AND'

View 4 Replies View Related

Multiple Tables And 1 Query

Apr 24, 2005

Hello all:

I have multiple tables in my access db, the structure of the tables (4 in all) are exactly the same except that the billing dates are for year 2001 to 2004 i.e. separate table for each of the years.

I don't want to copy the information in these 4 tables into 1 table because I have over 2 million rows in each table. However, I want to be able to run 1 query that will provide information from all 4 tables. E.g. filtering for billing dates between 2001 to 2004 for a particular customer.

How do I join the tables together so that I can run a query on all 4 tables at one time.

Thanks for your input.

Odun

View 2 Replies View Related

Multiple Criteria In Query

May 14, 2005

Hello all:

I need your urgent help with the following:

I am setting up a query in access. I have about 4 criteria for one of the fields called Center, I want the query to do the following:

Return all rows if:
Center is Like 6101*
Center is Like 61HKS56800
Ignore row if Center is Like 6101D*
Ignore row if Center is like 6101SALM01

I put in the following and I am not getting the correct results:

Like "6101*" Or Like "61HKS56800*" And Not Like "6101D*" And Not Like "6101SALM01"

Thanks,
Odun

View 1 Replies View Related

Multiple Parameter Query

Jun 15, 2005

Hi

Apreciate your help on this.

I have a stock control db and when goods are recieved they could have a different references on them e.g. our internal ref, the purchase order ref etc.

I would like to run a query that firsts ask what criteria i want to use to run the query e.g use the int ref or the PO nr. I can set up a simple parameter query in one field but can not find a solution for the db asking what field i want to use before you put in the reference?

many thanks

scott

View 7 Replies View Related

How To Do Multiple IIF / CASE Query

Jul 6, 2005

Hello

I was wondering if anyone knows how to do the following;

I have the number of years staff have been working for the company which is extracted from the startdate.

Based on the number of years service employees are entitiled to extra days leave, so I need to do the following:

IIF([YEAR]<5, 24) and IIF([YEAR] between 5 and 9, 25) and IIF([YEAR] between 10 and 14, 26) and IIF([YEAR] between 15 and 19, 27) and IIF([YEAR] between 20 and 24, 28) and IIF([YEAR] > 25 , 29)

The above syntax does not work...

HOw do I achieve the above? Any suggestion would be gratefully recieved.
Ta :rolleyes:

View 3 Replies View Related

Multiple Choices On A Query.

Aug 3, 2005

Hi All.
I currently have a query that runs a report. The "Project_Status" field of the query is decided from a combo (cboPS) that is on a form (frmReportManager). The user selects the status from the combo and clicks a button to run a report with that status. That is all OK.

What I would like is 4 tick boxes on the form with the 4 project Status (Ongoing, complete, invoiced and quote). The user would then choose the status types he would like in his report, not just the one type as in the combo.
How do I go about referencing the 4 text boxes for the status types, to the one field in the query (Project_Status).

I have tried using And and Or, but with no luck.

Czn anyone help?

Many Thanks.

Frank.

View 14 Replies View Related

Query Multiple Parameters, Help Pls!

Jan 4, 2006

I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...

strSQL = " SELECT NewsClips.RecordNumber, NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE (NewsClips.[NewsSource] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"

Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.

SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment
FROM NewsClips
WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?]))
ORDER BY NewsClips.IssueDate DESC;

Your help will be greatly appericated.

View 3 Replies View Related

Query Based On Multiple LIKE

Feb 14, 2006

Hi.
I have a situation where I have 2 tables A (20K records) and B (2K records).
Each table has a field 'Name'.

I'm trying to figure out a query that will return all the records in table A that have a LIKE match in table B, i.e a the name in table B is somewhere in the record in table A (pattern matching).

So, it looks as tough I would need to step through the records in table B, take the data in the 'Name' field, compare it with a Like '*<whatever>*' match on table A to return any records that contain that data from table B.

Then move onto the next record in table B and compare it again to all of table A and so on.

The result set would then display just the unique rows from table A that had a pattern match with one of the rows in table B.

Hope I'm making myself clear, apologies if not.

Any ideas?

View 3 Replies View Related

Multiple Variables In Query

Mar 21, 2006

I've built a pretty extensive database for work thanks largely to the folks on this board. But I'm stuck. I have a report to track inspections and it works fine, but I'd like to filter it down by AFSC (a coded expression the AF uses to denote career fields, i.e. 2A5x1x is Aircraft Maintenance.) The report's queries come from a couple of sources to retrieve the requirements for the report. For instance that AFSC has to start w/ "2A" and the labor code needs to start w/ "1" and not be "120". No problem. The problem I'm having is that the first 'x' (from the AFSCs below) is based on skill level (can be a 1, 3, 5, 7, or 9) and the last x is sometimes there and somtimes isn't.

For this particular report I've broken down the AFSCs down to three different formats. They are as follows:

2A5x1x (i.e. 2A531B or 2A571)
2A5x3x (i.e. 2A533A, 2A55B, 2A57C)
2A6(or 7)x1(or 2,3,4,5,or 6)

The report is designed to show who is due an inspection and it works fine. But I need to try to make it a little more user friendly so that all the various workcenters can trim it down to view just their career fields. Right now it reads from a query.

Here's the SQL for it.

SELECT qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC AS FilterAFSC, [Employee List Table].[Labor Code] AS FilterLaborCode, qryPEDueUnion.[Inspection Type], Last(qryPEDueUnion.Date) AS LastOfDate
FROM qryPEDueUnion LEFT JOIN [Employee List Table] ON qryPEDueUnion.[Main Assessee] = [Employee List Table].EMP
GROUP BY qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC, [Employee List Table].[Labor Code], qryPEDueUnion.[Inspection Type], Left([AFSC],2), Left([Labor Code],1)
HAVING ((([Employee List Table].[Labor Code])<>120) AND ((qryPEDueUnion.[Inspection Type])="PE") AND ((Last(qryPEDueUnion.Date)) Between DateAdd("m",-19,Now()) And DateAdd("m",-15,Now())) AND ((Left([AFSC],2))="2A") AND ((Left([Labor Code],1))=1))
ORDER BY Last(qryPEDueUnion.Date);

Let me know if you need more info. I'm new to posting on this board and haven't quite found out how to attach files....Plus this db is pretty big and I'd have to fool around w/ a copy of it first to make it small enough to attach.

View 3 Replies View Related

Query From Multiple Tables

Mar 31, 2006

i have an excel worksheet with 24 similar sheets : a,b,c,d, aso
the sheets contains similar data: nr, firm, invoice, sum, payment date, etc
i linked them to access creating tables : a,b,c,d, aso
i must create a query based on them to sort the data in the tables ascending by the due payment date (query based on multiple tables)

i've done this for 2 tables:

SELECT *
FROM a, b
WHERE (((a.SCADENTA)<=[due payment date]) And ((b.SCADENTA)<=[due payment date]));

but it multiplies the data by 2 or many times in the resulting table.
what is wrong?

View 3 Replies View Related

Multiple Source Query

Jun 26, 2006

I'm in the process of learning to use Access and I would appreciate any help that can be provided. The problem that I am facing is that I have data in three different columns, and this cannot be changed. The database that I am creating links to another database to retrieve a table. This table contains two columns of data that are essentially the same for my purposes. I have also created a new table in my database which houses new data and also this will be the location where new records are be stored. Ideally what I'd like to have is a single table with the information from the three sources stored in a single column that updates as the linked table is changed. Is there a way to do this with a query? or should I be looking into another method? Any help is appreciated. Thanks.

Would the append option be appropriate?

View 3 Replies View Related