Unwanted Enter Parameter Value Box

[Linda1503]

Sorry for delayed response - had to go fit shelves for son's new flat...
On the circular relationship in second diagram - don't truly understand - but you're right, it doesn't work.
On the number of tables needed I've now managed to convince myself (probably wrongly ) that I do need three...
Pork (T1) can be rolled or made into steaks (SubType)
Beef (T1) can be rolled or made into steaks (SubType)
surely that's a many to many relationship so would need a third table?

 

[The_Doc_Man]

No, not a third table. If the only difference is beef or pork, have a field in the table that says what it is. "Beef" "Pork" "Chicken" "Fish" "Alligator" (Sorry, my south Louisiana roots are showing ...)

 

[Linda1503]

Thanks Doc Man, but there are (so far) 39 Types and 88 SubTypes and 129 combinations so...

 

[The_Doc_Man]

The issue of type/subtype can be handled several different ways, at least one of which is that the type and subtype are two fields in the same table.

 

[Linda1503]

If subtype stands alone, then it isn't subordinate to type and if you want to relate many types with many othertypes, then yes there is a m-m relationship. But "subtype" would be assumed to belong to type and therefore NOT stand alone. For example, there are many cities in the US named Lincoln. is one Lincoln the same a another? NO, each Lincoln is different and exists in a different state. So State is "type" and city is "subtype". They have a 1-m relationship, not a m-m. We have a State (type) table. It has 51 entries (50 states plus DC or more entries if it includes dependencies like Puerto Rico). In the City table (subtype) there is a Foreign Key that points to the State in which the city exists.

Don't be confused by the subtypes having the same name.

Thanks Pat, very clear but in this instance the type is usually some sort of product description (beef, pork, pies, salads) and the sub type the preparation description - which can be applied to many different product types (although occasionally reversed eg cooked meats as type with beef, pork etc as sub).

 

[Linda1503]

Thanks Pat, very clear but in this instance the type is usually some sort of product description (beef, pork, pies, salads) and the sub type the preparation description - which can be applied to many different product types (although occasionally reversed eg cooked meats as type with beef, pork etc as sub).

Sorry - inadequate info from my end causing confusion

 

[Linda1503]

& crap naming of tables!

 

[Linda1503]

