How Do I Query Any Combination Of 7 Fields - 4cbo And 3 Txt

Jan 18, 2006

Hi guys, bit of a tricky one here and I can't seem to find satisfactory answers anywhere so hoping you can help.

I have a form which contains 7 parameters. 4 combo boxes and 3 text fields. The user selects entries on any of the combo boxes or types text in any of the text fields. They do not have to fill in all 7, it could be any combination of the 7.

Under this form is a sub form which will display results according to which fields are set above but how do I code my query to accept any of the above?

My main form is called Orders_Queried and my sub form is called Order_Query_Results

The 7 fields are:

cboQueriedCustomer
cboQueriedContact
cboOrder_Complete
cboProduct
txtQueriedCustRef
txtDescription
txtTypeColourSize

The first part of my query is:

SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date], Order_Details.[Item Number], Order_Details.[Item Type], Order_Details.Description, Order_Details.Type_Colour_Size, Order_Details.Quantity, Order_Details.Price, Order_Details.Denomination, Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber
WHERE


Now obviously my query needs to be set to show results where fields are equal to the combo box entries, but for the txt fields then the results need to be like. But how do I code this so it will display the results no matter which combination of parameters are set.

View Replies


ADVERTISEMENT

Reports :: Sorting By Boolean Fields - Print All Different Combination

Feb 20, 2015

I have 3 fields Yes/No.

A B C

To be in order it is necessary that: A , B and C = Yes.

I would want to print all different combinations :

A = no et B et C = Yes
Then
A: Missing

Nom, prenom et adresse.
.....
.....

A = Yes, B=No and C = No
Then
B and C missing :

Nom, prenom et adresse.
......
.......
Etc.....

View 1 Replies View Related

Tables :: More Than One Unique Values - Prevent Entry Of Duplicate Combination Of Fields

Nov 4, 2014

1. I have a database (see attached) with three tables all of them with the same fields. The first three are numbers (InCo_No, Proto_No, Year_No). Each of these fields (numbers) can be the same in the other table(eg. Year_No), but the combination of the three cannot be.

How can I prevent the entry of a duplicate combination of these three fields?

2. I want to have a form to fill the three tables separately, depending the values in the other fields.

How can I do this?

View 3 Replies View Related

Query Returns Every Possible Combination

Mar 5, 2008

Hi, i'm hoping someone here might be able to help me. I have come to a bit of a dead end with a database application i am working on.

The database is a delivery newspaper management system for a newsagents. Basically i need it to link customers to the paper they wish to receive, organise them into delivery rounds, and produce a bill for each customer based on the newspaper they get delivered.

The problem I am currently having is when it comes to the billing.
I have:
a table that stores customer details
a table that stores newspaper details (inc. price)
a table that stores the customerID and then the NewspaperID for each day of the week. (since not all customers get a paper everyday of the week)

If a customer gets two papers then two entries are made in the requirements table under there CustomerID)

Now to fetch the price of the newspaper the customer is down to receive each day and then add these all together is where i have been having trouble.

After many different attempts the solution i am currently using is this. I have a query for each day of the week. Each query take the customerID from the requirements table, then the newspaper price from the table storing newspaper details.
The problem here is that the query returns the CustomerID next to every newspaper in the database. I found the solution was this. I added the newspaperId from the newspaper details table and in the criteria stated "[tableRequirements].[NewspaperID]" And this worked perfectly.
Untill i added a customer who gets two papers. As far as i can tell the query is returning all the possible combinations of which newspaper the customer should get.

Any help with this would be greatly appreciated - thanks in advance.

View 1 Replies View Related

Combination Set

May 13, 2005

Hi,

I am designing a database that can sell items 'individually' and by the 'case' e.g 12 items that are sold at a discount price.

For example, a 'case' or 'combination-set' may either have 4 x 3 items, 3 x 4 items or 2 x 6 items etc. Each case would have a 'case-code' and 'case-price'.

So far I have an 'OrderID', and a 'ProductID' table that are linked into a one-to-many relationship to a 'OrderDetailID' table. This part works fine when selling individually items...

How do I link in the items together to sell by the 'case' (12 items) that is a 'combination set' as I listed above.

What extra table(s) do I need?

Thanks in advance for any help.
miranda65.

View 3 Replies View Related

