Posts by cgyrob

unless I am missing something in your table structure or what you are trying to achieve I think the following is more of what you are looking for.

Select d.pointerField, Case When clm_dateQualifier = 439 Then Case When isdate(CLM_StartDate) = 1 Then CLM_StartDate Else ' ' END END AS ApplicateDate,
                       Case When clm_dateQualifier = 433 Then CASE When isdate(CLM_StartDate) = 1 Then CLM_StartDate Else ' ' END END AS AccidentDate                       
From claimsdetailsdates a
Join TableD as d on d.id = a.pointer
Where d.Pointer = 2645

If that is the case you will have to use the alter command.

This is the syntax for addding a column in Access.

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] }

If you google 'VBA MS Access ALTER TABLE' you should get alot of hits for more details on how to use the command.

You will most likely have to build the statement in VBA on submit but I haven't built any Access forms in along time so there might be another way.

you need to explain what you are trying to do a little clearer.

If you actually mean you want to give the user alter control in your database to add their own fields to your tables this is very bad practice and should never be done.

If what you actually want to do is let them insert new records into one of your tables you can do this with an insert statement.

I haven't used Access in a long time but I beleive if you are using access forms you can permit insert in your controls (drop box, etc). This will allow the user to add new values in the controls and inserts them into your table for you or else you will have to write an insert statement to manually add the records.

There is no Top in Oracle. That is SQL Server syntax.

If you are inserting data from other tables you can use a select statment

Insert into customer(c_id,c_name,c_city,c-street)
Select c_id, c_name,c_cit, c_strett
From Table(s)
Where Conditions

Link82

Group by does exactly that; groups your result set by the fields provided, in the example provided by sknake. GROUP BY tblManagers.managerID, tblManagers.managerLastName, tblManagers.managerFirstName This will give you the number of rows selected grouped by Managers Id, Last Name, First Name so you get results like the following.

Id LastName First Name Count
1 Smith John 10
2 Jones Bill 8
3 Reilly Scott 15

Without the group by you would just get a list of all the records.

Id LastName First Name Count
1 Smith John 1
2 Jones Bill 1
2 Jones Bill 1
1 Smith John 1
3 Reilly Scott 1
3 Reilly Scott 1
...

I have not used [no lock], never thought about it before sknake's post. I would love an explanation of why it is important to use in this situation and others.

Learn a new thing everyday.

I can't see any easy way to parse this string in a single select statement. I would try each individual piece at a time and then once you can parse each piece put them back together.

You might also look into the PARSENAME function as it might help with the parsing.

Sorry I couldn't help more, maybe someone else might have some suggestions.

You should be able to create a stored procedure and try to break off a piece at a time.

If you don't need the data live maybe you can make a dts package to run daily to parse these strings and load them into the new fields.

I have question.

How were these records imported into the table in the first place?

It would have been easier to parse them correctly when they were initially loaded.

As you mentioned in an earlier post Intersect is the way to go if you are using SQL Server 2005 or later.

select a.name,a.userid 
from users a, aoi b, users_aoi c
Where a.userid = c.userid 
and b.aoiid = c.aoiid
and b.aoiid = option1
Intersect
select a.name, a.userid
from users a, aoi b, users_aoi c
Where a.userid = c.userid 
and b.aoiid = c.aoiid
and b.aoiid = option2

Ofcourse if you are still using SQL Server 2000 you do not have access to Intersect. I have put together a query that I beleive will do the trick for you, give it a try and let me know.

SELECT distinct a.userid,a.name
FROM Users a,efx_aoi b ,users_aoi c
Where a.userid = c.userid
and b.aoiid = c.aoiid
and EXISTS
(
SELECT 1
FROM users_aoi c
WHERE a.userid = c.userid
and c.aoiid = 3
and exists(SELECT 1
FROM users_aoi c
WHERE a.userid = c.userid
and c.aoiid = 4)
)

You will have to build the exists portion dynamically to include the options selected.

hope this helps

I see what you mean, I am in the middle of something but i will work on a query for you.

If you have questions on how to dynamically build queries is C#, make a post in the C# forum.

What you need to do is build the query for only the selected options within the code block. If you build a static select query with all the options then it will return nothing because no user will select all the options.

In the code you will have to pull all the options the user selected and build the query to include only those options. Also don't use the left join from the original query as that will give null results from the Users_AOI table which you don't want.

If you are trying to create an exclusive list then 'AND' is the way to go. The key will be to build the statement dynamically in the code so it is only querying for the selected fields.

Build something similar to this

SELECT DATA
FROM users, Users_AOI, AOI
WHERE users.userid = Users_AOI.userid
AND AOI.ID = Users_AOI.aoiid 
AND Users_AOI.aoiid = option1
AND Users_AOI.aoiid = option2
etc.

There is no way around having to dynamically build the statement as this is a dynamic request so a static SQL statement can't be created to encompass all possible resultsets.

Just to clarify,

when the user posts the form you want to retrieve all the users that have previously selected the exact same AOI (x and y and z) or any of the selected AOI(x or y or z)

Also the list of AOI is it a dynamic list that can be added to by the user or static list with limited choices?

the 'IN' statement is an 'OR' statement.

Basically it reads if value = 'x' or value = 'y' or value = 'z'

Intersect gives you the intersection of two seperate select statements.

This will give all the records associated to a user for each area of interest in the Users_AOI table

Select data
From users, Users_AOI, AOI
Where users.userid = Users_AOI.userid
and AOI.ID = Users_AOI.aoiid

If you want to limit the number of AOI to display the IN statement will be the most proficient. You can use a Select statement for the clause but you will require something within the Users_AOI table to use as a delimiter. Either the Id which you would require to list each id you wish to display or add another field to limit by.

Users_AOI
userid
AOIid
AOItype

Select data
From users, Users_AOI, AOI
Where users.userid = Users_AOI.userid
and AOI.ID = Users_AOI.aoiid 
and Users.userid in (select userid from Users_AOI where users_AOI.type = value)

I would do something like the following. This will give you all the records for the users that have any of the 3 AOI and with removing the left join you will not retrieve all the null returns. You can also make the join to the AOI table if you want further information.

Select Users.Id, Users.FN, etc
FROM Users
Join Users_AOI  on Users.userid = Users_AOI.userid
Join AOI  on AOI.aoiid = Users_AOI.aoiid
Where AOI.interest in ('x','y',z')

Try something like

Select Top 1 from Project order by date desc

If you are new to SQL and want to learn you can google 'SQL Tutorial' and should be able to find hundreds of tutorial from basic to advanced.

If you don't have any coding experience the 1st option would be the easiest way for you to get the Crystal report completed.

The wizard will create all the joins on the tables for you and will also let you choose which fields from the tables you want to include in the report. Its quick and easy. Once you create new report, you should be required to create a connection, then browse for the tables you want to include, then it will show you any joins the wizard created, then it will give you a pick list with all the fields in the tables, move the fields you want over and it will include only those fields. If you only see the table names in the field explorer you will have to expand the tables to see the individual fields.

There are a few ways to do this.

1. When you choose your connection and the report wizard runs you can pick your table, then it will ask you which fields you want to include from those tables.

2. When you create your connection choose add command, here you can write a sql statement to pull whatever data you want from the data connection.

3. Create a view in your database and then use the view for your report.

Their is no way to control flow with Case in SQL so the easy answer to your question is no it is not possible.

Its an easy search

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Not sure what you are asking. This statement will most likely return an error. (Multiple values returned when expecting single value)

To make it work you will either have to use an "in" instead of "=" for your sub query or use a where clause within your sub to limit the return values to one value.

Select * from sub where job in (select job from job)

or

Select * from sub where job = (select job from job where value = something )

Query optimization is the process of optimizing the performance of a query to utilize as little system resources as possible.

http://www.samspublishing.com/library/library.asp?b=STY_Csharp_24hours

I have never used filemaker but it is a relational db so their is most likely either a sql window or graphic sql designer like in acccess.

If there isn't you will have to find out how to join tables in filemaker, then join the items table to the costs table where the expiry date is null.

Simple sql

Select a.Item_name, b. Costs
From items a, costs b
Where a.id = b.Item_id
and b.expiry_date is null

Sorry I couldn't help with the filemaker syntax, there might be someone on here that knows the product.

You should have a seperate table which holds your costs and is referenced to the item table.

Items
--------
id
item_name
Desc
etc.

costs
----------
id
items_id
Costs
effective_date
expiry_date

When you need to update a cost you set the expiry_date for the current record and insert a new record in costs for the items_id. This way you will also have historical data for your changing costs. When referencing the costs you just have to select the active record for the item.