[Solved] Problems connecting multiple sales tables

[GardenElf]

Hi

i'm a lurking newbie with base and I'm not sure what to ask but i have following problem.
I get a automated CSV from external source that contains sales data. The file contains following fields:

Year, Month, day, week, storeid, storename, productname, salescode, price, sales, returns, totalAmount, totalQty, Salesperson
In the files each sale event is recorded separately despite it's made in the same store by the same person and on the same day. Due to the flux in sales different files have of course different amount of events recorded.

How should I link the files so that I can make a summary or pull out reports including specific information from a specific time frame?

There's a lot of advise on this forum about relationships and queries but I haven't found workable solution for this. Might just be that I'm not able to put the information into practise so if this is already solved could you be so kind and kick me into the right direction

Thanks,
GE

Edit: Thanks for your tips. For now I think I'll stick with your suggestion about importing data to the table instead of linking. But in the long haul I think I'll have to familiarize the other learning curve about automating the data import. Would probably serve me in the future as well. I think there's a ton to learn here but better take baby steps and start with the suggested tutorials. Hopefully this is like riding a bike... first you learn and get some rash, but once you get going it's a smooth ride

[MTP]

This is tough. You could write a query with UNION ALL to join the tables, but then every time you wanted to pull information from tables with different names you'd have to go through all the sub-queries and change the table names.

The most efficient solution, I believe, would be to write a script to import all the CSV files into one database table (instead of linking Base to the CSV files). Unfortunately this requires some knowledge of programming language. Definitely doable, just throwing another learning curve at you on top of the one associated with Base. There are some tutorials around to get started (I like the one at http://sheepdogguides.com/fdb/fdb1main.htm#gotoMacros), you'll probably need some of the more advanced information in the Andrew Pitonyak book OpenOffice Macros Explained (available as a free download from his site http://www.pitonyak.org/oo.php) and once you get to a level to actually write the script you need this forum has a lot of code snippets that can be found by searching.

[UnklDonald418]

There are some basic rules for good database design but understanding how to design and link tables requires a good understanding of exactly what information you need to get from your data. Various linked tables are often useful to simplify data entry, but it sounds like you may have the data entry task covered so you may not need to worry about those issues for now.
Import your data into a single Base table as MTP suggested. Then experiment with the GUI 'Create Query in Design View'. You may find that you can get much of the information you need from simple queries. As you get a better understanding of Base' as well as your needs, you can learn to write more sophisticated SQL queries, or determine if you need to create linked tables.
I've had some success with first importing CSV data into CALC. CALC has better import functions than Base. Then import the resulting spreadsheet into a Base table. Eventually you may want to investigate a macro script to do it in one operation.

A resource that I recommend for learning database design, and writing SQL is:
https://wiki.documentfoundation.org/ima ... torial.pdf
If you want to investigate the world of macros I found the book by Roberto Benitez
http://www.baseprogramming.com/
has explanations that I sometimes found easier to understand than those by Andrew Pitonyak. But Benitez's book will cost you. At the bottom of the web page is a link 'Click here to access documents ' that will get you to a lot of online information about macros. In particular the download 'Book Source Code' as the name implies has the source code for all the macros in his book. Chapter 12 is devoted to importing data into Base. But it only has the code, for explanations you will need the book.