Confusing Query-type Help

Aug 11, 2005

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.

Any ideas on how to implement this?

View Replies


ADVERTISEMENT

Query Results From Iif Statment Confusing

Jun 1, 2006

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)

Iif([Forms]![TablePlannerFrm]![TableSel]=0,>0,[Forms]![TablePlannerFrm]![TableSel])

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

Thanks everyone

View 1 Replies View Related

Confusing

May 12, 2005

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

View 1 Replies View Related

ER-D Really Confusing! Please Help!

May 1, 2007

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.

The ER-D is attached.
17309

Is there a solution to my dilemma?
Ant...

View 2 Replies View Related

Confusing Use Of Validation

Feb 7, 2005

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.

View 5 Replies View Related

Very Confusing & HUGE Challenege..need Help.

Nov 8, 2005

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:

GEString:2UPRR YYMM00&""&WorkOrderID yada, yada, yada

but it didn't work....any ideas? I also need the YYMM to be the current year/month and I have to pull the decimals out of the estimated billing costs.

Is there a better way to do this? As you can imagine...typing 5+ pages of this for each job would be a NIGHTMARE!

View 13 Replies View Related

Schools DB Confusing Problem

Oct 6, 2007

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

View 8 Replies View Related

Connecting Super Type & Sub Type Entities With A Condition

Sep 21, 2004

hi friends,
i have tried had to connect sub type tabels (Saving, Checking, Loan... they have their own ids...) with super type (Account...it has account id...) on the condition of account_type (either "S","C" or "L") attribute in ACCOUNT entity.
how to joint them??? with query or with expression??
i expect help from you.........please.
........thanks.

View 5 Replies View Related

Query By Type

Sep 16, 2005

I have a table products
product id - autonum, key
ref - txt
serial - txt
typeId - num (gets type from types table)

and a table types
typeId - autonum, key
type - txt

I want a query to allow my form in a choicelist to pick one of the types and display all available products on that type.

I got :
SELECT types.TypeId, types.Type, products.Reference
FROM types, products
WHERE (((types.TypeId)=[products].[typeid]));

But it doesn't give me that result when I make the form based on that query.

View 2 Replies View Related

What Type Of Query Is This?

Oct 26, 2007

I'm not really looking for code, just a clue as to what to read up on as I have been beating my head against the wall with this one. I can do it in about three steps but I am trying to do it in one. :confused:

The first table1 is like this:
id Field1 Field2 Field3
1 TagNo44 Y Yes
2 TagNo5 C No
3 TagNo127 Q Maybe
4 TagNo4 T Perhaps
5 TagNo88 Z Probably Not
6 TagNo17 P Could be

The second table2 is like this:
id Field1 Field2 Field3
1 TagNo44 Outfit6 30
2 TagNo44 Outfit14 70
3 TagNo127 Outfit14 100
4 TagNo4 Outfit3 100
5 TagNo88 Outfit3 50
6 TagNo88 Outfit8 50
7 TagNo5 Outfit7 100
8 TagNo17 Outfit92 100

Output table to be like this
id Field1 Field2
4 TagNo4 Outfit3&100
2 TagNo5 Outfit7&100
6 TagNo17 Outfit92&100
1 TagNo44 Outfit14&70&Outfit6&30
5 TagNo88 Outfit3&50&Outfit8&50
3 TagNo127 Outfit14&100

Table1Field1 is in a one to many relationship to table2.field1
All fields are strings.

View 3 Replies View Related

Type Mismatch In Query

Jun 4, 2007

Hi
Hope someone can help

I have a query that is trying to match two tables, one in MSSQL2000 and the other in Access 2003. The matching field in MSSQL is a Text field and in Access the field is a long integer. When I try to run the query it gives a Type Mismatch error.

I've tried all the functions that I can see to convert either of the fields data types but to no avail.

Does anyone know of a way to force a match?

Thanks
Mark

View 2 Replies View Related

Different Data Type In An Update Query

Jun 9, 2005

I want to say update set field1 = field2

But field1's data type is Number and Field2 is Text. (Field1 is fed by a lookup table). Am I doomed to go through the 700 or so by hand, gurus, please?

View 5 Replies View Related

Elseif Type Function In Query

May 31, 2006

i have the following query statement:

intext: IIf([calllogs]![InternalCall],"0","External")

in "InternalCall" there is either a "0" or a "1", these represent external and internal calls

So I want to turn the "1" and "0" into "internal" and "external"

I've done half of it, but I can't use an "else if" statement in the query, the only other option is to have one field for internal and one box for external with seperate iif statements which I want to stay away from...

View 3 Replies View Related

Type Mismatch Error In Query

Jun 15, 2006

I am trying to do a basic query and I keep getting a "Type Mismatch" error and the query will not run. If I only do a query on one table, it works no problem so I know it must be related to my Join between tables.

For the two tables that are joined (it is one-to-many)- the first table is a clients table and I created a field called ClientNumber that is an AutoNumber. The second table is called TrainingRequests. This will store the training requests for each client and each client can have multiple training requests. I created a field called ClientNumber in it as well (this is what field I linked the tables by). But I set it to text instead of AutoNumber.

Is there a way to do a query with the two tables? Or will I have to change something in table design? I already have some data in the tables so I am not sure what direction to take.

Thanks for any help someone can provide. It would be greatly appreciated.

View 1 Replies View Related

Converting Data Type In A Query

May 31, 2007

I'm sure this must be an easy one, I haven't used access for years and I've forgot how to do everything.

I'm designing a query based on a linked table which belongs to someone else. Unfortunately they appear to have stored a cost value (eg. 12030.30) as text.

I need to group the table records together and sum the cost value but I can't because its a text data type. Can anyone help me convert the data type within the query so that I can sum the costs, I've tried to build the expression using the cdbl() function but got stuck when it asked me for a parameter.....

Would appreciate the help :)

Thanks

Lloyd

p.s using Access 97....

View 1 Replies View Related

Correct Join Type And Query Statement

Feb 7, 2006

I've been struggling with this for awhile now. I've been trying to research a solution but I'm not even sure what to search for.I have the following table structure:tblContactsContact_ID (PK)First_NameLast_NametblBidPackBid_Pack_ID (PK)Bid_Pack_DescriptiontblDistributionListList_ID (PK)Bid_Pack_ID (FK) (1-M)Contact_ID (FK) (1-M)What I would like to do is create a query to display all of the contacts from tblContacts that are not associated with a certain Bid_Pack_ID. My end goal is to create a form where I enter Distribution List Information. I want a listbox on the left with all of the contacts from tblContacts and a listbox on the right with contacts that have been selected for a particular Bid_Pack. When the user double clicks an item on the left it should show up on the right and be removed from the left. I'm looking for the same functionality as when you use the design wizard and you choose fields to include/exclude.For example, let's say I want to add contacts for distribution list "A". My thought is the box on the left would show all the contacts from tblContacts that aren't selected for distribution list "A" and the box on the right would show all of the contacts that are part of distribution list "A". As you double click an entry it would perform the appropriate record creation/deletion and each box would be required.So back to my original question, what is the correct method (relationship, join, querydef) to select all of the contacts from tblContacts that haven't been assocatied with a particular bid pack?Thanks for any help you can provide. Even a nudge in the right direction would be appreciated.

View 2 Replies View Related

Data Type Mismatch In Query Criteria????

Nov 4, 2006

Query1:

Src: Table1 joined Table2

ID (Type Text)
Title (Type Text)
Remarks(Type Text)
Formatted: FormatTitle([title],[Remarks])
Expr1: InStrRev([Formatted], "~")


public functionFormatTitle(ByVal sTitle as String, ByVal sRemarks as String) as String
'do process code here very complicated an long, but works find in the end
'creates a Multi-String delimited by | (pipe)
end function

The above works, and Expr1 does give an accurate value for the position of a "~" (tilde) in the string Created by the FormatTitle() function.

However, If I put a Criteria >0 on Expr1 it asks for the value of the [Formatted] field as if it was a parameter. If I put a criteria for Formatted: Like "*~*" I get a Data Type Mismatch in Query Criteria

Query2:
Src: Query1
Title (Type Text)
Remarks (Type Text)
Formatted(Type Text)
Exr1 (Type Number) criteria >0

This Query Also produces the Data Type Mismatch in Query Criteria
pardon me, but WTF? If it isn't a STring, than InStrRev() should produce an error, not an accurate response, and if InStrRev() produces a number why can't i compare it to 0 (zero)? This is indubitably messed up that I'm getting this error. There is no data type mismatch, on either of these tests, one is a string and I criteria-limit it by a string operation, the other is a number and I criteria limit it by a number, WHAT IS GOING ON!!!

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner - The Frustratedly Confused

View 2 Replies View Related

Select Equal Type - Based On Random Query

Oct 6, 2006

Good Afternoon Everybody,

Apologies for imposing on you all but I was wondering whether or not you could help me out??

Would you be so kind as to kind as to show me a practical example of how to incorporate a facility into my attached Database which serves to select an equal ratio of my field “SectionHeadings” such that out of the 30 “random” questions that it currently generates it will provides me on completion with the following:

5 From Set1
5 From Set2
5 From Set 3 …..etc etc

It currently generates the 30 questions that I need without a problem; however it routinely selects any number of one particular type, sometimes more from one subject and less of another.

All subjects headings are of equal importance to me and as a consequence would be very grateful if you could look at what I’ve already done and show me what needs to be done in order to achieve my aim.

I hope my request is possible - Any help you could give me would be very much appreciated. Thank you very much.

Best Regards

