Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Question Unanswered: Daily bulk insert?

    Hello again I'm here with another clueless question This one is legitimately a little tricky though.

    Currently I'm trying to set up the following functionality: I want to be able to click a sign-in button on my application's switchboard and be prompted to enter a date. Then it should open my sign-in form for that day (very basic datasheet view form)

    That's all well and fine but here's where it gets tricky (for me anyway) Basically what I need is to have this "sign in" form autopopulated with a single entry for every entry in my "Employees" table. The sign-in form is tied to my "Sales" table which includes an employee name foreign key.

    It works just fine entering them all one by one from the drop down box but I want to speed up that Sign in process. Anyone have an idea of how I could solve this problem? I'm guessing VBA would be my friend but I just can't work out the logic to do this.

    If you need anymore information I'd be happy to give it, thanks a lot in advance everyone.

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Yes you will need VBA for this. Will you want an entry for each employee every time you sign in or will this sometimes change?

    If it is fixed you could write an append query that runs once you have entered the date.
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    May 2009
    Posts
    258
    Why can't you just bind the Employees table to your datasheet and auto-populate it? If you need to do appending, you should be able to do this in the form as well.

    Examples of Microsoft Access data-entry forms, switchboards, and dialog boxes - Access - Microsoft Office Online

  4. #4
    Join Date
    Jul 2009
    Posts
    8
    Quote Originally Posted by SoftwareMatters
    Yes you will need VBA for this. Will you want an entry for each employee every time you sign in or will this sometimes change?

    If it is fixed you could write an append query that runs once you have entered the date.
    Each employee everytime, unless they already have an entry for that date. Basically an apend query is what I'm looking for I'm just not sure exactly how to go about grabbing all of the entries from my Employee table and sticking them in my Sales table for the date entered, and on top of them how to check to ensure they haven't already been entered (and not add them if they have).


    Quote Originally Posted by Ax238
    Why can't you just bind the Employees table to your datasheet and auto-populate it? If you need to do appending, you should be able to do this in the form as well.
    I had tried this originally but I can't seem to make it work the way I need. The main problem I ran into is that I also need to store which employee sold what to the Sales table each day, and I couldn't figure out how to get it to load all of those employees as a new sales record each day.

    I hope that clears things up a little bit. I'm sorry for being a tad ignorant of terminalogy and pretty basic database stuff but I'm fairly new to this end of programming and unfortunately it's been a lot of on the job training. Any help is greatly appreciated and thank you both a lot for what you've already done

  5. #5
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    If you want to email me your database (strip any confidential data out if you wish) I will have a go at building you an append query.
    Regards
    JD

    Software-Matters

  6. #6
    Join Date
    May 2009
    Posts
    258
    Something like the following may do the trick:
    Code:
    INSERT INTO SalesTable (EmpID, SaleDate)
    SELECT EmpID, Date()
    FROM EmployeeTable A
    WHERE NOT EXISTS
    (SELECT * FROM SalesTable B
    WHERE A.EmpID = B.EmpID
    AND A.SalesDate = Date())
    You'd then have to bind your control to a SELECT query for the SalesTable with a SalesDate of today:
    Code:
    SELECT * FROM SalesTable
    WHERE SalesDate = Date()
    Ax

Posting Permissions

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