Results 1 to 12 of 12
  1. #1
    jrg24 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    17

    How should I begin setting up my database.

    I am trying to create a database to better track company costs. Since I cannot directly query our sales system via Access, I want to create a linked table to a csv file that I will update regularly with new and updated information. I do not want to change anything on the source data file. I have had success getting the linked data into Access and now I want to run queries from this table. The attachment contains the data I will be working with for these queries (the attachment is truncated to not show all columns for security sake. All names on the file have been changed). Besides the table in the attachment, I have another table that contains all possible "PROJ EXP" items. This table tells me which of our locations this item belongs to.



    When everything is said and done, I want a query that will tell me the following information: From Business Activity(PRO #, Ship Date, Invoice Date, Posted Info, Proj Exp, Proj Amt, Type), from Proj Exp Lookup Table: (Cost Location). This information will then be used to run other queries based off of this information

    My issue is that for Each PRO #, there can be up to 8 Proj Exp (the attachment only shows 5 but there are really 8 Proj Exp Columns). I would like the result to list all Proj Exp in one column because there is no difference between each Proj Exp column, The placement of a particular Proj Exp in a column is simply a function of when the data was entered in relation to other Proj Exp for the PRO. I also think this will make running queries off of this information easier. Any thoughts on this?

    I have had some success achieving this but I do not believe that my method is very efficient...in fact it bogs down Access to the point of (not responding) despite the fact that the sample dataset I am using is only 19,000 records long. My method can be seen in the code below. Please note that my first method did not try to incorporate matching the Proj Exp to Cost Location. I was going to do this in a subsequent query, but I am open to doing it all in one if that is practical.


    My question: Given the source data how would you attempt to do what I am trying to do? Thanks in advance for your help.


    Code:
    SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 1] AS Carrier,CCur([BusinessActivity].[Proj Amt 1]) AS Amount, [BusinessActivity].[Type 1] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp1" FROM [BusinessActivity] WHERE ([Proj amt 1] <> Null) 
    
    UNION ALL
    
    
    SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 2] AS Carrier,CCur([BusinessActivity].[Proj Amt 2]) AS Amount, [BusinessActivity].[Type 2] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp2" FROM [BusinessActivity] WHERE ([Proj amt 2] <> Null) 
    
    
    UNION ALL
    
    
    SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 3] AS Carrier, CCur([BusinessActivity].[Proj Amt 3]) AS Amount, [BusinessActivity].[Type 3] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp3" FROM [BusinessActivity] WHERE ([Proj amt 3] <> Null) 
    
    
    UNION ALL
    
    
    SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 4] AS Carrier, CCur([BusinessActivity].[Proj Amt 4]) AS Amount, [BusinessActivity].[Type 4] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp4" FROM [BusinessActivity] WHERE ([Proj amt 4] <> Null) 
    
    
    UNION ALL
    
    
    SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 5] AS Carrier, CCur([BusinessActivity].[Proj Amt 5]) AS Amount, [BusinessActivity].[Type 5] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp5" FROM [BusinessActivity] WHERE ([Proj amt 5] <> Null) 
    
    
    UNION ALL
    
    
    SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 6] AS Carrier, CCur([BusinessActivity].[Proj Amt 6]) AS Amount, [BusinessActivity].[Type 6] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp6" FROM [BusinessActivity] WHERE ([Proj amt 6] <> Null) 
    
    
    UNION ALL
    
    
    SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 7] AS Carrier, CCur([BusinessActivity].[Proj Amt 7]) AS Amount, [BusinessActivity].[Type 7] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp7" FROM [BusinessActivity] WHERE ([Proj amt 7] <> Null) 
    
    
    UNION ALL SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 8] AS Carrier, CCur([BusinessActivity].[Proj Amt 8]) AS Amount, [BusinessActivity].[Type 8] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp8" FROM [BusinessActivity] WHERE ([Proj amt 8] <> Null);
    Attachment 16292

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can't look at the attachment right now, but a UNION query is generally the way to normalize a table like that. Your test for Null should be:

    WHERE [Proj amt 1] Is Not Null
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jrg24 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    17
    Quote Originally Posted by pbaldy View Post
    Can't look at the attachment right now, but a UNION query is generally the way to normalize a table like that. Your test for Null should be:

    WHERE [Proj amt 1] Is Not Null
    I will change that, thanks. So you think that what I have done looks relatively good? Is doing a a union query on a single table something that is normally done in such situations?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, normally the table would have been designed correctly and it wouldn't be necessary. It is the standard workaround. You might consider using that as you import the data, so you don't have the performance hit when querying.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jrg24 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    17
    Quote Originally Posted by pbaldy View Post
    Well, normally the table would have been designed correctly and it wouldn't be necessary. It is the standard workaround. You might consider using that as you import the data, so you don't have the performance hit when querying.

    How would I go about doing that? Besides the Proj Exp columns, the table is pretty normal. One PRO, one customer, one invoice amount, etc. Are you suggesting there is some way that I can import the data into multiple tables so that I have my revenue information on one, Proj Exp on another, and shipper/consignee related information on another? Can I do that without having to actually change the source data?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    On a PC now so looked at the file. It looks like the "normalized" design would be 2 tables, header and detail info. Columns A through I would be in the header. Each of the groups of 3 fields for amounts would be in a detail table as separate records, along with the Pro# to relate them. Does that sound right to you? If so, you could certainly create an import routine that populated the 2 tables that way, basically 2 append queries: first populates the header data and then another based on a UNION query populates the detail.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jrg24 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    17
    Yes, this sounds right. I was just found something that does a similar thing which will essentially treat BusinessActivity as a temp table that will populate an Invoice Table and a Cost table. I am working on the Invoice table now and will edit my code above to create the new table for Cost. so far this is what I have for the invoice table:

    Code:
    SELECT tblBusinessActivity.[Pro #] AS PRO, tblBusinessActivity.Office AS Office, tblBusinessActivity.[Sales Rep] AS [Sales Rep], tblBusinessActivity.[Customer Name] AS Customer, tblBusinessActivity.[Ship Date] AS [Ship date] INTO tblInvoiceFROM tblBusinessActivity;
    After everything is set up I will create a macro to run these queries when I open the file so that everything updates properly. I think I am on the right track now. Thanks so much for your help.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    jrg24 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    17
    Now I have my tables set up. The query for what I am calling invoice data (header data) is as follows:

    Code:
    SELECT BusinessActivity.[Pro #] AS PRO, 
    BusinessActivity.Office AS Office, 
    BusinessActivity.[Sales Rep#] AS [Sales Rep], 
    BusinessActivity.[Shipment Type] AS [Shipment Type], 
    BusinessActivity.[Revenue Type] AS [Revenue Type], 
    BusinessActivity.[Customer Name] AS Customer, 
    BusinessActivity.[Ship Date] AS [Ship date], 
    BusinessActivity.[Delivery Date] AS [Delivery Date], 
    BusinessActivity.[Release Date] AS [Release Date], 
    BusinessActivity.[Invoice Date] AS [Invoice Date], 
    BusinessActivity.[Posted Info] AS [Post Date], 
    BusinessActivity.[LH Revenue] AS [LH Revenue], 
    BusinessActivity.[Carrier LH] AS [Carrier LH], 
    BusinessActivity.[Proj# L/H Profit] AS [Proj LH Profit], 
    BusinessActivity.[Acc Revenue] AS [Acc Revenue], 
    BusinessActivity.[FSC Revenue] AS [FSC Revnue], 
    BusinessActivity.[Carrier Acc] AS [Carrier Acc], 
    BusinessActivity.[Acc Profit] AS [Acc Profit], 
    BusinessActivity.[Invoice Amount] AS [Invoice Amount], 
    BusinessActivity.[Proj Carrier Expense] AS [Proj Carrier Exp], 
    BusinessActivity.[Proj Gross Profit] AS [Proj Gross Profit], 
    BusinessActivity.[Credits] AS Credits, 
    BusinessActivity.[Debits] AS Debits 
    INTO tblInvoice
    FROM BusinessActivity;
    I run this and the data comes out fine. The issue is that I am creating relationships with this table. Every time I run this query it tell me that tblInvoice will be deleted and then tells me it cannot delete it due to relationships. I tried an ammend query but this seems to just add records to those already in the table. Do I just need to run a delete query to delete data from tblinvoice and then run an ammend query? Am I missing anything I should have?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's a make table query (creates a new table). That's fine for the initial run, but for your ongoing process I'd use an append query (adds records to an existing table). If you're repopulating the table, then run a delete query first to empty it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    jrg24 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    17
    Quote Originally Posted by pbaldy View Post
    That's a make table query (creates a new table). That's fine for the initial run, but for your ongoing process I'd use an append query (adds records to an existing table). If you're repopulating the table, then run a delete query first to empty it.
    You are a Scholar and a Gentleman, Sir. thanks.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help. There are a great many instructors in my past who would argue the first point.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-16-2013, 12:40 AM
  2. Where to begin?
    By charaia in forum Programming
    Replies: 4
    Last Post: 11-18-2012, 05:44 PM
  3. Where do I begin...
    By ETCallHome in forum Access
    Replies: 1
    Last Post: 04-15-2011, 08:07 AM
  4. Help with setting up database
    By peachesflora in forum Database Design
    Replies: 1
    Last Post: 02-22-2011, 05:02 PM
  5. Begin:/Endit:
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 11-15-2010, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums