Table structures????? (1 Viewer)

[geoffishere]

I have a staff table which consists of an ID key, First Name, and Surname, this then feeds into three tables Modules, Modulelecturers, programmes connected by the ID KEY. This works fine unless i am doing a query bassed on two of these tables.

Do i have this set up wrong?? or is there something I need to change

Any help would be much appreciated

Geoff

 

[MSAccessRookie]

I have a staff table which consists of an ID key, First Name, and Surname, this then feeds into three tables Modules, Modulelecturers, programmes connected by the ID KEY. This works fine unless i am doing a query bassed on two of these tables.

Do i have this set up wrong?? or is there something I need to change

Any help would be much appreciated

Geoff

In theory, if the IDKey is Primary to one Table, and Foreign to the other three Tables, then it should be able to work. Can you show an example of the SQL Code for a Query that DOES NOT work?

 

[geoffishere]

well I cant get it straight in my head how it would work.

My staff table with my primary key feeds into the three table where the foreign keys are for module leader, module lecturer and programme leader.

I want to be able to run a query where it will return information about the module including the leader and it lecturers, so the query would have to pull data off the staff table twice. I do not understand how this owuld work or how I would do this in the query.

The database is on my other computer when I can get on it i will post the sql

Many Thanks

Geoff

 

[MSAccessRookie]

Without being familiar with the Table structures, I am unable to provide a clear answer. If you are unable to provide Sample SQL code, can you provide the Table Layouts? Table and Column names (with data types) should be enough to start.

 

[bany69b]

Hello!i have a table tblcustomers and 2 fields 1*Custname 2*entry and i need to write in Custname firstfield=bany and in second field=bany just 2 time only in the third time when i try to enter bany in the field 3 i must have error so any way to do that.

 

[vbaInet]

Hello!i have a table tblcustomers and 2 fields 1*Custname 2*entry and i need to write in Custname firstfield=bany and in second field=bany just 2 time only in the third time when i try to enter bany in the field 3 i must have error so any way to do that.

Please do not post on someone else's thread especially when the original enquiry has not been resolved, unless you were contributing. You can create your thread and you will get more response.

 

[geoffishere]

I have attached the structure of my tables, relationships and attempted query.

Many Thanks

Geoff

 

[geoffishere]

sorry i missed a table out on that diagram. The table(called instances)links programmes to modules via the progID and Mod ID. The table contains these two fields and a primary key of InsID.

Cheers

 

[jdraw]

sorry i missed a table out on that diagram. The table(called instances)links programmes to modules via the progID and Mod ID. The table contains these two fields and a primary key of InsID.

Cheers

geoffishere,
Here is a link to information on normalization.
http://r937.com/relational.html This may help with some of your issues.

However, you have only mentioned wanting to do a query. The database must be used for more than the query. Perhaps if we knew more about what your "business" is, we could offer more focused help.

You may wish to look at the data models and business rules at this site to help put your situation into context.
http://www.databaseanswers.org/data_models/index.htm

Good luck.

 

[geoffishere]

I think i have it working now, the query test does what i want it to. However when i run the report wizard on the query it crashes, i can design the report from scratch so its not a problem but i was wondering if it points to errors in my query design or table design.

I have attached my database for info

Many Thanks