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
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.
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.
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.
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.
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?
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.
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.
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 RelatedHi 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.
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.
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
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
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).
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.....
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
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!
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.
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?
How Mark disappear field data based on field data last.
Example:I have a field type in the name and on behalf of another field No.
In the case of the Type-B data is deleted Number field, which is before the character.
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.
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?
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
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1
ID // Response // Cats // Dogs // Elephants //.... Column 10
1 // I like cats // I like cats //(null)//(null)// ... (null)//
2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..//
3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data
2. Section 2: Show all responses from the Dogs bucket where there is data
3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?