Maybe I should post this bit as a new question but...
This DB is used to gather Christmas order prep info & generate production crosstabs, route planning uploads, delivery schedules, labels etc for when there's a huge amount of product (for a small business) going out over a couple of days (mayhem!!!). Historically I've done the data entry & reports. But it's not good for a business to be so dependant on 1 person - they can't afford a 3rd party to design an app (& I'm too fearty to try ).
Question: Is there a way to share an order recording form from the DB on a network (no idea how to set that up yet) between the office windows pc and a tablet (they have an IPad) in the shop?
I'm a total novice on that sort of thing :-/ so any help much appreciated!!!

 

[isladogs]

I'm too fearty to try.

Fearty????

 

[The_Doc_Man]

The only way that you can use a remote connection like that involves some type of
(a) split database with a non-access backend and (a.1) Access front end to SQL backend and (a.2) separate web front-end to the SQL, or
(b) some sort of Remote Desktop Protocol so that the table "logs in" an RDP session that can touch Access.

The problem with using Access over any kind of wireless network is that one momentary network drop and your back-end is potentially toast. The Access File Sharing protocol (actually called SMB or Server Message Block) does not play nicely with wireless networks. Not nicely at all. Very likely to corrupt the back-end if it is native Access.

 

[Linda1503]

Fearty????

You get the gist I'm Scottish!

 

[Linda1503]

The only way that you can use a remote connection like that involves some type of
(a) split database with a non-access backend and (a.1) Access front end to SQL backend and (a.2) separate web front-end to the SQL, or
(b) some sort of Remote Desktop Protocol so that the table "logs in" an RDP session that can touch Access.

The problem with using Access over any kind of wireless network is that one momentary network drop and your back-end is potentially toast. The Access File Sharing protocol (actually called SMB or Server Message Block) does not play nicely with wireless networks. Not nicely at all. Very likely to corrupt the back-end if it is native Access.

The only way that you can use a remote connection like that involves some type of
(a) split database with a non-access backend and (a.1) Access front end to SQL backend and (a.2) separate web front-end to the SQL, or
(b) some sort of Remote Desktop Protocol so that the table "logs in" an RDP session that can touch Access.

The problem with using Access over any kind of wireless network is that one momentary network drop and your back-end is potentially toast. The Access File Sharing protocol (actually called SMB or Server Message Block) does not play nicely with wireless networks. Not nicely at all. Very likely to corrupt the back-end if it is native Access.

I could maybe copy the DB onto a laptop and set up an export file to email/google docs that could be appended to the main DB? Might have to brush up on appending data or would a union query be less fraught?

 

[isladogs]

You get the gist I'm Scottish!

I did indeed get the idea. As in

The Prime Minister should stop being a big fearty and come to Scotland to debate with me. (Alex Salmond)

 

[Linda1503]

iPads are pretty useless in a Windows environment. a Surface pro would be significantly more useful

Yep.

The Type and SubType tables have no relationship to each other but they each have a relationship to the product. And please do find better names for them.

S'okay for you pro's - I'm petrified to change anything in case I rock the boat!
The types & sub types have no relationship in access but they do in the mind of the user...But I shouldn't mix the two up - My only excuse for bad practice - apologies Pat

 

[The_Doc_Man]

I could maybe copy the DB onto a laptop and set up an export file to email/google docs that could be appended to the main DB? Might have to brush up on appending data or would a union query be less fraught?

There used to be a way to synchronize DBs that were copies of one another. However, Microsoft dropped that feature because it was a really tough thing to get right.

You can do this easily for yourself, though, by first making rules that define what you can do, or set up the DB on the laptop to only allow certain actions. For example, allow yourself to ADD data but do not EVER modify existing data. If that is practical, you could selectively filter based on dates to then (a) export a spreadsheet with only the new data required for each field, then (b) import the added records from a spreadsheet to a temporary table and then (c) use an INSERT INTO ... SELECT ... FROM query to load the temporary table to the permanent table. The problem would be that you CANNOT also allow the master DB to add records if there is any chance whatsoever that the ADD process would involve autonumbering. That would lead to destructive interference.

 

[Linda1503]

iPads are pretty useless in a Windows environment. a Surface pro would be significantly more useful

Yep - if there's

There used to be a way to synchronize DBs that were copies of one another. However, Microsoft dropped that feature because it was a really tough thing to get right.

You can do this easily for yourself, though, by first making rules that define what you can do, or set up the DB on the laptop to only allow certain actions. For example, allow yourself to ADD data but do not EVER modify existing data. If that is practical, you could selectively filter based on dates to then (a) export a spreadsheet with only the new data required for each field, then (b) import the added records from a spreadsheet to a temporary table and then (c) use an INSERT INTO ... SELECT ... FROM query to load the temporary table to the permanent table. The problem would be that you CANNOT also allow the master DB to add records if there is any chance whatsoever that the ADD process would involve autonumbering. That would lead to destructive interference.

Gulp! - Useful but might have to take a while to think it through...
Thanks!

 

[Linda1503]

iPads are pretty useless in a Windows environment. a Surface pro would be significantly more useful

Sorry , yep if they can face the change & ha e the dosh

 

[Linda1503]

You can use iPads if your app runs in a browser so you need either Citrix or RD to host your app. With Surface Pro's, they run windows so they work just like a desktop or laptop would. They are just more portable.

Thank you

 

[isladogs]

There are also far cheaper Windows tablets that run Access reasonably well.
I have used a Linx 12x64 Windows tablet with Office 365 for over 4 years. It also comes with a detachable keyboard and is a fraction of the cost of a Surface Pro

 

[Linda1503]

There are also far cheaper Windows tablets that run Access reasonably well.
I have used a Linx 12x64 Windows tablet with Office 365 for over 4 years. It also comes with a detachable keyboard and is a fraction of the cost of a Surface Pro

Ta