Unique Combination

Jul 31, 2007

I have thought about this for days now (and nights) and I have simplified my problem below. (I even bought a book)
I have three tables: 1, 2 and 3.
Table 1 = a, b and c
Table 2 = x, y and z
Table 3 combines table 1 and table 2; ax, ay, az, bx, by, bz, etc.
Table 1 and Table 2 are both One to Many with Table 3.
The Table 1 field and Table 2 field are a ‘unique’ index combination.
My Problem:
It would be very handy to produce a list of the ‘unique’ index combinations that are not in Table 3. That is, the ‘unique’ index combinations that aren’t used yet.
You could do this by trial and error, but is there a Query you can run that will give you a result that you could then Append?
An Unmatched Query on the Table 3 / Table 1 contents will point out a complete lack of either a, b or c as will the same Query on Table 2 for missing x, y or z.
A query with the two non-linked tables will give every combination, but is this the best way to do it? It works by default when you use it to append table 3, but is it the best way to do it?

View 14 Replies View Related

Combination Of Data From Two Field

Nov 22, 2006

Hi all
I have a table with data like
Field 1 = Ref_No (IM1006/0548)
Field 2 = Loc (CBUS)

I want to combine these two fields in a separate field

Must look like this
Field LocNo = CBUS0548

View 2 Replies View Related

Modules & VBA :: Combination Of Two Queries

Jun 11, 2015

Is it possible to do combination of these two queries?

SELECT * FROM tblGoraZleceniaNowaWyceny WHERE [id_wycena_pre] = Forms!frmWycenyObszarroboczy!ID_wycena_pre[/CODE]

and

Code:
SELECT [Query1].[NumerArkusza], [Query1].[nazwa], [Query1].[id_wycena_pre]
FROM Query1
WHERE ((([Query1].[numerarkusza]) In (SELECT [numerarkusza] FROM [Query1] As Tmp
GROUP BY [numerarkusza]
HAVING Count(*)>1 )));

I need to do this to work with Recordset.

View 11 Replies View Related

Importing Combination Text And Numbers

Oct 13, 2005

I am trying to import a field into a data, which is either a number, a number and text or just text. Within Excel the format has been set to text. The data type for the field in Access has been set to text and yet for those records which are only numbers when imported the record is shown as a number in scientific format. Does anyone have any ideas how I can get the number to be a number in its full format?

View 2 Replies View Related

OpenArgs And Find Record Combination

Dec 9, 2014

The access website says there is a way to use the open arg in combination with the FindRecord method to open a form up to a specified client name. I'm trying to have a macro that asks the user what student they are looking for and then takes them to the form with that name. The reason I can't use a query or something else is because I have macros on the form page that I want them to have access to.

So my question is this.

What's the best way to make a macro that will
1) ask the user for a name input
2) open an already made form
3) take them to a specific record based on the input name

I tried to use something with InputBox and DoCMD.OpenForm but I'm running face first into a wall any time I tried to combine the two.

View 7 Replies View Related

Conditional Formatting WITH Hide Columns Combination

Feb 22, 2005

Hi All

Has anyone experienced this:

