I've got an unbound form which has a field that I need to relate to from a query, I've got no problems relating to specific records, but I've not quite cracked the ability to select all other records if the field is left at 0.
I've tried using in the query under the 'table' field (for selecting a specific group of people seated at a particular table)
Which I thought would evaluate a true/false with the two results coming from the two options, so if the Table selected on the unbound form was set to 0 then it would set the criteria to >0 (everything table 1 and up), or if anything else was selected other than 0, it would use the number from the unbound form from the TableSel field.
I'm getting blank results from both positive and negative results. I assume that I'm not using the Iif statement correctly, or that there is another way of approaching the problem that my headache is blocking :)
This is a bit confusing, so I'll try to explain it well.
Two tables. One has a list of metals. The other has a list of submetals, and what basic type of metal it is. It's grabbing the second column from the first table. Makes sense, right?
Now, I have another table, with links to both of those fields. What I want it to do is when it picks the metal type, to filter the subgroup to only the types it has associated with it to the basic metal type. Not sure if that makes sense. I'll supply an example.
Table 1 Aluminum Brass Copper
Table 2 Subgroup Basic group License Plate Aluminum Iron Aluminum Aluminum Yellow Brass Brass Copper Wire Copper
Now, if in Table 3, they choose Aluminum from the drop down box for basic metal, I want the dropdown box for the subgroup to have only License plate, and Iron Aluminum. For Brass, only Yellow Brass, and the same for copper.
Anyone knows if I can predefine decimal places in a query statement? For example, when I run the select query below, I want the MedicalPremAmt to have two digits after the "." It should look something like this: 12.38
Thank you.
SQL = "Select [Barg Unit], First([Medical Option]) As Carrier, First([Medical Coverage Tier]) As Tier, Sum([Medical Premium Amount]) As MedicalPremAmt,Sum([Total Grant]) As TotalGrant,Sum([Health Allocation]) As HealthAllocation,sum([Medicare Allocation]) As MedicareAllocation FROM RetireeCensus Group By [" & Category & "];"
Ok this may seem very confusing as alot of things need to happen for what I am trying to do
I am doing an inventory database that contains all order, client, purchasing, and inventory information.
I have a table that has all of the different products we sell (also includes which of the following tables are needed) I also have separate usage tables storing how many of each part (given a partnumber) for each of the products.
Now this is where the complecation comes in .When I create an order I want to 1. add a record to my order table (containing ordernumber, client number, product purchased) 2. using the product table, find out which usuage tables need to be looked at, go into those usage tables, get the number of each part that is needed for the product and store the value into an allocation table
I have had this on my mind for the past 2 weeks that I have know I will have to do this at some point. I am getting to the point where I cannot do anything else without this being done. I need help something bad.
If you feel you know how to do this and think you need more information, email me at cgow1@cogeco.ca
Hello, Im creating a database for a charity just to get some experience and im stuck on something - my table structure.
I know you might find it a bit difficult because you dont know the background info but i need the database to be able to let customers and members make bookings. Ive thought of putting both customers and members in one table but i dont want it to get complicated and messy, although i will do that if there is no other way.
Hi everyone, basically what i need to able to do is set a validation rule that allows me to enter a value that is between 5 and 10 kgs but (and here comes the confusin bit is also rounded to the nearest 5kg. I tired using the[ like "?.?0" or "?.?5" ] command but have so far been unsucessful. Also i really like oranges.
One of our key customers (GE) has demanded that we begin to submit our estimates in their transmission 160 character format.
Basically it it a txt file that looks like this all on one line. 1ABCDWXYZ0502EST TEST123456T050208654321C 00010851803098518031 000000E 0000000000000D CAR SHOPPED AS CLEAN-PROCEED WITH
Only a couple of those characters are in my database and will change. Other than that items will continously be the same. The items that are optional and we don't have not only have to be blank but have to have spaces there instead...not 0's.
How do I set up a query to kick all of this out? I tried something like:
I am trying to redesign an older DB for a school, to meet the new needs that emerged. I am totally confused due to the complexity of the problem. I've already searched in this forum and in Google but most articles are for simpler DBs. Well to get to the point.
School has Students, Students attend to Classes (many to many) Students are assigned Lessons (many to many) and coming to the confusing part a Lesson in specific Class may be teached from two different Professors (e.g. the 1st may teach the theory and the other the excersizes). So another many to many Class-Professor and another Class-Professor-Lesson and so on?
After throwing away many sheets of paper I come up with a schema which seems a little bit strange but seems to going to work for my case. I figure out that I could have a unique junction table storing all these info, and that's no other than the Schedule table. So I'm thinking of having the following relationship schema:
Students StundeID Name etc.
Lessons LessonID Title
Classes ClassID Title
Professors ProfessorID Name etc.
Schedule ScheduleID (maybe a combination of all others) ProfessorID,join with Ptofessors ClassID, join with Classes LessonID, join with Lessons Day Time
Schedule_Students tableID ScheduleID,join with Schedule StudentID, join with Students
What is your oppinion about? Can you see any problems which may emerge from such a design? Thanks in advance
im writing this really long query in vb so it needs to go on two lines, but whats the syntax for going on the next line(theres a quote or something but i cant remember for carrying on from one line to the next)
Hello again all... Today's problem is as follows.... I have an "after update" combo box that is coded as "CurrentDb.Execute "uno", dbFailOnError". I have an SQL Query named "uno" that is showing the following: "UPDATE [Input] SET [Input].Loc_Lng = [Loc-1L].Combo8, [Input].Loc_ID = "1", [Input].Loc_Desc = "xxxx";" I am trying to have the combo box update the values from the combo box into a blank table. When I try to run this, it comes up with the error: "Too few parameters. Expected 1". Any idea what that means, and then how to fix?
I have a select statement that returns information into a sting Then I try to insert this value into a table but I am not sure of the correct syntax. The SQL select statement works but not the insert into...
I was wondering if anyone knows how to insert more than one record at a time with a SQL statment? I have a form that asks the person how many records they would like to put into the system, this is a text box that they can enter a number.
My SQL for input one record is this: [CODE]
SQL = "INSERT INTO tblTemp ([Name],[Number]) VALUES (TName,Len)"
DoCmd.RunSQL SQL
If the person wants to put in more than one I want the system to take the Number field and add 1 to it for ever record the person asks to be put into the system. the Name and Number fields will be that same except the Number field will increase by 1 for however many they choose to put in.
I am trying to setup a query that will return only the records in a date range that is user specified. In the Criteria of the date field I entered this
Between [Forms]![Report Switchboard]![txtStartTrend] And [Forms]![Report Switchboard]![txtEndTrend]
But the query does not work. Is the syntax correct? Both txtStartTrend and txtEndTrend are format Short Date.
hello all, i have a pretty simple problem related to ACCESS query. I have two tables having similiar columns ( i had to do this to compare the values given to me, because both the table data came from different sources)
The tables named are OFFICERS token full_name division EMPLOYEE token full_name division
Now the table EMPLOYEE contains more than 5000 records and the table OFFICERS contains around 2400 records which are already present in the EMPLOYEE table.
Now I want to build a query that will return me all the records in the EMPLOYEE table that are not present in the OFFICERS table. The criteria for comparing 'full_name'. so the query should return me 2600 records that are not present in the OFFICERS TABLE.
I tried doing this Code:" SELECT DISTINCT EMPLOYEE.full_name,EMPLOYEE.token FROM EMPLOYEE,OFFICERS WHERE EMPLOYEE.full_name<> OFFICERS.full_name"
But this query returns me records that is not present in the OFFICERS table as well as some more 1000 records which are present in both tables.
problem with this insert statment in vba Hi guys i got vba code that suppose to write table name ,column name , rquired ,feild type and feild size to an external db. The first part of this code write tables naem to external db and it is working well. I get the following error when i press the button on my form :
Code:Run-time error '424':Object required
and when i click on debut it points to this part with yellow collor.
Code: metadb.Execute " Insert Into SysColumns(tablename,columnname,required,type,leng ht) " & _ " Values ('" & TableDef.Name & "','" & Feild.Name & "'," & Feild.Required & ",'" & FieldType(Feild.Type) & "'," & Feild.Size & ")" I be happy if some one help me fix this erro.Thanks
my complete code
Code:Sub InsertSystemCatalogPopulation(db As Database, metadb As Database)'''now locating all the non system tables in current db and then writing it'''to systables tableFor Each tbl In db.TableDefs''' excluding the system tables If Left(tbl.Name, 4) <> "MSys" Then ''' writing the tables name to systables metadb.Execute " Insert Into SysTables(TableName) Values ('" & tbl.Name & "')" End If Next tbl MsgBox (" All tables names coped to systables system cataloge ") '''############################################### #################################### ''' now we go find all non syste feilds in the current db and then writing ity ''' to syscolumns For Each TableDef In CurrentDb.TableDefs'''this if statment remove the system feilds If Left(TableDef.Name, 4) <> "Msys" Then For Each Field In TableDef.Fields metadb.Execute " Insert Into SysColumns(tablename,columnname,required,type,leng ht) " & _ " Values ('" & TableDef.Name & "','" & Feild.Name & "'," & Feild.Required & ",'" & FieldType(Feild.Type) & "'," & Feild.Size & ")" Next Field End IfNext TableDefEnd SubFunction FieldType(intType As Integer) As String Select Case intType Case dbBoolean FieldType = "dbBoolean" Case dbByte FieldType = "dbByte" Case dbInteger FieldType = "dbInteger" Case dbLong FieldType = "dbLong" Case dbCurrency FieldType = "dbCurrency" Case dbSingle FieldType = "dbSingle" Case dbDouble FieldType = "dbDouble" Case dbDate FieldType = "dbDate" Case dbText FieldType = "dbText" Case dbLongBinary FieldType = "dbLongBinary" Case dbMemo FieldType = "dbMemo" Case dbGUID FieldType = "dbGUID" End SelectEnd Function
Here is my CASE WHEN statement in SQL. What would the If statement be in MS access?
CASE WHEN LEN(Serial) = 9 THEN Serial WHEN LEN(CAST(Serial as Varchar(255))) = 8 THEN '0'+Serial WHEN LEN(CAST(Serial as Varchar(255))) = 7 THEN '00'+Serial WHEN LEN(CAST(Serial as Varchar(255))) = 6 THEN '000'+Serial ELSE 'Error' END AS NewSerial
Im using MS Visual Web Developer with an Access Database, i am trying to enter a new row of data into a database via a select statment attached to a button_click event. the code i am using looks something like this.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
that being said. when i click the button, NOTHING happens, no error and no insert!!!
I did test the connection to the Database and its fine, i can do an update and i can read the data that exists in the database i just cant insert a new row.
This is not the first time i have delt with INSERT statments but this has me at my whits end and any help would be great.
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items. How can i make 3rd query which will give me all but taken items from query1? (of course items from query 1 are in query2) thx in advance
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
