Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012
    Posts
    1

    Question Unanswered: NS SQL Count date with 2 tables

    I'm definitely not a developer but a Project Manager. I built my own MS Access front end/MS SQL db back end from the ground up using what knowledge/online solutions I could find to get the results I needed so I'm sure there are things that are wrong with my structure or thought process.

    I'm starting with the question but my end would be to store the results of this data so I can query it from my Access front end using between dates to determine totals.

    1) How do I create a SQL view/temp table to query count totals from an MS Access 2010

    2) CREATE TABLE [dbo].[tblProject](
    [ProjectID] [int] IDENTITY(1,1) NOT NULL, [CAD] [bit] NULL, [RMS] [bit] NULL, [JMS] [bit] NULL,

    and

    CREATE TABLE [dbo].[tblProjectMilestones]([MilestoneID] [int] IDENTITY(1,1) NOT NULL, [ProjectID] [int] NULL, [ProjectSignedByCustomer] [datetime] NULL,
    [ProjectCompleted] [datetime] NULL,

    3) tblProject - CAD, RMS and JMS = True/False variations
    tblProjectMilestones - ProjectCompleted = datetime

    4) To calculate the count for each deliverable with no dates:
    Select (Select COUNT (*) as CountCAD from tblProject where CAD='true') as CADNumber,
    (Select COUNT (*) as CountRMS from tblProject where RMS='true')as RMSNumber

    5) When I run the query between any dates, using tblProjectMilestonses.ProjectCompleted, what I would like is:
    Code:
    CADCount | RMSCount| JMSCount|
    5 10 3

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is your data stored in MS Access, or is it stored on a SQL Server?
    Look up "CREATE VIEW" in MSSQL Books Online. Basically you write a SELECT query that shows the data you want, and then put "CREATE VIEW MyView AS" in front of it.
    Couldn't be much easier.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •