logo
down
shadow

MS-Access : selecting data from two tables and only returning you need


MS-Access : selecting data from two tables and only returning you need

By : Mark Ten
Date : November 21 2020, 03:00 PM
this will help You have the courses list in Course table and students list in Student table, but you need to have one more table probably as CourseStudents which holds the data that represents which students take which courses.
Generally speaking (you may think of it as common practice) CourseStudents table consists of 2 columns: foreign key for Course table and foreign key for Student table.
code :
SELECT First_Name, Last_Name, Course_Name
FROM CourseStudents 
 INNER JOIN Student ON CourseStudents.StudentID = Student.StudentID
 INNER JOIN Course ON CourseStudents.CourseID = Course.CourseID
ORDER BY Course_Name


Share : facebook icon twitter icon
Access query for tables in linked SQL Server DB not returning data for search of datetime values as times

Access query for tables in linked SQL Server DB not returning data for search of datetime values as times


By : user2465378
Date : March 29 2020, 07:55 AM
To fix the issue you can do You can change your where clause to the following which removes the date on the left side of the comparison
WHERE TimeValue(tblMainData.timeInMain) Between #12/30/1899 9:0:0# And #12/30/1899 17:0:0#
code :
    WHERE 
         DATEADD( DAY , -1 *DATEDIFF(DAY, 0, timeInMain ) , timeInMain )
                    BETWEEN '09:00' and '17:00'
CREATE Test 
( 
    id int identity,
    DateTimeValue DateTime
)

--Values with COM Zero Date
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 8:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 9:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 10:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 11:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 12:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 1:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 2:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 3:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 4:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 5:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 6:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 7:00 PM')

--Values with SQL Zero Date
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 8:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 9:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 10:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 11:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 12:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 1:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 2:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 3:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 4:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 5:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 6:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 7:00 PM')

--Abritrary Date After Zero Dates
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 8:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 9:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 10:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 11:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 12:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 1:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 2:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 3:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 4:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 5:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 6:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 7:00 PM')
SELECT id, DateTimeValue
FROM dbo_Test
WHERE TimeValue(DateTimeValue) Between #12/30/1899 9:0:0# And #12/30/1899 17:0:0#
Selecting Multiple Tables in MS Access using C#

Selecting Multiple Tables in MS Access using C#


By : papa jygz
Date : March 29 2020, 07:55 AM
around this issue I'm not a good sql query builder but shouldn't it be TableName.FieldName not the other way around ?
Anyways try to put the TableName first:
code :
SELECT Customer.fn + ' ' + customer.mi + ' ' + customer.ln as [CUSTOMER FULLNAME]
     , Cashier.f. + ' ' + Cashier.mi + ' ' + customer.ln as [CASHIER FULLNAME]
     , Product.prodName as [PRODUCT NAME]
     , Product.prodDescription as [PRODUCT DESCRIPTION]
     , Product.prodBrand as [PRODUCT BRAND]
     , Transaction.prodQuantity as [PRODUCT QUANTITY]
     , Transaction.prodTotalPrice as [SUBTOTAL]
     , Personnel.job. as [Referral] 

FROM   Product 
     , [Order] 
     , [Transaction] 
     , Customer
     , Cashier 
     , Personnel  

WHERE Product.prodCode = Order.ProdCode 
  AND Order.orderNo = Transaction.orderNo 
  AND Personnel.pID= Transaction.pID 
  AND Customer.custID = Transaction.custID 
  AND Cashier.userID = Transaction.userID 
  AND Transaction.sDate = @transDate 
command.Parameters.AddWithValue("@transDate", someDate);
SQL query selecting from two tables returning duplicate rows

SQL query selecting from two tables returning duplicate rows


By : user2602583
Date : March 29 2020, 07:55 AM
like below fixes the issue I am trying to run this SQL query: , Try to select DISTINCT:
code :
SELECT DISTINCT t.*, tu.*...
SELECT t.*, tu.*, t.contact_name as contact_name, t.contact_email as contact_email, 
 t.ticketnumber as ticketnumber, t.subject as subject 
  FROM tickets t, ticket_updates tu 
  WHERE t.ticketnumber = tu.ticketnumber AND 
  tu.type = 'update' AND tu.customer <> 'Y' AND 
  t.status = 'Awaiting Customer' AND 
   tu.datetime <= '2014-10-18 16:26:00' 
   GROUP BY tu.`id`
   order by tu.datetime DESC LIMIT 0,5
SELECT statement isn't returning any results when inner-joining three tables and selecting one column from two of them

SELECT statement isn't returning any results when inner-joining three tables and selecting one column from two of them


By : vibouk
Date : March 29 2020, 07:55 AM
around this issue Your loan.card_number is not same as Patron.Card_Number - that's why it is not showing any records One way could be - cast loan.card_number to int like below -
code :
select Patron.name,
    Book.title
    from Patron
        Inner Join Loan on Patron.Card_Number=cast(Loan.card_number as int)
        Inner Join Book on Loan.book_ID=Book.book_id
    where Loan.date_out is not null
Selecting Count from multiple tables and returning them all in one row - SQL Server

Selecting Count from multiple tables and returning them all in one row - SQL Server


By : dpdespojo
Date : March 29 2020, 07:55 AM
hope this fix your issue This looks uglier but it is hundreds of times more efficient than doing 8 subqueries with a complete table scan in each one.
Related Posts Related Posts :
  • How to create table via ODBC in ansi sql compliant way?
  • Postgres. How to get all the parents that meet the childs criteria?
  • 'Exploratory' SQL queries that uses one criteria to find more criteria
  • Adding Date columns
  • Display ContactName from Guid in Gridview using SQLDataSource / Asp.Net
  • making groups based on match between column 1 and column 2 from 1 table
  • Teradata 15.10 - Remove set of characters from a string
  • PostgreSQL array_agg(INTEGER[])
  • Does a query goes through all data when you only select the last N?
  • Displaying results based on customer first order after specific date SQL Server
  • Sum records and add note what was summed up in sql
  • Need help in generating values
  • RODBC - import a table
  • SQL: How to Group By task and choose only those that are completed?
  • PostgreSQL/PostGIS Count number of visits
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org