normalization and principles of tables and querys regarding redundant data

[ShaunG]

Hi people, attached is a photo of tblOrders and qryPurchaseOrders, every where I read and have been told to minimalise redundant data which makes sense and ive tried to do.... my question is would you call any of the data in my table or query redundant data.... the fields repeats themselves multiple times? and im wondering if that will cause issues later...



also does it matter if I have 2 querys of nearly the same fields so some data will be doubled up in the querys only not tables is this an issue or only an issue with tables repeating themselves?

thanks.

[Minty]

It appears there is no picture?

[ShaunG]

[ShaunG]

sorted lol

[Minty]

A query is not stored data. It is a combination of data from related tables.
You can make as many queries as you deem necessary to achieve your end goals.

Sometimes you can get economical with temporary or dynamic query creation in code if you need to vary how your data is being pulled.

I'm not a great believer in Yes No fields for certain types of data.
I would probably have a OrderCompletedDate rather than a check box for instance. You could then report on the time taken from order creation.
Possibly the same for cancellations?

[ShaunG]

A query is not stored data. It is a combination of data from related tables.
You can make as many queries as you deem necessary to achieve your end goals.

Sometimes you can get economical with temporary or dynamic query creation in code if you need to vary how your data is being pulled.

I'm not a great believer in Yes No fields for certain types of data.
I would probably have a OrderCompletedDate rather than a check box for instance. You could then report on the time taken from order creation.
Possibly the same for cancellations?

that's great to know about the querys probably a question I should of ask before building..... what about the tables I cant see another way to separate it?

that check box is on my received goods entry form which has 4 controls on it, 2 of them are automatically inserted - date and current user name, after they adjust the amount of goods that come in box is ticked and that locks the controls from being accessed.

[Minty]

Not sure what you mean about separating tables?

Re the check box - You can easily do the same based on a date being filled out or not.
Me.YourControl.Locked = Not IsNull(Me.CompletedDate)

[ShaunG]

Separating the fields is what I should of said...

yeah very true... the check box works well at the moment but still fine tuning it all... I don't have anything for goods on backorder yet.

[ArviLaanemets]

In query from purchase order and order details having order detail row info is OK;
The same in purchase orders table is not OK. In purchase orders table you'll have a single row of data per order, i.e. there are data which are valid for all order detail rows for given order.

In tblOrderDetails, I'd prefer an autonumeric primary ID OrderDetailID_PK (Maybe you have - in your added picture it is not visible the end of name OrderDetail...). One order can have one or many order detail rows.

[ShaunG]

In query from purchase order and order details having order detail row info is OK;
The same in purchase orders table is not OK. In purchase orders table you'll have a single row of data per order, i.e. there are data which are valid for all order detail rows for given order.

In tblOrderDetails, I'd prefer an autonumeric primary ID OrderDetailID_PK (Maybe you have - in your added picture it is not visible the end of name OrderDetail...). One order can have one or many order detail rows.

yeah that's fine I have a separate orders table with Ordernumber, supplier and Orderedby and then have an OrderDetails table with the details of the order and yes correct it is a primary key OrderDetailsID_PK

[ShaunG]

Not sure what you mean about separating tables?

Re the check box - You can easily do the same based on a date being filled out or not.
Me.YourControl.Locked = Not IsNull(Me.CompletedDate)

Hi Minty ive tried getting the above code to work in lieu of check box but im getting a compile error

[orange]

Shaun,

This link points to information in various formats related to Database Planning and Design.
If you work through 1 or 2 of the tutorials from RogersAccessLibrary you will learn/experience a procedure that you can use with any database.
There are videos and articles on Normalization, analysis techniques and data modelling.

[ShaunG]

Shaun,

This link points to information in various formats related to Database Planning and Design.
If you work through 1 or 2 of the tutorials from RogersAccessLibrary you will learn/experience a procedure that you can use with any database.
There are videos and articles on Normalization, analysis techniques and data modelling.

Thanks for that Orange, I have read and done some tutorials from RogersAccessLibrary already but always open to more

[Micron]

does it matter if I have 2 querys of nearly the same fields so some data will be doubled up in the querys only

Provided the more extensive query isn't a resource hog or something, there's nothing wrong with using the more extensive one and only using the fields you need for any given form. While I probably wouldn't use a 24 field query as a source for a 3 field form (for example) you make it seem like they're quite similar, so that's where I'm coming from.

Some don't seem to like queries with parameters (to each their own but I have no such issue) but that is another option. A third is to use vba based sql, which can be harder to conceptualize. The upside is that unlike a compiled Access db where query sql is still visible, no code, thus no query sql, can be viewed when it's all in vba. It tends to make the db smaller to start with.

[ShaunG]

Provided the more extensive query isn't a resource hog or something, there's nothing wrong with using the more extensive one and only using the fields you need for any given form. While I probably wouldn't use a 24 field query as a source for a 3 field form (for example) you make it seem like they're quite similar, so that's where I'm coming from.

Some don't seem to like queries with parameters (to each their own but I have no such issue) but that is another option. A third is to use vba based sql, which can be harder to conceptualize. The upside is that unlike a compiled Access db where query sql is still visible, no code, thus no query sql, can be viewed when it's all in vba. It tends to make the db smaller to start with.

you could be on to something when you said provided one isn't a hog, I'm having issues as we speak with the query's..... it was working fine until about 2 hours ago until I tried adding another query to add jobs in to the tables.

I gotta do some more research on query's get my head around it and then reconfigure them to suit I think.