Deleting Unwanted Entries At The Subscriber .
Oct 8, 2002
Hello ,
I have created a publication from SQL Server 7.0 and subscribed the publication it to one of the SQL 2000 server std. edition .
Now there are some entries in the subscription list at the subscriber which need to be deleted .
The list shows the name of the publication , the database name and the date .
I wanted to know hoe can we delete those unwanted entries. If i select the entry and right click on it , there is no delete option .
Are those entries to be deleted from one of the tables at the subscriber or the publisher ? If yes, then what is the name of the table ?
Any ideas ?
Thank you very much .
View 3 Replies
ADVERTISEMENT
Sep 11, 2007
Hello. I have a simple project using 3 tables: Categories, Subcategories, and Items. I have just gotten my insert, edit, and delete functions to work, but I've noticed a problem I hope someone can help with:
When I delete a certain category (lets say "Restaurants"), the subcategories and items that were in that category still remain in the database/table. (If I delete "Restaurants", the subcategories "Italian", "Seafood", etc - as well as any items in those subcategories - are not deleted).
So what would I need to do to delete any Subcategory that is in the deleted Category (shares a CategoryID) and then delete all Items in those Subcategories?
For reference, all Subcategories in a Category have reference to that CategoryID, and all Items in a Subcategory have a "SubcategoryID" field. I understand that I need to traverse the tables and remove all Subcategories with the CategoryID being deleted, but since the Items do not have a reference to the CategoryID, how would I delete those as well?
Thanks to whoever can help me out here!
View 4 Replies
View Related
Jul 11, 2014
I have a question on appending and deleting entries in mysql table. This is my sample table.
table name: details
id_name | model | mode | media| first | end | id | level |
+--------------------+-------+---------+-----+-------+-------+--------+--------+
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
[Code] ....
I have numerous entries of same id name belonging to same median number.However,I want to only retain the entries having the longest first and end position and discard the remaining entries
E.g. for id name ="PSK_30s1207681L002" AND median = 5 we have four entries
id_name | model | mode | media| first | end | id | level |
+--------------------+-------+---------+-----+-------+-------+--------+--------+
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
But I want to retain only the one having longest first and end points ie
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
Here ,since for id name ="PSK_30s1207681L002" & median = N we have only 1 entry I want retain that one.
My final output in mysql table should look like this.
id_name | model | mode | media| first | last | id | level |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 3 | 14033 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | T0 | 15550 | 16050 | 6255 | q |
| PSK_30s1189731L001 | -1 | 1 | 3 | 999 | 7531 | 9808 | c |
| PSK_30s1189731L001 | -1 | 1 | 5 | 6609 | 8257 | 9808 | c |
[Code] ....
Is there anything that I could do to append it?
View 1 Replies
View Related
Aug 30, 2013
The following is the trigger which create a row in the audit table when a single deletion is occurred.
ALTER TRIGGER [dbo].[TRG_Delete_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
[code]....
I am trying to create a trigger which should prevent the bulk deletion. The following is the trigger which I have written, it is preventing the bulk deletion. But the problem is, it is removing the single deletion entries in the audit table. I want audit table to hold back the single deletion entries without allowing the bult deletion
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[code]....
View 1 Replies
View Related
May 15, 2006
I have a 3 tier replication with one master server on tier1, 3 servers
on tier 2 and up to 20 remote servers (tier 3) on each of the tier 2
servers. I recently had to delete the subscriptions to each of
the tier 3 servers from the tier 2 servers and then recreate the
subscription. All went well for most of the subscriptions, but
for 3 subscriptions the subscription was dropped at the tier 2 level
but failed to drop at the subscriber due to a time out. I went to
the tier 3 servers in question and issued the sp_removedbreplication
command that supposedly executed properly. On one of these 3
servers everything worked fine and I was able to re-subscribe
successfuly and have everything continue to work properly. On the
other 2 it appeared the re-subscription worked properly until users
began to try to update the database through adds and updates (all
controlled through stored procedures). Users receive an error
message claiming an invalid object name ctsv_ followed by guid looking
string of characters. I suspect it has to do with all of the
stored procedures that were created by merge replication when the
subscription was set up and initialized (no sync). These include
numerous sp_ins_, sp_sel_, and sp_upd_ ... named procedures and a bunch
of dt_ ... stored procedures as well.
How do I clean this up so that I can have users using their database
again and then re-subscribe again short of rebuilding the database (a
very time consuming process)?
View 1 Replies
View Related
Dec 2, 2013
If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL -------
SELECT
job.job_number,
(SELECT MAX(jsl.job_log_number)
FROM job_status_log jsl
WHERE
job.job_number = jsl.job_number AND
jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
View 1 Replies
View Related
Mar 21, 2006
Hi All,I am trying to delete rows from a table using a SQL statement similarto this:DELETE FROM TableA where ID1 IN(Select ID1 From TableB where ID2>= @min and ID2<=@max)Basically I want to delete all rows from TableA that have an ID in arange in TableB. This is done in a stored proc.When I look at the execution plan, it is using the indexes as I wouldhope for. The problem is that it is doing a sort which accounts for73% of the cost. I do not need to sort the results. I don't care whatorder they are deleted in.How can I prevent the sort from occuring? I need this delete to occuras fast as possible.Thanks In Advance
View 4 Replies
View Related
Aug 9, 2007
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
View 9 Replies
View Related
Dec 10, 2007
Hi all,
I have a stored procedure that generates the following SQL WHERE clause
UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND
It is good except that it i can not remove the last AND which is not neccessary at the end of the clause.
I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .
Thanks
View 3 Replies
View Related
Jan 22, 2004
I've created an asp.net page that takes the content of text boxes and writes them to a sql table.
The problem is that when I examine the resulting data in the SQL database, I find that the fields written to have had padding added (up to the maximum size of the fields).
I was under the impression that fields of type NVARCHAR did not store padding (only the no of characters being stored).
I've checked it's not the text boxes on the aspx page by explicitly posting values instead of the boxes content and the same thing happens.
Help
example of function i'm using to post data:
Function AddNews(ByVal Category As String, ByVal ApplicRole As String, ByVal NewsType As String, ByVal Description As String, ByVal News As String, ByVal Hyperlink As String, ByVal Email As String, ByVal BirthDate As Date, ByVal KillDate As Date, ByVal Parent As String) As Integer
Dim connectionString As String = ConfigurationSettings.AppSettings("AuthentConnection")
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [News]([Category],[ApplicRole],[NewsType],[Description],[News],[Hyperlink],[Email],[BirthDate],[KillDate],[Parent]) VALUES (@Category,@ApplicRole,@NewsType,@Description,@News,@Hyperlink,@Email,@BirthDate,@KillDate,@Parent)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_Category As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Category.ParameterName = "@Category"
dbParam_Category.Value = Category
dbParam_Category.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Category)
Dim dbParam_ApplicRole As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_ApplicRole.ParameterName = "@ApplicRole"
dbParam_ApplicRole.Value = ApplicRole
dbParam_ApplicRole.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_ApplicRole)
Dim dbParam_NewsType As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_NewsType.ParameterName = "@NewsType"
dbParam_NewsType.Value = NewsType
dbParam_NewsType.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_NewsType)
Dim dbParam_Description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Description.ParameterName = "@Description"
dbParam_Description.Value = Description
dbParam_Description.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Description)
Dim dbParam_News As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_News.ParameterName = "@News"
dbParam_News.Value = News
dbParam_News.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_News)
Dim dbParam_Hyperlink As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Hyperlink.ParameterName = "@Hyperlink"
dbParam_Hyperlink.Value = Hyperlink
dbParam_Hyperlink.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Hyperlink)
Dim dbParam_Email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Email.ParameterName = "@Email"
dbParam_Email.Value = Email
dbParam_Email.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Email)
Dim dbParam_BirthDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_BirthDate.ParameterName = "@BirthDate"
dbParam_BirthDate.Value = BirthDate
dbParam_BirthDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_BirthDate)
Dim dbParam_KillDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_KillDate.ParameterName = "@KillDate"
dbParam_KillDate.Value = KillDate
dbParam_KillDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_KillDate)
Dim dbParam_Parent As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Parent.ParameterName = "@Parent"
dbParam_Parent.Value = Parent
dbParam_Parent.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Parent)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
View 4 Replies
View Related
Jun 18, 2002
Hi ,
I have lot of SQL logins which do not have access to any databases . They do not show any database access when i check their properties .
My server is SQL 7.0 with SP 3 .
I want to clean up those logins with the help of script instead of deleting every login manually which is time consuming as i have to check the database access before deleting the login.
Is there any where script available or any other way to delete these unnecessary logins ? Also will it be a problem on the SQL Server if i delete those logins at one command ?
Any info. will be helpful to me.
Thanks
Sandeep .
View 1 Replies
View Related
Aug 17, 2006
Here is a simplified version of my problem:
I am inserting data into a table using a stored procedure. The table has an identity column that increments with each insert. When I use erroneous data in the other fields the insert fails….no surprises there! But when the next insert occurs with valid data I find that my identity field has increased even with those inserts that failed, so my sequence has jumped a few numbers. How do I get the identity inserts to roll back if the rest of the data in a row doesn’t insert successfully?
Marcha x
View 9 Replies
View Related
Jul 3, 2007
Hello, I have a report that is based on a dynamic column. The column is specified by the user, and the column tyep may be text, datetime, or bigint. The user specifies the column name, and there is also a parameter that specifies the type. I allow my users to format the date via passing in a date format string, so my code looks something like:
=Switch
(
Parameters!primarycolumntype.Value = "text",
Fields(Parameters!primaryitemcolumn.Value).Value,
Parameters!primarycolumntype.Value = "date",
[DO DATE FUNCTIONS HERE, e.g. Year(Fields(Parameters!primaryitemcolumn.Value).Value)],
Parameters!primarycolumntype.Value = "numeric",
Fields(Parameters!primaryitemcolumn.Value).Value
)
The trouble I am running into is that I am getting invalid date conversion errors whenever a text value is being supplied - in other words, the code in the "DO DATE FUNCTIONS HERE" is being executed regardless of whether my primary column type parameter is text OR date. It would appear that all conversion fuctions are calculated at runtime prior to running other logic. If so, this is a bad thing for me, as that means I cannot mix data types in the same report and figure it out all dynamically. Is this true? I had always thought that the date code (in the example above, the call to Year()) would not be executed unless that statement in the switch statement was true. I've had the same luck with If statements, and Choose statements, and I've even tried using custom code.
I think my whole problem relates to a misunderstanding of how the functions are processed. Can anyone shed some light?
Thanks!
Michael
View 4 Replies
View Related
Jan 5, 2007
Can unwanted characters (e.g. control codes) be replaced or removed in varchar fields during extraction inside DTS package?
SQL Server/SSIS 2005.
Thanks, Andrei.
View 8 Replies
View Related
Jul 18, 2014
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
View 9 Replies
View Related
Oct 14, 1998
How do I remove old stored procedures that are no longer being used?
Thanks,
rm
View 1 Replies
View Related
Jan 13, 2005
I have some code that sorts a column logically even if it contains both numbers and text. But I've found that SQL converts certain characters automatically....
Here is the code...
SELECT * FROM TABLE
order by
case when isnumeric(fieldname)=1 then
right(replicate(' ', 10 ) + convert(varchar,convert(numeric(10),fieldname)),10)
else fieldnameend --10 is the length of the field
The issue I've run across is that if my column contains the following strings(5D000,5E000,4E001) it thinks they are numeric then the THEN part of the condition causes a typecasting error....
I found out that any certain combinations of number + E or D + 0. Does anyone know a way to get around this? Try it out select isnumeric('5E0303') will return 1
View 1 Replies
View Related
Jul 6, 2015
I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...
DBCC Shrinkfile (DB_Name_log2, Emptyfile);
go
alter database [Db_Name]
remove file DB_Name_log2;
go
I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?
It shows up in sys.master_files as offline.
View 4 Replies
View Related
Oct 12, 2007
Hi All,
We've got a basic query that pulls a list of some parts out of our inventory database:
SELECT p21_view_inv_mast.item_id, p21_view_inv_mast.item_desc, p21_view_inv_loc.gl_account_no
FROM dbo.p21_view_inv_loc p21_view_inv_loc, dbo.p21_view_inv_mast p21_view_inv_mast
WHERE p21_view_inv_loc.inv_mast_uid = p21_view_inv_mast.inv_mast_uid
This returns a lot of info over three columns: "item_id" "item_desc" and "gl_account_no"
What I want to do is remove any entries where the "gl_account_no" shows as 011500000
I am by no means a TSQL person, I mostly run the canned queries that our vendor gives us so any help with this would be greatly appreciated.
View 4 Replies
View Related
Mar 31, 2008
Hi,
I have a need to create a report that has a graph at the top and a table at the bottom. The graph at the top can optionally be made hidden because they cause problems when exported to Excel as images. However, when I set the Hidden property of the graph to true, positions of all items on the report remain absolute. Meaning of course that the table that is located half-way down the page remains half-way down the page and there is a lot of nothing on the first half where the graph used to be.
It would be desirable to have the ability to have the table move up when the graph is not visible, however it obviously doesn't do it automatically and also refuses me the ability to change the position with an expression.
Any advice is appreciated, thank you!
View 3 Replies
View Related
Oct 27, 2006
hi
we get ASCII data inserted into a SQL Server database by ODBC connection from an old UNIX system.
Example: INSERT INTO test.db VALUES ('123abc', '456ПРО')
All characters > 128 are converted to "?" automatically.
We tried to setup the database to the appropriate codepage, but we allways get "?" inserted.
View 2 Replies
View Related
Aug 20, 2015
I am creating a report query that returns all unreconciled P/O lines. I am near completion but I am unable to find a way to remove the reconciled records.
I have included a script to produce some sample table, data & query.
The recordset dispalys 6 rows. All reconciled Supplier Invoices are duplicated and have transaction codes 40, 50 and reconcile code of 9 (5024, 921689471).
All unreconciled only appear once and have transaction codes 40 and reconcile code of 0 (4835 & 921978016). These are the only records that I want to show.
CREATE TABLE [dbo].[Purch_Ledger](
[EPDIVI] [nvarchar](3) NULL,
[EPSUNO] [nvarchar](10) NULL,
[EPSINO] [nvarchar](24) NULL,
[EPDUDT] [numeric](8, 0) NULL,
[EPTRCD] [numeric](2, 0) NULL,
[code].....
Whatever I try I cant find a way to get rid of the unwanted records.
View 3 Replies
View Related
Mar 18, 2008
This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.
I know how to get the user table names from the DB as follows:
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'
What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".
delete from tableName where Date_Stamp = '2008-3-18'
View 7 Replies
View Related
May 22, 2015
I have a third party app that passes parameters (main dataset query) from the app to SSRS. Â Unfortunately, when the parameters are passed from the app they will contain equal signs ("=") in front of each parameter. Â For example, the parameters that may be passed to the main dataset query should be:
"HYDRO, OKO, ONEPL, TECHNI"
However, the parameters that are passed from the app, get to SSRS as:
"=HYDRO, =OKO, =ONEPL, =TECHNI"
Again, this is for the main dataset query and there may be one parameter or there may be any number of them.
Basically, I need to strip the "=" signs from the parameters. whether there is one parameter or ten.
I believe that I will need a custom function to strip the equal signs?
View 5 Replies
View Related
Dec 1, 1998
Below are the registry entries I want to update but "Trusted_Connection" = "Yes" does not seem to have any effect when it is run. Any ideas??
REGEDIT4
[HKEY_CURRENT_USERSoftwareODBCODBC.INIAAED]
"Driver"="C:WINNTSystem32sqlsrv32.dll"
"Description"="AAED"
"Server"="SIM0108"
"Trusted_Connection"="Yes"
[HKEY_CURRENT_USERSoftwareODBCODBC.INIODBC Data Sources]
"AAED"="SQL Server"
View 2 Replies
View Related
Jan 27, 2005
Hi, I am searching for the most easy SQL solution:
Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?
Thx for reply.
dajm
View 11 Replies
View Related
Sep 24, 2006
Hello
my table :
Report :
R_id (PK)
RName
RDate
i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name
i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)
how can i do it ?
thanks a lot for helping
View 1 Replies
View Related
Mar 30, 2012
I have a table which stores phone numbers of a customer in a table.
Say this table is as below
CustomerName - PhoneNumber
Customer 1 - Phone number 1
Customer 2 - Phone number 2
Customer 2 - Phone number 3
Customer 3 - Phone number 4
What would be the best approach to prevent adding another entry against Customer 2. I should be able to add new customers and add multiple phone number against all other Customers. The restriction should be only against Customer 2.
View 4 Replies
View Related
May 14, 2004
I am trying to pull the last three entries from a table in my database but I am having trouble writing the correct query. The database has multiple entries for each item in my database but I want to pull just the last three for each item. I have tried the TOP function with the items ordered in descending order but that only works when I define each item seperatley. I need to know how to pull the last three entries for every item I have in the table.
Thanks,
Tim
View 3 Replies
View Related
Nov 8, 2005
Hello
Without technical information and DB design, I wish to know the maximum entries in MS SQL in million. I guest for access are a limit of 2 million and a 2GB DB.
How much for MS SQL in max. entries and how much in GB?
A lot of thx for your feedback.
Dominique Javet
View 4 Replies
View Related
Mar 28, 2006
I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script:
Select
L.URN,
LH.THE_TIMESTAMP,
LH.ACTION_TYPE,
LH.ACTION_BY,
LH.ACTION_REQD,
LH.NOTE,
em.position_type_ref
From
LOG L
Join Log_history LH on (L.URN = LH.LOG_URN)
left outer join employee em on (em.code = LH.action_by)
Where
(L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU')
Order By
L.URN ASC, LH.THE_TIMESTAMP DESC
The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall
View 7 Replies
View Related
Jun 12, 2006
Hi,
I need help desperately. The following is an example of the data that I have.
I have two tables one name Customers and the other named Orders. The tables have the following attributes
CUSTOMER
CustomerNum, CustomerName, OrderNum
ORDER
OrderNum, OrderDesc, CustomerNum
Bare in mind that the CustomerNum can have multiple customers attached to it, example a whole family (I know bad database design but it's too late to change) Let's use the following info for the tables respectively
CUSTOMER
0001 Sharon Bigbsy 1234
0001 Dale Bigbsy 1235
0001 Omar Bigbsy 1236
ORDERS
1234 Chips 0001
1235 Gatorade 0001
1236 Candy 0001
The query i'm using is select Customer.CustomerNum, Customer.CustomerName, Orders.OrderDesc from CUSTOMER, ORDERS where CUSTOMER.CustomerNum = ORDER.CustomerNum
The results are as follows
0001 Sharon Bigbsy Chips 1234
0001 Sharon Bigbsy Gatorade 1235
0001 Sharon Bigbsy Candy 1236
0001 Dale Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Dale Bigbsy Candy 1236
0001 Omar Bigbsy Chips 1234
0001 Omar Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236
It's giving all the orders place to each customer name but I need only the following
0001 Sharon Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236
I'm using SQL in MS Access 2003. Please disregard the abnormalities of the tables, it's someone else's work I inherited with over 6000 entried.
Any help will be greatly appreciated
View 8 Replies
View Related
Feb 7, 2007
I have an application that allows the user to enter data into a table. There are multiple users so I put in some code that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem.
The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1.
We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening?
Here is how I create the new record id:
I get the MAX(ID) from the table
I add 1 to the ID and then insert a new record with the new ID into the table.
Any help is appreciated.
Thanks,
enak
View 5 Replies
View Related
