One report from four databases

[Khushalay]

Hi Friends

I have four separate databases. Now I want to make a report taking fields from all the four. Is it possible if I link all the four tables in one new db then make a table and create a report?

I might be sounding too naive but I just do not know how to go about it. Any idea is appreciated.

Thanks in advance.

 

[Minty]

If each database only has the one table - I wonder if you maybe should only have one table and another field in the table with a high level identifier.
Do all the tables have the same fields?

 

[Khushalay]

Neither all the databases have one table nor all the databases have same fields or tables.

Though I have called/linked one table in another database as per the requirement.

I tried to link all the four tables and clicked on create report wizard. Combined all the fields form the 4 databases that I wanted for my report, when clicked ok, it gave me an error message that one or more tables isn't related to others. Click ok to edit system relationships.

 

[Minty]

Okay - we need to know what the data layout of the tables is, and what you are trying to extract.

 

[Khushalay]

Data layout?

I have tables and forms based on those tables for data entry and then I have reports as well.

 

[Minty]

What I was asking is what are the field data types and how are you trying to combine them. This is better described in simple plain English terms rather than database jargon.

What are the inputs and what are your expected outputs?

 

[Khushalay]

Data fields are text, date and numbers and we do entry in the form in different databases.
1: Order
2: Cut
3: Print
4: Pack

We have forms for these 4 databases.

Now, a final summarized report is needed by taking few fields from all the four databases.

 

[Minty]

Can I ask why you have separated these out into four different databases - they appear to all be part of the same overall process surely ?
If you are linking the tables back together to do the reporting it would be much easier if they all just sat in one database, and if necessary you had four different front ends (if required) for the different areas?

 

[Khushalay]

Thats right they are all part of one process. I divided it into 4 different databases because of the amount of fields and data. They are exceeding 250.

 

[Minty]

That sounds as if you have your data stored incorrectly. You are probably storing things spreadsheet style (Across) rather than Down - so your tables are probably poorly designed.
Thing1, Thing2, Qty1, Qty2 etc...
This is why your tables are too big.

Now this is why I asked originally about your table data. Can you list the fields, possibly better to upload a picture, or spreadsheet with the layouts.

 

[Khushalay]

Thats image of the cut form for users to enter data
all others are like this

 

[Minty]

Okay looking at you layer record picture, are these 3 separate records for that top level docket record?
The same question goes for the cutting records, how are these stored? Why have you got 0 values where there appears to be no records?

 

[jdraw]

Khushalay,

In 5 lines or less of plain English --NO JARGON - what exactly is the "business" involved?
What are the processes? How are the processes related?
It appears that you have an Excel background/experience, and that may be a handicap when moving to Access/database.

 

[Khushalay]

Its a garments business. Yes, I have Excel background.

How to store the data?

Minty... the default value is zero thats why it shows zero where there is no value and the total fields are calculated fields so if it appears blank, totals are not showed.

 

[Khushalay]

Its such an embarrassment but all this is self learning thats why all messed up

 

[Minty]

You need to take a step back an look at what your data is. Forget the existing structure - that has morphed into the nightmare spreadsheet you currently have.

You know what the individual entities are within your business model. These should each be related to other entities.

So essentially you have an order system, that is for garments. This probably involves a number of separate stages, indicated by your four existing databases.

You need to look at what you need as outputs from the system, and then determine what the inputs are to make that up. Do this all with pen and paper, if you can't model it on paper then you can't model it in the database.

I think you may possibly have been landed with a pretty complex process to try and automate/ rationalise. It looks as if you may have to get involved with inventory, and multiple items to make up final sales items. Inventory systems are notoriously awkward to build, which is why a lot of even experienced developers shy away from them or only do basic systems.

 

[jdraw]

You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model

Here is a different set of videos by a different presenter to complement those above.
These are free youtube videos from Mark Serva that deal with entity relationships, modelling, cardinality, ....
The playlist is listed here.


Here is a tutorial from RogersAccessLibrary -deals with a Customer-Order-Product .
IT has a problem definition, a process to design the appropriate tables and a solution. You have to work through it, but you will learn a procedure that you can use with any database.
Good luck with your project.

 

[Khushalay]

Thanks Minty and jdraw

wl try to check what you guys have suggested.