CarolW

View 5 Replies View Related

Queries :: Left Joint Query Type Mismatch?

Aug 27, 2014

Working with two tables - tbl_A_OrdData & tbl_B_ShipData tbl_A have all the orders placed, but tbl_B only have the orders that got shipped. I'm trying to create a query that will show me all orders that got placed for a particular criteria and if the order find a match in tbl_B to give me the ship date too. A left joint query get me the data I need. But my dates are in YYYYMMDD number format so in the query I am putting the DateValue function. And that error out as "data type mismatch" because it cannot find the shipdate for certain records. I tried this but didnt work. ShipDt: IIf (IsNull ([PSHPDT]), "", DateValue(Mid([PSHPDT],5,2) & "/" & Right([PSHPDT],2) & "/" & Left([PSHPDT],4)))

View 2 Replies View Related

Queries :: Can Retrieve Value Of User-defined Type In A Query

Aug 19, 2013

Is there a way to retrieve the value of a user-defined type in a query?

Here's the type:

Code:
Public Type ClassRank
Rank As Integer
ClassCount As Integer
End Type

I have a function with the following excerpt:

Code:
Function GetRank(strDOD) as ClassRank
...
GetRank.Rank = intRank
GetRank.ClassCount = intCount
...
End Function

In my query I expected to be able to put the following:

Code:
GetRank(strDOD).Rank & " " & GetRank(strDOD).ClassCount

However, Access didn't like the period in .Rank or .ClassCount.

Should I just write two different function to get 'rank' and 'classcount'?

View 2 Replies View Related

Tables :: Converting Text Data Type To Number Data Type

Nov 3, 2012

I have a table with a field with names set to text data type and i want to change it to number data type but when i do it in design view the data get lost. I want to know if there is a way to convert the data in the field as number type and keep the data in the field.

View 7 Replies View Related

Query Using Joins To Disply All Records From Both Sides Of Join Type

Apr 14, 2008

I have a list of PC SN#s in tbl1;
I have a list of other PC SN#s in tbl2
I want to display ALL records of tbl1, AND ALL records of tbl2, matching up the SN#s (where there is a match) - but I want to display all records whether they match or not.

How can I accomplish this?

View 1 Replies View Related

Modules & VBA :: Data Type Mismatch Error When Running Sql Query

Jan 16, 2014

I have vba code that creates the following SQL:

SELECT SubscheduleID, EventID, WeekOrder, DayID, StartTime, EndTime, Priority, CanJoin, PatientTitle, PatientNickname, IncludesPatient, IncludesAftercare, Letter1
FROM [qryScheduleCombinedDetails]
WHERE (SubscheduleID = 1 AND IncludesPatient = -1 AND DuringAftercare <> "AC only" AND (WeekOrder = "All" OR WeekOrder = 3 OR (WeekOrder = 1 AND Letter1 = "XYZ")) AND DayID = 2 AND StartTime <= #8:00:00 AM# AND EndTime >= #8:30:00 AM#);

When I try to run it, I get a "data type mismatch" error. When I put the same code into a query, I get the same error. However, it will run if I delete either condition from within the (WeekOrder = 1 AND Letter1 = "XYZ") pairing. I can't figure why it can run with either of those, but not both together.

WeekOrder is defined as String. Letter1 is calculated as Cstr(Nz(IIf(Letter,"XYZ","ABC"))) within [qryScheduleCombinedDetails], because I wanted to make sure that it would be recognized as a string.

View 4 Replies View Related

Queries :: Limit The Type Of Data That Can Be Entered In A Query That Has A Like Criteria?

Jan 18, 2014

Is it possible to limit the type of data that can be entered in a query that has a LIKE criteria?

( Like [Enter Data] & "*")

limit to two digits or any number of digits, or limit to numbers only or letters only. .

View 2 Replies View Related

Modules & VBA :: Opening A Query With Parameters - Data Type Conversion Error

Jun 11, 2013

Here's my Goal: To open a saved query that has a parameter, setting that parameter via a VBA sub.

Here's my Problem: I was getting various errors, but after debugging my program a bit, it comes down to a "Data Type Conversion Error"

Here's my Code:

Set db = CurrentDb
Set qd = db.QueryDefs("qryMY_DATA")
qd.Parameters(0) = Me.txt_ReferenceID
Set rs = qd.OpenRecordset("qryMY_DATA", dbDynaset)

Code:
'*** Database Variables
Dim db As DAO.Database, rs As DAO.Recordset, gq As DAO.QueryDef, prm As DAO.Recordset

I've been all over the forums and tried several different approaches, all to no avail. The Query runs fine in the QDT, but kicks back an error when I try to run it from my sub.

View 10 Replies View Related

General :: Cross Join To Create Ledger Type Query From 4 Tables

Apr 2, 2013

I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .

See the attached image ...

View 7 Replies View Related







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