Idea of Access Relationship

Hi,

Please i need your help;

i have one master table;

MASTER
A1
A2
A3
A4
A5
A6
A7
A8
A9
A10

and i have many other tables like;

11-11-2012
A1
A2


A5
A6

A8
A9
A10

and;

12-11-2012

A2
A3
A4
A5
A6

A8


and

13-11-2012
A1
A2
A3
A4
A5
A6

A8



now i want a relation ship between table 11-11-2012 and 12-11-2012 and 13-11-2012 to relate them togather, and another relationship & query to show the following result in one table (attached Untitled.jpg) - while the master list will show all cells and only where matching in the new table after merging table 11-11-2012 and 12-11-2012 and 13-11-2012

many thanks in advance

You'd use a left outer join to join each of the tables to the Master table; something like:

Please Login or Register  to view this content.

You'll probably need some brackets in that - but I don't have access so I can't check the syntax

But to be honest I think you need to re-structure your database, you shouldn't have so many tables, 2 tables would suffice. e.g

Please Login or Register  to view this content.

Please Login or Register  to view this content.

hi Kyle,

actually i tried to do what u r talking about but its not worked, can u help whats the table / relationship structure should be?

for this

Please Login or Register  to view this content.

I don't really understand the question.

In DateTable, Field1 would be a foreign key to the Master table (I've excluded other fields you should have in here, for example a primary key). So you'd have a one to many relationship between Master and DateTable

it is ok no problem, just forget it, just one more question;

if i kept one master file which include the primary key related to more than 200 tables (forein key), then i use query

any problem will be in the future?, the database will be damaged easier like this more than two tables in the whole database?

thanks.

I think you've misunderstood relational database design - there is never a need to have 200 tables just to have different dates, you need 1 master table and 1 table to hold a record for the master and the date.

I can't comment on how reliable your database is using your structure, but since you're using access in a way it's simply not designed to be used I wouldn't be overly optimistic for performance and stability issues.

Databases are designed for fewer tables with greater number of rows.

as an aside, using that many tables makes writing queries truly awful

ok thanks a lot

i understand what u wrote, and i know many things of relationships, tables...access in general.

the problem that all the tables like

Please Login or Register  to view this content.

not like;

Please Login or Register  to view this content.

and they are more than 200 excel files / 500+ rows each

that's why i post here to get some ideas how...

anyway, appreciated your time while replying to me

and they are more than 200 excel files / 500+ rows each

What's any of this got to do with Excel? I'm confused.

Do you mean you've got a load of Excel data and you're wanting to create an access database from it? Access tables do not have blank rows.

In either case, your tables don't appear to make sense to me . Databases aren't structured like Excel workbooks if you're trying to convert your excel workbooks into access, you're going to have to do a lot of data conversion to manipulate it into the structure I outlined - i.e one table with your master stuff in whatever that might be and another table that lists all your master stuff and the dates it occurred, without more data it's impossible to comment any further I'm afraid.

Perhaps it would help if you outlined some background around what you have now and what you're trying to achieve along with some sample data if all of this is still in Excel

ok, i will explain

1- MASTER LIST.xlsx file - list of employees with details

2- 11-11-2012.xlsx file - some employees worked on 11-11-2012.xlsx

3- 12-11-2012.xlsx file - some employees worked on 12-11-2012.xlsx

4- 13-11-2012.xlsx file - some employees worked on 13-11-2012.xlsx

5- RESULT.xlsx file - the result i want

thanks

Ok, that's a bit of a pain, but you need to set out your two tables as I mentioned, with a master table the same as your master sheet, you then need to consolidate all your workbooks into a single table with the badge number in one field and the date in the second. You can then import these into access and create a one to many join on the badge number to the date worked table.

hi

yes i already did that, but when i did the query, the result is not the same i want (RESULT.xlsx), what i have to do to get the same result in RESULT.xlsx file.

thanks.

this is the code

Please Login or Register  to view this content.

thank u very much

i used the cross-tab query then i used another simple query link the details

it is working now

thanks a lot