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 Replies


ADVERTISEMENT

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

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

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

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

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

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 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

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

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

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

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

Modules & VBA :: Update Every Records Using Queries

Dec 27, 2013

I am new to MS Access and using MS Access 2013.I just added 50 new fields into a table. I need to update these new fields on every record.The table layout is as followed:

Table1
-Company Id
-Question1
-Question2
-Question50

[code]....

View 6 Replies View Related

Modules & VBA :: How To Pre-processes Queries Before They Are Exported

Dec 2, 2013

I was wondering if it is possible to pre-processes queries before they are exported.

I wanted to do a few things like, conditional formating, column widths and some simple formatting.

I currently have a macro to export to an xlsx excel spreadsheet and i dont know if i can code in the formatting or if i have to create an additional excel spreadsheet with the macro on it so that I can do this:

In Access -->
Export Query
Open export
Open Pre-programmed macro
run macro
save the export with the macro completed
close macro

View 2 Replies View Related

Modules & VBA :: Search All Queries For A Field

Sep 26, 2013

I need to find out where the field called "R_SHIFT" is being referenced in all our MS Access queries. I just need the query name so I can go into the query and change the name from "R_SHIFT" to "SHIFT"

I was thinking there was a function for Modules to Find Field? I have never done any Modules before..

View 3 Replies View Related

Modules & VBA :: Run Queries That Are Named In Table

Feb 13, 2015

Basically I want to execute every query within my database that exists in one of my tables. It should fill a recordset with the query names and loop through the database and execute each one.

When I try and run access keeps saying run-time error 3061 too few parameters (yellow line). I know the issues is with the sql, but not sure how to get around it or if this type of query is do-able.My code below:

Public Sub C38()
Dim rst As DAO.Recordset
Dim DB As Database
Set DB = CurrentDb()
Set rst = DB.OpenRecordset("mTable")

[code]....

View 6 Replies View Related







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