I have a datasheet as a sub-form on a main form. Because the main form is accessed from 2 different forms in the system (let's call them A and B), I hide a column on the datasheet that is not relevant when the access is from Form B. This was working fine.

Until I added a new field to the datasheet/subform with some conditional formatting (background colour change dependent on field content). Now the hide column function causes an error which locks the database. I removed the conditional formatting and the hide column functions works fine. Please note the column being hidden and the column with the conditional formatting are not the same column.

Seems the combination of hide column AND conditional formatting in the same datasheet is the problem.

Any ideas?

Cheers
Alan

View 1 Replies View Related

General :: Conditional Combination For Data In Tables

Jun 12, 2014

I have specific query related to Access, Currently I'm trying to create all possible combination of lot data. I need to know if this would be possible in MS Access.Below is simple example of what is required

Table1 Table2 Table3
Value1 TokenW GateA
Value2 TokenX GateB
Value3 TokenY GateC
Value4 TokenZ GateD

I have 3 sets of data for which I've to create all the possible combinations but with restrictions.Below are few of my restrictions

1. "Value2" always comes with "GateC" & Vice versa but, both don't exist independently.
2. "Value4" always takes "TokenW", But "TokenW" exists independtly as well doesn't need Value4 always.

View 1 Replies View Related

Queries :: Top N For Each Combination Based On Group Averages

Jul 16, 2014

I need to create a query where in the end, I will have four rows of data based on based on two combinations of WaterSourceType and Crop.

I need the query to bring back the results of the average Top N (lets say Top 10%) for each combination.

I have tried this every which way and I can't seem to get it grouped like I want it. I NEED to have four distinct rows with the average of the ProfitPerBushel for each grouping.

Basically, what this does is show me the average profitablity of the top 10% in each grouping.

WaterSourceType | Crop | ProfitPerBushel

Irrigated | Soybeans | ProfitPerBushel
Non-Irrigated | Soybeans | ProfitPerBushel
Irrigated | Corn | ProfitPerBushel
Non-Irrigated | Corn | ProfitPerBushel

View 9 Replies View Related

Queries :: Set Criteria By Having Combination Of Three Options - Or Statement

Aug 19, 2013

In my query, I am looking to set the criteria by having the user pick 1 of 3 options, or a combination of those three options. Here's an example:

The fields are Manager, Employee, Job Function. Let's also say that my criteria is David, John, Busy Work. I want to be able to pull results by David, John, Busy Work, or a combination of those three.

My Query for picking 1 of the 3 looks like this:

Field: Manager Employee Job Function
Criteria: David
Or: John
Busy Work

When I try to add additional Or statements, it never works. An example would be in the fourth line of the criteria, I add:

David John

Running the query produces only the results for David. Mixing it up produces only the results from the first item in the criteria (Manager over Employee over Job Function).

View 2 Replies View Related

Queries :: Implement Combination Criteria To Extract Records

Apr 18, 2014

We have the following table.

Code:
ID studentname lectureplace lecturesubject
1 jack A Biology
2 steve A politics
3 jack B math
4 steve A math
5 jack B politics
6 joe A politics
. . . .
. . . .
154 Jane B Geography

We need a query to implement two criteria to extract the following records:

1-Show lectureplace A AND politics lecturesubject
2-Show only those with lectureplace B regardless of the subject

In case the query is run we will have the following result:

Code:
ID studentname lectureplace lecturesubject
2 steve A politics
3 jack B math
5 jack B politics
6 joe A politics

View 1 Replies View Related

Reports :: Combination Of Grouping And Sorting In Report Does Not Work

May 15, 2013

Access 2010. I have a table with the following fields:

- From
- To
- TypeOfWork (to be chosen from a combo-box)
- Activity (text field to be filled in freely)

In a table i have a complete day with times (from -> to), the type of work between those times and the activity performed between those times a bit like this:

FROM TO TYPE OF WORK ACTIVITY
00:00 - 11:00 Welding Welding clamps
11:00 - 13:00 Welding Welding anodes
13:00 - 15:00 Cleaning Cleaning pipes
15:00 - 18:00 Cleaning Cleaning floor

I would like it to show in the report like this:

FROM.....TO.......TYPE OF WORK.....ACTIVITY
00:00 - 13:00......Welding............00:00 Welding clamps
...............................................11: 00 Welding anodes
13:00 - 18:00......Cleaning...........13:00 Cleaning pipes
...............................................15: 00 Cleaning floor

So it should sort on "From", then group by "TypeofWork" and repeat the "From" field(I think...). But I now have tried every combination of sorting and grouping I could think of and nothing works!

View 13 Replies View Related

Forms :: Auto Populate Field Based On Combination In Different Table

Oct 17, 2014

I have two forms - Main and StudentAdvisor (filtered by student ID#) SAdvisor.jpg

The main form originally had a combo box field to display advisor, the problem is that some students had multiple advisors during the course of their program. This problem was fixed by the creation of a StudentAdvisor (table/form) for recording ALL advisors. I set the form to display by newest term so the current advisor (or initial if no change) is the displayed record.

On the main form I then added "add" to open the StudentAdvisor form for entering information, and an unbound txtfield to display their current advisor (or initial advisor if no change).

The problem is I can not get the txtfield to display the information. I have managed to get the error ( #Name?)

Advisor Type 1=Initial, 2=Current, 3=Previous
Changed 1=Yes, 2=N/A

so a record would record as:
Initial and N/A
Current and Yes
Previous and Yes

I tried writing DLookup but could not get that to look up the combinations (above) and display either initial or current.

also tried pulling form SA = Advisor to form Main = txtAdvisor

For report purposes I need to know not only current advisor but how many were assigned during the program and also how many students each advisor was assigned during certain time frames.

View 1 Replies View Related

Modules & VBA :: Total Based On Combination Of Criteria - Compound If Statement

Aug 15, 2013

I'm trying to get a total based a combination of criteria. Here's my issue:

If ([Vendor Billed Amount] > 650 $ and $ [Excess Fee Approved] = "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
If ([Vendor Billed Amount] < 650 $ and $ [Excess Fee Approved] <> "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
endif
endif

I think I'm missing an argument with the nested If statement.

View 3 Replies View Related

Queries :: Combination Of Letters And Numbers = Data Type Mismatch In Criteria Expression

May 16, 2013

I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.

I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)

This query produces the error given in the title of this thread, whilst the following works:

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)

This related query also works and shows a load of -1s and 0s correctly

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix,
IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND

But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.

View 2 Replies View Related

Reports :: Use 1st Query To Populate Fields Then Use 2nd Query To Filter Fields

Apr 30, 2013

Any way in report that I have 2 reference queries just 1 is to populate all details and 2nd query to filter details and will be the final reporting information???

View 5 Replies View Related

How To Match 4 Character Word Or Number Or Combination By Identical 4 Characters Word

Apr 11, 2013

How to match 4 character word or number or combination by identical 4 characters word or number or combination in one word have 10 or 15 characters.

I have to two separate tables (Table A and Table B). Table A has one column (Tag No) and Table B has about 15 columns with one column name Tag No as below

Table A

Table B

Tag No

Tag No

2009

ZZZ-2030-DC

2010

ZZZ-2010-M9P

[code]....

They're both in MS Access.I am trying to match 2 tables - columns (Tag No) with join query, but not success. I want to match 4 characters in Table-A with 4 similar characters in Table-B (Tag No) cell.What query is suitable to compare two tables.

View 8 Replies View Related

Auto Number, Year, Month Number Combination

Apr 20, 2008

I need to create an auto number for service calls that show the following:

year as 08, 09 etc, month as a letter, Jan = A, Feb = B etc then an incremental number starting at 300 for each month/year combination.

So for example: 08A300

Can anyone help me as I am stuck?

View 10 Replies View Related

Max Value For A Single Date/multiple Time/single Tag ID Combination

Oct 18, 2007

Hi, thanks in advance for any help you can offer. I've got a table that has

Date
Time
Tag ID
Power Level

throughout the day a computer listens to several tags (transmitters) and records the power level of the signal generated by the tag each 3 seconds. What I'd like to do is build a query that gives the Date, Time and Maximum Power level reading for each tag ID. I only want 1 record per tag per day

I've tried using "group by" and max in the query but this gives me all the times throughout the day.

anyway, thanks again for looking

cheers, Shaun

View 2 Replies View Related

Forms :: Total Query - Count Of Fields Based On Data In Other Fields

Jun 28, 2015

I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.

When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.

If I type * in the box (to denote all values) and press enter I get the results expected.

View 4 Replies View Related

Blank Fields In Crosstab Query Based On Previous Query

May 31, 2006

:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....

First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.

When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)

The SQL was written by Access not by me. :)

Here is an example of the Crosstab SQL (which is using a previous query):

TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;

-----------
qryTest2 SQL: (Grouping by to remove dups)

SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;

Thanks for you time! :)

View 1 Replies View Related

Sum Fields In Query

Apr 12, 2006

Basically i want to sum up the total of fields with a currency value in them from a query! how do i do this

e.g. I run a parameter query at present which shows me all mortgage applications by lender for year 2003 (I enter the lenders name) and the loan of each application.

I want it to sum up the value of all loans from this lender. e.g

CaseIDLenderNamePRODUCTDateRecievedLoan required
3058Freedom 820 02/02/2003 £160,000.00
3120Freedom 820 17/03/2003 £80,000.00
3130Freedom 828 22/03/2003 £135,000.00
3153Freedom 838 31/03/2003 £210,000.00

How do i get it to create a new field or basically give me the total of this loan???

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved