Autogenerating Numbers For A Primary Key Field, StudyId, In A Table--but With A Few Twists.
Jul 31, 2007
I have a question on autogenerating numbers for a primary key field, "studyID," in a tablebut with a few twists.
We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).
Heres the range of values we want our StudyIDs to be (this is to be our validation rule as well):
10001-19999 for Site 1
20001-29999 for Site 2
Your suggestions are VERY VERY WELCOME! THANKS!
View 2 Replies
ADVERTISEMENT
Apr 13, 2007
I have been testing my database application for a while now. As a result i have a lot of records already with their respective identity field values. What i want to do is to reset this field to 0 (zero) before i deploy the aplication. I have tried deleting all the records and entered one to see if the id will be 1 but it still starts from where it was before i deleted the records. Any ideas.
View 2 Replies
View Related
May 17, 2005
is there any way that i can copy primary key(PK) value to another field in the same table .. say my PK is MemberID i want to replicate that into other field say SortID at the same time when the primary key is incrementing. I'm a newbie in this field please pardon me if something is wrong in the way i'm asking ...please help me friends i'm struggling since a long time ... Thanks in advance ..
savvy
View 11 Replies
View Related
Feb 23, 2001
I'm trying to get a SQL 7 and 6.5 DB to interact, but while there is no problem in SQL7, I cannot create a table called "Public" or a field called "Primary"!!
Does anyone know why this might be and if so where I might get a list of any other "invalid" names??
Thanks in advance,
Damon
View 1 Replies
View Related
Apr 10, 2015
How can i find the primary field name and primary table name of a given foreign key.
For example
Table A:
IDa
Column1
Column2
Column3
Table B:
IDb
column1
column2
column3 <---- this is foreign key to table A's IDa
What i want to do is i pass input of tableB, column3 and i get name of Primary tableA and field name IDa. How is it possible to do in tsql ?
View 4 Replies
View Related
Jun 13, 2015
I am designing a database. I want to define a automatic sequence on a table primary key field. what is the best solution for it?
I know I can enable identity property for a field, but it has some problems ( for example its seed jumps on restart and unsuccessful events)
I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.
which one is better?
View 3 Replies
View Related
Jun 18, 2007
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
View 5 Replies
View Related
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
Jul 8, 2006
Hi all,
-- Table 1
Declare @TblFin Table
(
Empid int
)
-- Inserting the Data in the Table 1
Insert @TblFin
Select 1 Union All
Select 2 Union All
Select 3 Union All
Select 4 Union All
Select 5 Union All
Select 6 Union All
Select 7 Union All
Select 8 Union All
Select 9
-- Table 2
Declare @TblBgt Table
(
EmpID int,
PosID Int
)
--Inserting the records in the Table 2
Insert @TblBgt
Select 1,1 Union All
Select 2,2 Union All
Select 3,3 Union All
Select 4,4 Union All
Select -1,5 Union All
Select -1,6 Union All
Select -1,7
-- Now here i want insert all the missing empids in the my second table
i.e. @TblBgt and PosID will be MAx(PosID)+1
So The Expected output when i do
Select * from @TblBgt
11
22
33
44
-15
-16
-17
5 8
6 9
7 10
8 11
9 12
I know this that i can do this using the Temp Tables but i dont want
to use the Temp Table, is there any way to directly bluk insert the records without using Temp Tables
And I cant make the PosID Column an Indentity since, the value can come from some differents ways also..
Thanks
Chirag
View 2 Replies
View Related
Dec 28, 2007
i would to make a column contains of 3 characters and 6 auto increment numbers (example: "DLL - 123456")
and made it primary key and which data type i should use. i do not know whether i use after insert trigger in two columns one for three characters and another for code which has identity property >>>so please help me
View 4 Replies
View Related
Jan 1, 2008
Hi, I have a question regarding inserting numbers automatically. For example, there are 2 fields. One is CountryID and other is CountryName. From Front-end I'm entering the Country Name "India" alone and in my table the CountryID "1" or what ever the sequence should be entered. I believe this can be done using Triggers. I read on KB article in Microsoft website. But didn't get clear idea. Is that the correct way?Can any one show me a small example of this? Or give an idea for this?
View 5 Replies
View Related
Feb 4, 2006
Hi,
I have a decimal field in SQL Server 2000 which has a precision value of 3 and scale 1. I will be storing values ranging from 0.5 to 10.0 in there. However, in my asp.net web form, if I select the value 2.5 from the DropDownList, SQL Server stores it as 3.
Can anyone tell me why this is happening and give me some pointers on what I can do to fix it? Your help is much appreciated.
View 9 Replies
View Related
May 13, 2014
Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:
Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223
Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999
There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.
View 8 Replies
View Related
Mar 29, 2015
I have run into a problem when searching for a Movie title that is a number (like the movie name 21)
This is an access database and the Movie_Name is a text field
The SQL I am using is
Select * from Movie_Data where Movie_Title = ‘” & Movie_Name & “’”
This works perfectly for everything except when the Movie_Name = a number
Is there a way to make this search work with both characters and numbers?
View 3 Replies
View Related
Mar 3, 2007
I am totally confused, i have set my data type in the ID field for my database as VarChar (SQL Server Database)
I am confused a have it declared in ASP.NET 2 as a string but it doesnt recognise it when i try and insert the data into a database, what am I doing wrong?
Thanks
1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
2
3 <script runat="server">
4
5 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
6
7 End Sub
8
9 </script>
10
11 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
12 <span style="font-size: 11pt" id="SPAN1">
13 To initiate a new project, Enter details and
14 select <strong>Insert</strong>.
15 <br />
16 <br />
17 <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False" DataSourceID="SqlDataSource1" Height="50px" Width="324px" DefaultMode="Insert">
18 <Fields>
19 <asp:BoundField DataField="ProjectID" HeaderText="ProjectID" SortExpression="ProjectID" />
20 <asp:BoundField DataField="ProjectName" HeaderText="Project Name" SortExpression="ProjectName" />
21 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
22 <asp:TemplateField HeaderText="Project Manager" SortExpression="ProjectManagersID">
23 <EditItemTemplate>
24 <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("ProjectManagersID") %>'></asp:TextBox>
25 </EditItemTemplate>
26 <InsertItemTemplate>
27 <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ProjMan" DataTextField="PMName"
28 DataValueField="ProjectManagersID" SelectedValue='<%# Bind("ProjectManagersID") %>'>
29 </asp:DropDownList><asp:SqlDataSource ID="ProjMan" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
30 DeleteCommand="DELETE FROM [ProjectManagers] WHERE [ProjectManagersID] = @ProjectManagersID"
31 InsertCommand="INSERT INTO [ProjectManagers] ([PMName]) VALUES (@PMName)" SelectCommand="SELECT [ProjectManagersID], [PMName] FROM [ProjectManagers]"
32 UpdateCommand="UPDATE [ProjectManagers] SET [PMName] = @PMName WHERE [ProjectManagersID] = @ProjectManagersID">
33 <DeleteParameters>
34 <asp:Parameter Name="ProjectManagersID" Type="Int32" />
35 </DeleteParameters>
36 <UpdateParameters>
37 <asp:Parameter Name="PMName" Type="String" />
38 <asp:Parameter Name="ProjectManagersID" Type="Int32" />
39 </UpdateParameters>
40 <InsertParameters>
41 <asp:Parameter Name="PMName" Type="String" />
42 </InsertParameters>
43 </asp:SqlDataSource>
44 </InsertItemTemplate>
45 <ItemTemplate>
46 <asp:Label ID="Label1" runat="server" Text='<%# Bind("ProjectManagersID") %>'></asp:Label>
47 </ItemTemplate>
48 </asp:TemplateField>
49 <asp:TemplateField HeaderText="Project Type" SortExpression="ProjectTypeID">
50 <EditItemTemplate>
51 <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("ProjectTypeID") %>'></asp:TextBox>
52 </EditItemTemplate>
53 <InsertItemTemplate>
54 <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="ProjType" DataTextField="ProjectType"
55 DataValueField="ProjectTypeID" SelectedValue='<%# Bind("ProjectTypeID") %>'>
56 </asp:DropDownList><asp:SqlDataSource ID="ProjType" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
57 DeleteCommand="DELETE FROM [ProjectType] WHERE [ProjectTypeID] = @ProjectTypeID"
58 InsertCommand="INSERT INTO [ProjectType] ([ProjectTypeID], [ProjectType]) VALUES (@ProjectTypeID, @ProjectType)"
59 SelectCommand="SELECT * FROM [ProjectType]" UpdateCommand="UPDATE [ProjectType] SET [ProjectType] = @ProjectType WHERE [ProjectTypeID] = @ProjectTypeID">
60 <DeleteParameters>
61 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
62 </DeleteParameters>
63 <UpdateParameters>
64 <asp:Parameter Name="ProjectType" Type="String" />
65 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
66 </UpdateParameters>
67 <InsertParameters>
68 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
69 <asp:Parameter Name="ProjectType" Type="String" />
70 </InsertParameters>
71 </asp:SqlDataSource>
72 </InsertItemTemplate>
73 <ItemTemplate>
74 <asp:Label ID="Label2" runat="server" Text='<%# Bind("ProjectTypeID") %>'></asp:Label>
75 </ItemTemplate>
76 </asp:TemplateField>
77 <asp:BoundField DataField="StatusID" HeaderText="Status" insertvisible=False readonly=True SortExpression="StatusID" />
78 <asp:CommandField ShowInsertButton="True" ButtonType=Button />
79 </Fields>
80 </asp:DetailsView>
81 <br />
82 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
83 DeleteCommand="DELETE FROM [Project] WHERE [ProjectID] = @ProjectID" InsertCommand="INSERT INTO [Project] ([ProjectName], [Description], [ProjectManagersID], [ProjectTypeID], [StatusID]) VALUES (@ProjectName, @Description, @ProjectManagersID, @ProjectTypeID, @StatusID)"
84 SelectCommand="SELECT [ProjectID], [ProjectName], [Description], [ProjectManagersID], [ProjectTypeID], [StatusID] FROM [Project]"
85 UpdateCommand="UPDATE [Project] SET [ProjectName] = @ProjectName, [Description] = @Description, [ProjectManagersID] = @ProjectManagersID, [ProjectTypeID] = @ProjectTypeID, [StatusID] = @StatusID WHERE [ProjectID] = @ProjectID">
86 <DeleteParameters>
87 <asp:Parameter Name="ProjectID" Type="Int32" />
88 </DeleteParameters>
89 <UpdateParameters>
90 <asp:Parameter Name="ProjectName" Type="String" />
91 <asp:Parameter Name="Description" Type="String" />
92 <asp:Parameter Name="ProjectManagersID" Type="Int32" />
93 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
94 <asp:Parameter Name="StatusID" Type="Int32" />
95 <asp:Parameter Name="ProjectID" Type="Int32" />
96 </UpdateParameters>
97 <InsertParameters>
98 <asp:Parameter Name="ProjectID" Type="String" />
99 <asp:Parameter Name="ProjectName" Type="String" />
100 <asp:Parameter Name="Description" Type="String" />
101 <asp:Parameter Name="ProjectManagersID" Type="Int32" />
102 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
103 <asp:Parameter Name="StatusID" Type="Int32" DefaultValue=1 />
104 </InsertParameters>
105 </asp:SqlDataSource>
106 </span>
107 </asp:Content>
108
109
View 1 Replies
View Related
Jul 23, 2005
I have a feeling I'll be forced to use a script and a trigger for thistype of field format but I'm wondering if any of your wizards couldpoint at a simple way I could do something like this:For example, if I want to be able to keep track of new orders followingthis incrementing convention:ORD100000001ORD100000002ORD100000003.... etc ...Does MSSQL2000 have features that I can simply set for this kind offield or will I be resorting to writing up a SQL script and a trigger?
View 11 Replies
View Related
Jul 25, 2006
I am trying to use several tables that have one 10-character text field in
common. Most of the records have a numeric expression, but some tables have leading
0's, and some don't.
I can't cast the field to numbers because there are some records that have
letters also.
What function can I use to get rid of all the 0s at the left of each record?
(Sort of a LTRIM function that gets rid of 0s instead of spaces).
Thanks!
View 3 Replies
View Related
Oct 8, 2015
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
View 2 Replies
View Related
Jun 19, 2015
I have the following text field in SSRS report:
Version
2.2.32.7
1.0.0.0
2.0.0.0
1.2.0.0
2.1.8.8
1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly? Once sorted ascending, report should show
Version
1.0.0.0
1.2.0.0
1.4.11.0
2.0.0.0
2.1.8.8
2.2.32.7
View 3 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Apr 12, 2001
Does anyone has an idea on creating a new field in a table with more then say 10000 rows already in that table, and making that new field as primary key field. None of the fields in that table are unique.
View 2 Replies
View Related
Nov 10, 2000
Hey guys.
I just wanted to ask you a question? we are having a field declared
as BIT and we want that field to be unique..We are not able to declare
as a primary key..Is there anyway to declare as unique from the database
point of view?..please help us out in this issue and i'd appreciate that.
Andrew.
View 5 Replies
View Related
Nov 10, 2003
Hi,
I want to find out the primary key field name of the table using system table information i.e. sysobjects, syscolumns etc.
I tried to find it out but it seems to be very complicated.
Could anybody help me in this regards?
Thanks in advance.
Prasanna.
View 3 Replies
View Related
May 12, 2008
Hello,
I have an app that is connected to SQL 2005 express edition.
In the DB there is a table that has an auto incremental primary key
What I want is, I want to get a new value for that auto incremental field before sending the new record's date to the DB to be stored. So I can display the new record's ID in my app's interface
So is there something in SQL server like SEQUENCE in oracle.
Thanks in advance.
View 11 Replies
View Related
Jan 4, 2005
I need to insert a row of data and return the value of the primary key id of the row.
I thought that something like this would work
int Key = (int)command.ExecuteScalar();
where command is SqlCommand object.
It doesn't work, maybe I've misunderstood the usage of ExecuteScalar.
View 1 Replies
View Related
Nov 28, 2005
I have a table (table1) that has a bunch of fields....[field1][field2][field3][field4][field5]None of these fields are unique, but if I combine them, then they areunique.I know there is a way to make multi-field primary keys, but when I tryI get an that field1 is not unique, which I already know,How can I make a multi-field primary key?thanks
View 2 Replies
View Related
Jul 20, 2005
I want to use the Identity field (increment 1,1) as a primary key andhave a unique constraint on my other field which is of type char.I am worried that related data in other tables may lose referntialintegrity if records in the ID table get messed up and need to bere-entered.Can you please advice on best way to do this. I definitely need anumeric id field because it makes the joins and queries so muchfaster.
View 9 Replies
View Related
Aug 28, 2002
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View 9 Replies
View Related
Oct 8, 2006
I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product.....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input.......I'm very sorry but I'm having a hard time putting this into words for some reason..umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?ThanksAdam.
View 4 Replies
View Related
Sep 18, 2004
I'm really puzzled with this. I am new to using MS SQL and need help with the primary key. When I was using Access, I would open the database table and just enter values for the various columns, each time a new column was begun, the computer would insert a unique number there. OK, so now I am using visual studio to work on a SQL database. I created a new table and the first field I specified was "ID", I then chose it as the primary key. For default value, I tried both auto number and auto increment. These don't seem to work, as when I type values into fields and try to move to the next row, having left the ID field blank, I get an error which reads "String or Binary data may be truncated". What is going on, what do I need to do so the field automatically populates with an ID number... Some research sort of led me to the concept of a "trigger" to do this. Is this the approach. I was able to r-click on the table and I saw an option for "create trigger" or "new trigger"... How do I learn to do this, is there a generic trigger to do an auto increment of a primary key ID field?
View 2 Replies
View Related
Jul 10, 2007
Hi everybody couldn't get through with saving my data on the table with two primary keys...
my table structure is this
pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)
a sample data is here
pubid pub publ pubcode
1 a b ab
1 b b bb
2 a b ab
2 b b bb
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
thanks
View 7 Replies
View Related
May 15, 2006
Hi,
Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.
Thanks in advance for any reply.
View 1 Replies
View Related
Sep 4, 2007
Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752
10
60
5752
20
5752
30
5753
400
3000
5753
500
5753
600
5753
700
5753
800
5754
7
15
5754
8
Is there any chance to do that?
Thanks in advance,
Aldo.
Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')
ORDER BY Accounts.ACCOUNTKEY
View 22 Replies
View Related
